All Forums Database
Santanu84 122 posts Joined 04/13
06 Dec 2013
TD Lock Upgrade and Downgrade Options

Hi
In a script, at Session level the transaction isolation is set to RU (Read Uncommitted or ACCESS).
At table level I am overriding to READ lock for Table1 in a SQL select.
 
Now if there is already an INSERT / MERGE / UPDATE operation going on Table1 then it will be on WRITE lock. So the SQL with READ lock will wait. 
In this scenario will TD system down grade the READ lock to ACCESS lock and return result from Table1?
 
I need to know this because if it is possible then I will implement this in a batch script. Please let me know.
 
Thanks
Santanu
 

dnoeth 4628 posts Joined 11/04
07 Dec 2013

Hi Santanu,
there's no automatic downgrade. When you request a READ lock you get a READ lock, the session will be blocked until the other session commits.
You can check if the request will be blocked using:

LOCK TABLE tab READ NOWAIT;
.IF ERRORCODE = 7423 THEN ...

 

Dieter

Santanu84 122 posts Joined 04/13
07 Dec 2013

Thanks dnoeth for your reply.

You must sign in to leave a comment.