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?

Answer 1

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 > 50