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.
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 ...