Environment:
SQL Server 2008 R2 - DMV (Dynamic Management View)
Query to see the locks across database:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
L.request_status AS LockStatus,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
- This query will run using selected database - db_id().
- sys.dm_tran_locks - system table has two types of columns (resource and request). The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.
- Values in sys.dm_tran_locks.resource_type - KEY, PAGE, DATABASE, FILE, OBJECT etc
- Values in sys.dm_tran_locks.request_mode -
- NULL = No access is granted to the resource. Serves as a placeholder.
- Sch-S = Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
- Sch-M = Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object
- S = Shared. The holding session is granted shared access to the resource.
- U = Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
- X = Exclusive. The holding session is granted exclusive access to the resource.
- IS = Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
- IU = Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
- IX = Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
- SIU = Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
- SIX = Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
- UIX = Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
- BU = Bulk Update. Used by bulk operation
- There are few others about range related
- Values in sys.dm_tran_locks.request_status -
- GRANT: the lock request was granted
- WAIT: The request to acquire a particular lock type is waiting.
- CONVERT: the request was granted earlier with a particular lock status but now is trying to upgrade to another status and is being blocked
SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID FROM
sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_iD('name_of_the_table'))
ORDER BY request_mode;
Note:
- Have to change 'name_of_the_table' to correct table
Command - KILL
Note:
- sessionid can be found by running above queries.
- Most of the time killing exclusive (X) type request solves table/db lock issue
1. http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/cae67b92-1f17-4f97-b68e-cfaba1207253/
2. http://msdn.microsoft.com/en-us/library/ms190345.aspx
