I am trying to use the following statement so I can do a dirty read on a table which has locks applied to it from other users.
LOCKING TABLE table_name FOR ACCESS
The problem is, this statement is being blocked due to the other locks. Why? I thought the point of the ACCESS lock was to circumvent other locks, at the risk of getting inconsistent data.
Thanks for your time.
One further observation: When I instead lock the ROW for access (as below) followed by a SELECT statement, the command is not blocked.
LOCKING ROW FOR ACCESS
this statement is blocked in only one senario when we the requested table is Already having exclusive lock.An exclusive lock on table prevents other users from obtaining any lock on the locked object.
please check below link , might be it will help you to get clear picture .
Thanks Arparmar. I have searched my codebase and am sure that it does not explicitly apply an exclusive lock. Further, I am not using DDL in my codebase (which I understand implicitly applies an exclusive lock). Are there other common operations that would apply an exclusive lock? I am doing pretty simple stuff here.
LOCKING ROW FOR ACCESS will give you the dirty read. Check the Teradata documentation, there is a matrix of which locks can supercede and stuff. It would help.