Is there a way to know if a table is locked and what kind of lock is currently on a table? I was hoping for something through the DBC tables in teradata, but I can't find any reference to anything like this. I have normal user access and the DBA is no help. Thanks.
+1
A:
AFAIK only DBA utilities are available to determine the type of lock on a table.
With only user-level rights you can do something like the following (from here):
Lock Table dbName.myTable for Access nowait
Select * from dbName.myTable;
And according to the master himself (Geoffrey Rommel):
If the table is locked, you will get error 7423, "Object already locked and NOWAIT. Transaction Aborted."
Adam Bernier
2009-07-06 22:06:53
I would think you have to use FOR WRITE instead of FOR ACCESS, since FOR ACCESS will succeed even if the table is locked for write.
Carlos A. Ibarra
2009-07-20 18:22:56
@Carlos: thank you for adding that info.
Adam Bernier
2009-07-21 05:27:09
This may be getting into the nit picky area, but if you are concerned about performance, you might want to replace the * with (top 1 1). It will prevent a full table retrieval from happening.
bogertron
2010-08-14 21:47:18