Archives of the TeradataForum

Message Posted: Thu, 28 May 2004 @ 00:07:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Thursday, May 27, 2004 18:26 -->

All,

I imagine many of you design your warehouse to control access to base tables through a "view database". And I am guessing that in these views, you specify to lock row for access. This is what we do and it works great, except when someone does a PI select. In that case, it appears Teradata does not place an access lock and so the user gets blocked if the table is being updated. We understand the user could be making a dirty read, that is more acceptable than a delayed block.

So does anyone know of a way to ensure an Access lock when selecting on PI through a view that locks row for access? I do realize that we could just add the locking modifier to the users' PI select statement, but expecting that of our developers all the time is unrealistic. I had hoped to handle this completely through the view layer.

An explain without PI select shows:

     1) First, we lock Prod_Tables.Summ_Customer for access.
     2) Next, we do an all-AMPs RETRIEVE step from...........

An explain with PI select skips step 1 above:

     1) First, we do a single-AMP JOIN step from Prod_Tables.Summ_Customer
        by way of the unique primary index

Any suggestions are greatly appreciated. Thank you.

	<Prev	Next>		<<First	<Prev	Next>	Last>>