Monday, February 21, 2011

Teradata locks - How to know if a table is locked ?

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.

From stackoverflow
  • 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."

    Carlos A. Ibarra : 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.
    Adam Bernier : @Carlos: thank you for adding that info.
    bogertron : 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.

0 comments:

Post a Comment