All Forums Database
sdc 13 posts Joined 06/15
01 Mar 2016
LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

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.


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.

sdc 13 posts Joined 06/15
01 Mar 2016

One further observation: When I instead lock the ROW for access (as below) followed by a SELECT statement, the command is not blocked.



Arparmar 8 posts Joined 02/16
01 Mar 2016

hi Shaun,
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 .

sdc 13 posts Joined 06/15
01 Mar 2016

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.

Adeel Chaudhry 773 posts Joined 04/08
06 Mar 2016

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.