SQL Server View lock table SQL "Go"

1. Select * from sys.dm_tran_locks or sp_lock view request_node field for ' X ' (exclusive lock) or ' IX ' (Intent exclusive lock)
2. View process details with SP_WHO2 + PID (process ID)
3. Use DBCC INPUTBUFFER (PID) to view the contents of the dead-lock statement

When blocking occurs, the following command shows which process session ID is blocking which process session ID, and how many milliseconds (ms) have elapsed. As in 3, session ID = 53 blocks the session ID = 52 process. You can also see the same content through the SQL Server Profiler tool.

SELECT blocking_session_id, Wait_duration_ms, session_id from Sys.dm_os_waiting_tasks

Figure 3 The UPDATE statement (53) of session a above, blocking the SELECT statement for session B (52)

We can also see the lock and block details for the entire database through the following two commands:

SELECT * from sys.dm_tran_locks

EXEC sp_lock

Figure 4 Session ID = 52 process has been waiting for blocking (wait)

Another kill command directly kills the process that caused the blockage, as follows:

KILL 53

==================================================================

sp_lock– query which process lock table, SPID: Process Id,objid: Object ID
EXEC sp_executesql N ' kill [spid] ' – Kill process
Select object_name ([ObjId]) – query which table is locked and find the one whose ObjId is not 0

– View with SQL statement, locked table name
Select request_session_id spid,object_name (resource_associated_entity_id) tableName
From sys.dm_tran_locks where resource_type= ' OBJECT '

SPID Lock Table Process
TableName the name of the table being locked

===================================================================

Turn from: 48031459

SQL Server View lock table SQL "Go"