LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

Database
sdc
Enthusiast

LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

Hello,

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.

4 REPLIES
sdc
Enthusiast

Re: LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

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
Enthusiast

Re: LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

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 .

https://forums.teradata.com/forum/enterprise/exclusive-lock

sdc
Enthusiast

Re: LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

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.

Teradata Employee

Re: LOCKING TABLE table_name FOR ACCESS can be blocked by other locks

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.