How to check if a table is locked in sql server

Question

I have a large report I am running on sql server  It takes several minutes to run  I don t want users clicking run twice  Since i wrap the whole procedure in a transaction  how do I check to see if the table is locked by a transaction  If so I would want to return an error message saying  report generating  please try again in a few  minutes    How can this be accomplished

User · Accepted Answer

Better yet  consider sp getapplock which is designed for this  Or use SET LOCK TIMEOUT  Otherwise  you d have to do something with sys dm tran locks which I d use only for DBA stuff  not for user defined concurrency

User · Answer

sys dm tran locks  contains the locking information of the sessions  If you want to know a specific table is locked or not  you can use the following query  SELECT      from   sys dm tran locks  where    resource associated entity id   object id  schemaname tablename     if you are interested in finding both login name of the user and the query being run  SELECT  DB NAME resource database id     s original login name    s status    s program name    s host name     select text from sys dm exec sql text exrequests sql handle       from   sys dm tran locks dbl      JOIN sys dm exec sessions s ON dbl request session id   s session id   INNER JOIN  sys dm exec requests exrequests on dbl request session id   exrequests session id where  DB NAME dbl resource database id     dbname    For more infomraton locking query  More infor about sys dm tran locks

User · Answer

You can use the sys dm tran locks view  which returns information about the currently active lock manager resources    Try this    SELECT       SessionID   s Session id       resource type          DatabaseName   DB NAME resource database id        request mode       request type       login time       host name       program name       client interface name       login name       nt domain       nt user name       s status       last request start time       last request end time       s logical reads       s reads       request status       request owner type       objectid       dbid       a number       a encrypted        a blocking session id       a text         FROM         sys dm tran locks l      JOIN sys dm exec sessions s ON l request session id   s session id      LEFT JOIN                    SELECT             FROM    sys dm exec requests r          CROSS APPLY sys dm exec sql text sql handle         a ON s session id   a session id  WHERE        s session id  gt  50

User · Answer

If you are verifying if a lock is applied on a table or not  try the below query   SELECT resource type  resource associated entity id      request status  request mode request session id      resource description  o object id  o name  o type desc  FROM sys dm tran locks l  sys objects o WHERE l resource associated entity id   o object id     and resource database id   DB ID

[sql] How to check if a table is locked in sql server

Examples related to sql

Examples related to sql-server

Examples related to concurrency

Examples related to locking