Lock a table row by rowhash

Database

Lock a table row by rowhash

Hello,

   Is it possible to explecitely lock a row or a set of rows of a table in a bteq/tpump through out the session?  

What will happen if only one row is locked due to update activity and on the other side someone trying to access the same row using a range query. Is it the case that user will get all the records other than the locked one if the view has locking row for access. 

Thanks,

1 REPLY
Teradata Employee

Re: Lock a table row by rowhash

Yes it is possible, here you have an example:

BT;
LOCKING ROW FOR WRITE SELECT NULL FROM TABLE1 WHERE COL1 = 'XXXX';
....
ET;

If one session has 1 rowhash blocked and another one do a range query (all AMP retrieve), the second one will get bloced until the first one release the rowhash. 

In the other hand, if the second session add a locking table like this:

locking table TABLE1 for access
SELECT * FROM TABLE1 WHERE COL1 between 'AAAA' and 'ZZZZ'

this session will not be blocked by the first one but you can get inconsistency data