Archives of the TeradataForum

Message Posted: Fri, 28 May 2004 @ 08:00:02 GMT


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

Subj:		Re: Access Lock with PI Selection

From:		Dieter Noeth

Anomy Anom wrote:

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

You dropped the interesting part:

     create view qwert as lock row access sel * from au.trans;

     sel a.account_number, count(*)
     from qwert t join au.accounts a
     on a.account_number = t.account_number
     where t.account_number = 1
     group by 1;


	`1)`	`First, we do a single-AMP JOIN step from au.a by way of the unique primary index "au.a.account_number = 1" with no residual conditions, which is joined to au.trans by way of the primary index "au.trans2.account_number = 1" with no residual conditions locking row of au.a for access and row of au.trans for access. au.a and au.trans2 are joined using a merge join, with a join condition of ("au.a.account_number = au.trans.account_number").`

In V2R5 this is the expected plan, can't remember if it was different in V2R4. So what's your release?

Dieter


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

Attachments

Library

Quick Reference