Sql Server 死锁 死锁捕捉 阻塞 邮件提醒 监控工具 sqllockfinder
死锁
数据库
SELECT @@version
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 18362: )
示例数据库
AdventureWorks2017.bak 这是SqlServer示例数据库，可以直接在网上下载
触发死锁
打开第一个查询窗口，输入
USE AdventureWorks2017
GO
BEGIN TRAN
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = OrderQty + 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
GO
打开第二个查询窗口，输入
USE AdventureWorks2017
GO
BEGIN TRAN
UPDATE Production.Product
SET ListPrice = ListPrice * 0.9
WHERE ProductID = 922;
回到第一个窗口，输入以下代码，可以看到一直在运行。
UPDATE Production.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductID = 922;
GO
现在在第二个窗口输入以下代码：
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = OrderQty - 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
GO
本机在3s后出现死锁。
第一个查询窗口中的UPDATE对表Purchasing.PurchaseOrderDetail申请了一个X锁。但是事务没关闭，所以锁没有释放。在第二个窗口中的UPDATE语句同样在表Production.Product申请了X锁，同样锁也没有释放，此时再到第一个窗口，也就是第一个事务内执行对表Production.Product的UPDATE操作，由于第二个窗口也就是第二个事务还持有对这个表上的资源锁，所以第一个事务会处于等待状态。而第二个事务的UPDATE Purchasing.PurchaseOrderDetail又由于第一个事务还在持有X锁，所以仍然在等待。最后到达了死锁的条件，发生了死锁。
展示了转换死锁的情景，A、B两个过程都在相同的页上持有共享锁，每个过程都想把自己的共享锁升级到排他锁，但是由于共享锁和X锁在有多会话持有时不兼容，就造成了等待。
监控死锁的几种方式
启动 1222 和 1204
DBCC TRACEON(1222, -1)
GO
1204：返回参与死锁的索梓源和类型，以及受影响的当前命令
1222：以不符合任何XSD架构的XML格式，返回参与死锁的锁资源和类型，以及受影响的当前命令
* 可能需要定期清理日志
SqlServer的ERRORLOG中
2020-11-30 14:30:12.08 spid22s deadlock-list
2020-11-30 14:30:12.08 spid22s deadlock victim=process2c602046ca8
2020-11-30 14:30:12.08 spid22s process-list
2020-11-30 14:30:12.08 spid22s process id=process2c602046ca8 taskpriority=0 logused=264 waitresource=KEY: 16:72057594050904064 (3e75cd3a78e7) waittime=4928 ownerId=42727624 transactionname=user_transaction lasttranstarted=2020-11-30T14:30:00.417 XDES=0x2c5f7e14490 lockMode=U schedulerid=6 kpid=14396 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2020-11-30T14:30:07.153 lastbatchcompleted=2020-11-30T14:30:07.150 lastattention=2020-11-30T14:29:25.857 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=DESKTOP-6122L19 hostpid=15724 loginname=DESKTOP-6122L19\Administrator isolationlevel=read committed (2) xactid=42727624 currentdb=16 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2020-11-30 14:30:12.08 spid22s executionStack
2020-11-30 14:30:12.08 spid22s frame procname=adhoc line=1 stmtend=248 sqlhandle=0x020000004fbb092c29dceca676884294df83a6c4d191eec80000000000000000000000000000000000000000
2020-11-30 14:30:12.08 spid22s unknown
2020-11-30 14:30:12.08 spid22s frame procname=adhoc line=1 stmtend=248 sqlhandle=0x0200000086861218faf80bb42e2b30c275c0de82f20b50510000000000000000000000000000000000000000
2020-11-30 14:30:12.08 spid22s unknown
2020-11-30 14:30:12.08 spid22s inputbuf
2020-11-30 14:30:12.08 spid22s UPDATE Purchasing.PurchaseOrderDetail
2020-11-30 14:30:12.08 spid22s SET OrderQty = OrderQty - 200
2020-11-30 14:30:12.08 spid22s WHERE ProductID = 922
2020-11-30 14:30:12.08 spid22s AND PurchaseOrderID = 499;
2020-11-30 14:30:12.08 spid22s process id=process2c60205f468 taskpriority=0 logused=2200 waitresource=KEY: 16:72057594049921024 (bd095ec17235) waittime=8528 ownerId=42727514 transactionname=user_transaction lasttranstarted=2020-11-30T14:29:57.680 XDES=0x2c5c0258490 lockMode=X schedulerid=9 kpid=17144 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2020-11-30T14:30:03.553 lastbatchcompleted=2020-11-30T14:30:03.550 lastattention=1900-01-01T00:00:00.550 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=DESKTOP-6122L19 hostpid=15724 loginname=DESKTOP-6122L19\Administrator isolationlevel=read committed (2) xactid=42727514 currentdb=16 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2020-11-30 14:30:12.08 spid22s executionStack
2020-11-30 14:30:12.08 spid22s frame procname=adhoc line=1 stmtstart=58 stmtend=224 sqlhandle=0x02000000b946b92f64e6020ab57679e19aa9e916cc7e0da10000000000000000000000000000000000000000
2020-11-30 14:30:12.08 spid22s unknown
2020-11-30 14:30:12.08 spid22s frame procname=adhoc line=1 stmtend=166 sqlhandle=0x02000000cbda872f8f10a0c57ab5fcea19a16786794f88340000000000000000000000000000000000000000
2020-11-30 14:30:12.08 spid22s unknown
2020-11-30 14:30:12.08 spid22s inputbuf
2020-11-30 14:30:12.08 spid22s UPDATE Production.Product
2020-11-30 14:30:12.08 spid22s SET ListPrice = ListPrice * 1.1
2020-11-30 14:30:12.08 spid22s WHERE ProductID = 922;
2020-11-30 14:30:12.08 spid22s resource-list
2020-11-30 14:30:12.08 spid22s keylock hobtid=72057594050904064 dbid=16 objectname=AdventureWorks2017.Purchasing.PurchaseOrderDetail indexname=PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID id=lock2c5d4652780 mode=X associatedObjectId=72057594050904064
2020-11-30 14:30:12.08 spid22s owner-list
2020-11-30 14:30:12.08 spid22s owner id=process2c60205f468 mode=X
2020-11-30 14:30:12.08 spid22s waiter-list
2020-11-30 14:30:12.08 spid22s waiter id=process2c602046ca8 mode=U requestType=wait
2020-11-30 14:30:12.08 spid22s keylock hobtid=72057594049921024 dbid=16 objectname=AdventureWorks2017.Production.Product indexname=PK_Product_ProductID id=lock2c5e6001a00 mode=X associatedObjectId=72057594049921024
2020-11-30 14:30:12.08 spid22s owner-list
2020-11-30 14:30:12.08 spid22s owner id=process2c602046ca8 mode=X
2020-11-30 14:30:12.08 spid22s waiter-list
2020-11-30 14:30:12.08 spid22s waiter id=process2c60205f468 mode=X requestType=wait
2020-11-30 14:30:14.66 spid12s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 325408, committed (KB): 656824, memory utilization: 49%.
hobid === 72057594050904064
USE AdventureWorks2017
GO
SELECT OBJECT_NAME(i.object_id) ,
i.name
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE p.partition_id = 72057594050904064
使用Profiler捕获死锁信息
使用Service Broker Event Notification
USE msdb;
-- 创建一个 service broker queue
CREATE QUEUE DeadlockQueue
GO
-- 创建一个 service broker service 接收事件
CREATE SERVICE DeadlockService
ON QUEUE DeadlockQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
-- 创建一个针对死锁的事件通知
CREATE EVENT NOTIFICATION CaptureDeadlocks
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE 'DeadlockService', 'current database';
GO
SELECT CAST(message_body AS XML) AS message_body FROM DeadlockQueue
使用WMI捕获死锁
使用ExtendedEvents捕获死锁
--扩展事件会话的信息
select * from sys.dm_xe_sessions where name = 'system_health'
SELECT
xed.value('@timestamp','datetime')as Creation_Date,
xed.query('.')AS Extend_Event
FROM
(
SELECT CAST([target_data] AS XML)AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address= xt.event_session_address
WHERE xs.name=N'system_health'
AND xt.target_name=N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')AS XEventData(xed)
ORDER BY Creation_Date DESC
死锁报警
实质是开启1222，然后读取日志文件，然后发送邮件
开启数据库邮件发送
IF EXISTS (
SELECT 1 FROM sys.configurations
WHERE NAME = 'Database Mail XPs' AND VALUE = 0)
BEGIN
PRINT 'Enabling Database Mail XPs'
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
END
配置数据库邮件
新建作业，设置警报，设置步骤
DECLARE @starttime VARCHAR(30),
@endtime VARCHAR(30),
@Cmd VARCHAR(500),
@servername NVARCHAR(150),
@mysubject NVARCHAR(200),
@body VARCHAR(2000)
SET @endtime = CONVERT(VARCHAR(30), GETDATE(), 126)
SET @starttime = CONVERT(VARCHAR(30), DATEADD(MI, -1, GETDATE()), 126);
SET @Cmd = 'EXEC master.dbo.xp_readerrorlog 0, 1, null, null, ' + '''' + @starttime + '''' + ', '
+ '''' + @endtime + '''' + ', ' + 'N''ASC'''
SET @servername = @@servername
SET @mysubject = '[Warning]：Deadlock event notification on server ' + @servername + ' at '+@starttime
SET @body = N'Deadlock has occurred.Please refer to attachement or use below sql statement to check deadlock detalis：
' + @Cmd
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBMailProfile',
@recipients='herw@novasoftware.cn',
@subject=@mysubject,
@body=@body,
@query=@Cmd,
@attach_query_result_as_file=1,
@query_attachment_filename=N'deadlock log.txt',
@query_result_width=32767,
@exclude_query_output=1,
@append_query_error=1;
为什么查询慢？是不是死锁了？
这个问题被大量的开发人员问过。查询慢有很多情况，归根结底是资源问题，但是绝大部分情况是由于设计、编码导致的。不合理、低效地操作数据库，导致资源利用不合理甚至不足，从而发生性能问题。查询慢主要是因为在并发特别是悲观并发模式下，互相阻塞和锁过多、过久造成的其他会话等待，甚至死锁，从而表现出来运行慢。至于死锁导致查询慢，其实很少见，因为在通常情况下，死锁5s内就会被SQL Server终止。所以更准确地来说，是阻塞而不是死锁导致慢。
阻塞
会话等待的锁
SELECT
der.[session_id]
,der.[blocking_session_id]
,sp.lastwaittype
,sp.hostname
,sp.program_name
,sp.loginame
,der.[start_time] AS '开始时间'
,der.[status] AS '状态'
,dest.[text] AS 'sql语句'
,DB_NAME(der.[database_id]) AS '数据库名'
,der.[wait_type] AS '等待资源类型'
,der.[wait_time] AS '等待时间'
,der.[wait_resource] AS '等待的资源'
,der.[logical_reads] AS '逻辑读次数'
FROM sys.[dm_exec_requests] AS der
INNER JOIN master.dbo.sysprocesses AS sp
ON der.session_id = sp.spid
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
-- WHERE [session_id]>50 AND session_id<>@@SPID
ORDER BY der.[session_id]
GO;
具体资源
resource_associated_entity_id
72057594051624960
SELECT OBJECT_NAME(i.object_id) ,
i.name
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE p.partition_id = 72057594051624960
发现是Bill表，寻找特定的id需要去Page里面找，这里我们就使用工具 http://www.sqllockfinder.com，去捕捉或者获取等待锁
