How to Find Out What Is Locking My Tables

How to find out what is locking my tables?

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

How to check if a table is locked in sql server

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.

How to check which locks are held on a table

To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.

There are multiple different versions of "friendlier" sp_lock procedures available online, depending on the version of SQL Server in question.

In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.

Detecting locked tables (locked by LOCK TABLE)

SHOW OPEN TABLES to show each table status and its lock.

For named locks see Show all current locks from get_lock

How to check specific table is locked in Mysql?

Approach1: Simply execute command show processlist; or show full processlist; command and check if any query/table is showing locked in processlist.

Note: check from super user to check all running process on server.

Approach2: execute show engine innodb status; and you can check locking here...

Approach3: In mysql 5.6 onwards you can check it in information_schema db also.

Note: If you are using any gui tools like mysqladministrator then you can check all running process in this and easy findout if any table is locked....

Update:

As you want to get this by sql query, so you can use below query-

For all locked tables-

SHOW OPEN TABLES WHERE in_use>0;

To check specific table-

SHOW OPEN TABLES WHERE in_use>0 AND `Table`='your_table_name';

How to find out what table a page lock belongs to

This is what the resource_associated_entity_id column is for (Example query).

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'OBJECT'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id 

Find what rows are locked for a given table and who is locking them in SQL Server

sys.dm_tran_locks, as already said in 694581. To identity which rows are actually locked, you need to understand the locking hierarchy (table->rowset->page->row) and you need to crack the lock resource description. For table locks is the object id from sys.objects, for rowsets is the partition_id from sys.partitions and for pages is the actual page id. For rows it depends whether is a heap or a btree, but you can use the (undocumented) %%lockres%% virtual column to find the row. If this is too simple, you need to consider also range locks as they impact all the rows in the specified range.

When you add up the difficulty of navigating the physical hierarchy, specially when page locks are involved, with the complex model of the lock compatibility matrix, the complications added by hash collisions and consider the the pace at which the locks you're looking at change, I would say that at the very best you can do a very rough approximation. Besides doing a specific problem investigation, there is little point into digging into this. I would be horrified to hear of an application that looks actively at locks held and makes any kind of decision based on the information seen.

---

Related Topics