SQLServer锁表查询及解锁

1. 查询锁表会话和表名

1
2
select request_session_id 锁表进程, OBJECT_NAME(resource_associated_entity_id) 被锁表名
from sys.dm_tran_locks where resource_type = 'OBJECT';

2. 查询锁表状态及锁表SQL

1
2
3
4
5
6
7
SELECT A.BLOCKING_SESSION_ID, A.WAIT_DURATION_MS, A.SESSION_ID, B.TEXT
FROM SYS.DM_OS_WAITING_TASKS A,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) B
WHERE A.SESSION_ID = B.SESSION_ID
AND A.BLOCKING_SESSION_ID IS NOT NULL;

3. 查询阻塞语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT BL.SPID BLOCKING_SESSION,
BL.BLOCKED BLOCKED_SESSION,
ST.TEXT BLOCKEDTEXT,
SB.TEXT BLOCKINGTEXT
FROM (SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES A
WHERE BLOCKED > 0
AND NOT EXISTS (SELECT 1
FROM SYS.SYSPROCESSES B
WHERE BLOCKED > 0
AND A.BLOCKED = B.SPID)
UNION
SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES
WHERE BLOCKED > 0) BL,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) ST,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) SB
WHERE BL.BLOCKED = ST.SESSION_ID
AND BL.SPID = SB.SESSION_ID;

4. 批量生成解锁语句

1
2
3
4
5
6
select distinct
'kill '+convert(nvarchar(50),request_session_id) + ';' [解锁SQL]
FROM
sys.dm_tran_locks
where
resource_type='OBJECT';