All Forums Database
dipakrana 2 posts Joined 10/11
09 Aug 2016
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,

AtardecerR0j0 71 posts Joined 09/12
11 Aug 2016

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

Be More!!

You must sign in to leave a comment.