SQL死锁进程内容查询语句

1.方式1

SELECT   object_name(A.resource_associated_entity_id) as TABLENAME, A.request_session_id AS SPID,
         DB_NAME(B.dbid) AS DBName,B.blocked,B.dbid,B.program_name,B.waitresource,B.lastwaittype,
	     B.loginame,
	     B.hostname,B.login_time,B.last_batch--,B.*
FROM  sys.dm_tran_locks  A  ,sys.sysprocesses B
WHERE resource_type = 'OBJECT' 
     AND request_session_id =B.spid  AND blocked >0
1
2
3
4
5
6
7

2.方式2

 SELECT spid, blocked,sp.dbid, DB_NAME(sp.dbid) AS DBName, object_name(A.resource_associated_entity_id) as TABLENAME,
	program_name,
	waitresource,
	lastwaittype,
	sp.loginame,
	sp.hostname,
	a.[Text] AS [TextData],
	SUBSTRING (
		A. TEXT,
		sp.stmt_start / 2,
		(
			CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start
		) / 2
	) AS [current_cmd] ,object_name(C.resource_associated_entity_id) as TABLENAME
	FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
	WHERE spid > 50  AND blocked >0   AND request_session_id =spid AND resource_type = 'OBJECT' 
	ORDER BY blocked DESC,
	DB_NAME(sp.dbid) ASC,
	a.[text];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.建立定时LOG记录，避免发生的时候无法抓取

--建立LOG表
CREATE TABLE A_LOCK_LOG		                       
(
   login_time  DATETIME ,
   last_batch  DATETIME,
   spid INT, 
   blocked INT,
   TABLENAME  VARCHAR(6000),
   dbid  INT,
   DBName VARCHAR(6000),
   program_name VARCHAR(6000),
   waitresource VARCHAR(6000),
   lastwaittype VARCHAR(6000),
   loginame VARCHAR(6000),
   hostname VARCHAR(6000),
   TextData VARCHAR(6000),
  current_cmd VARCHAR(6000),
  CREATION_DATE		DATETIME	DEFAULT GETDATE()-- 创建时间(派令日期)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

--建立定时作业的进行记录资料
INSERT INTO A_LOCK_LOG(login_time,last_batch,spid,blocked,TABLENAME,dbid,DBName,program_name,waitresource,lastwaittype,loginame,hostname,TextData,current_cmd)
   SELECT login_time,last_batch,spid, blocked,object_name(C.resource_associated_entity_id) as TABLENAME,sp.dbid, DB_NAME(sp.dbid) AS DBName, 
	       program_name,waitresource,lastwaittype,sp.loginame,sp.hostname,
		   a.[Text] AS [TextData],
	      SUBSTRING ( A. TEXT,sp.stmt_start / 2,(CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd] 
	FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
	WHERE spid > 50  AND blocked >0   AND request_session_id =spid AND resource_type = 'OBJECT' 
	ORDER BY blocked DESC,
	DB_NAME(sp.dbid) ASC,
	a.[text];
1
2
3
4
5
6
7
8
9
10
11

4.模拟上锁机制

ALTER PROCEDURE [dbo].[Test]--
     @ERR_NO     INT              OUTPUT,
     @ERR_DESC   NVARCHAR(4000)   OUTPUT

AS
BEGIN
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置事务隔离级别为 Serializable
    
    BEGIN TRANSACTION;

    -- 锁定目标表
    SELECT * FROM 表名 WITH (TABLOCKX, HOLDLOCK); -- 使用 TABLOCKX 和 HOLDLOCK 提示锁定表，并保持锁定

    -- 模拟长期锁定
    WAITFOR DELAY '00:10:00'; -- 等待10分钟

    COMMIT;
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19