我要评论一、背景
我们在uat环境压测的时候，遇到了如下的死锁异常。
我们立即 查看应用日志，找到报错的方法查看，发现在一个方法对同一张表进行了3种操作，先insert，然后select，最后delete。也就是说在同一个事务中，对同一张表先插入，然后查询，最后根据查询结果删除。此时，我大概意识到问题所在了。但是uat环境中，sql server数据库是部署在客户侧的，不太好拿死锁报告。所以我决定在本地模拟出来这个死锁问题，然后进行修复。
二、本地模拟死锁
1.业务场景简介
我们有一张usertoken表，核心字段有id、loginid和token，主要用来记录用户的登录token，用来控制系统中一个用户能不能多次登录。
我们出现死锁问题的方法是登录方法，该方法在登录时会向usertoken表中插入一条数据，插入成功之后回去第三方检查这个用户的状态等是否正常，因为用户数据是第三方维护的。如果检查结果是这个用户状态不可用，那么就会去删除这个用户的token数据，同时给前端返回相应的异常信息。问题就出在删除的时候，是先根据用户的loginid去查询出该用户的所有token数据，然后找出本次登录的token数据，进行删除。为什么这里有问题后面我们再详细说明。
2.在本地模拟死锁
1). 准备数据
要模拟这个死锁场景，可以在 sql server management studio (ssms) 或者dbeaver中创建一个简单的脚本，我使用的是dbeaver也很好用。使用以下存储过程代码：
-- 1.创建一个示例 usertoken 表
create table usertoken (
id int identity(1,1) primary key,
loginid varchar(50),
token varchar(50)
);
-- 2.创建一个存储过程，以模拟登录过程
create procedure sp_login
@loginid varchar(50)
as
begin
-- 插入一个新记录
insert into usertoken (loginid, token) values (@loginid, 'token_' + convert(varchar(50), newid()));
waitfor delay '00:00:05'; -- 模拟延迟，更容易发生死锁
-- 选择和删除记录
declare @id int;
select @id = id from usertoken where loginid = @loginid;
delete from usertoken where id = @id;
end;
-- 3. 在第一个窗口中模拟第一个线程
declare @loginid varchar(50) = 'user';
begin transaction;
exec sp_login @loginid;
commit transaction;
-- 4. 在第二个窗口中模拟第二个线程
declare @loginid varchar(50) = 'user';
begin transaction;
exec sp_login @loginid;
commit transaction;
-- 5. 在两个窗口中同时运行，模拟并发登录，并观察执行结果
2).执行存储过程并观察死锁发生
按照上面的步骤创建表和存储过程，并分别在两个窗口中同时执行。可能需要执行多次才能出现死锁。如果出现下面的两种之一，就说明已经发生了死锁。
情况一：
情况二：
通过sqlserver自带的扩展事件[system_health]查看死锁的详细信息，执行下面的sql如果表格中有数据则已经发生了死锁。
select xdr.value('@timestamp', 'datetime') as [date],
xdr.query('.') as [event_data]
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(xdr)
order by [date] desc;
如上图，已经发生死锁。
三、死锁的详细分析
1.查看死锁报告
在上面第二步中，我们通过sqlserver自带的扩展事件[system_health]先拿到了死锁报告。如下：
<event name="xml_deadlock_report" package="sqlserver" timestamp="2024-05-10t07:53:31.599z">
<data name="xml_report">
<type name="xml" package="package0"/>
<value>
<deadlock>
<victim-list>
<victimprocess id="process19f4497c108"/>
</victim-list>
<process-list>
<process id="process19f4497c108" taskpriority="0" logused="284" waitresource="key: 6:72057594058768384 (e8a66f387cfa)" waittime="3342" ownerid="50677" transactionname="user_transaction" lasttranstarted="2024-05-10t15:53:23.250" xdes="0x19f4c400428" lockmode="s" schedulerid="3" kpid="7120" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-05-10t15:53:23.250" lastbatchcompleted="2024-05-10t15:51:07.110" lastattention="1900-01-01t00:00:00.110" clientapp="dbeaver 24.0.2 - sqleditor <script-7.sql>" hostname="ncscnd13691rvd0" hostpid="30508" loginname="sa" isolationlevel="read committed (2)" xactid="50677" currentdb="6" currentdbname="deadlockdatabase" locktimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionstack>
<frame procname="deadlockdatabase.dbo.sp_login" line="11" stmtstart="590" stmtend="698" sqlhandle="0x03000600dfe61621f0cd05016cb1000001000000000000000000000000000000000000000000000000000000">
select @id = id from usertoken where loginid = @logini
</frame>
<frame procname="adhoc" line="4" stmtstart="124" stmtend="166" sqlhandle="0x02000000b95c920287375badb00b99eeb827a3f3037c6bda0000000000000000000000000000000000000000">
unknown
</frame>
</executionstack>
<inputbuf>
declare @loginid varchar(50) = 'user';
begin transaction;
exec sp_login @loginid;
commit transaction;
</inputbuf>
</process>
<process id="process19f4497e4e8" taskpriority="0" logused="284" waitresource="key: 6:72057594058768384 (11ea04af99f6)" waittime="2677" ownerid="50681" transactionname="user_transaction" lasttranstarted="2024-05-10t15:53:23.917" xdes="0x19f4ffdc428" lockmode="s" schedulerid="2" kpid="1248" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-05-10t15:53:23.913" lastbatchcompleted="2024-05-10t15:52:46.183" lastattention="1900-01-01t00:00:00.183" clientapp="dbeaver 24.0.2 - sqleditor <script-2.sql>" hostname="ncscnd13691rvd0" hostpid="30508" loginname="sa" isolationlevel="read committed (2)" xactid="50681" currentdb="6" currentdbname="deadlockdatabase" locktimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionstack>
<frame procname="deadlockdatabase.dbo.sp_login" line="11" stmtstart="590" stmtend="698" sqlhandle="0x03000600dfe61621f0cd05016cb1000001000000000000000000000000000000000000000000000000000000">
select @id = id from usertoken where loginid = @logini
</frame>
<frame procname="adhoc" line="5" stmtstart="128" stmtend="170" sqlhandle="0x020000009bc16a079a9d61241dde15013e2cc413cd9c26920000000000000000000000000000000000000000">
unknown
</frame>
</executionstack>
<inputbuf>
declare @loginid varchar(50) = 'user';
begin transaction;
exec sp_login @loginid;
commit transaction;
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594058768384" dbid="6" objectname="deadlockdatabase.dbo.usertoken" indexname="pk__usertoke__3213e83fcab09e1a" id="lock19f4f504a00" mode="x" associatedobjectid="72057594058768384">
<owner-list>
<owner id="process19f4497e4e8" mode="x"/>
</owner-list>
<waiter-list>
<waiter id="process19f4497c108" mode="s" requesttype="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594058768384" dbid="6" objectname="deadlockdatabase.dbo.usertoken" indexname="pk__usertoke__3213e83fcab09e1a" id="lock19f4f509180" mode="x" associatedobjectid="72057594058768384">
<owner-list>
<owner id="process19f4497c108" mode="x"/>
</owner-list>
<waiter-list>
<waiter id="process19f4497e4e8" mode="s" requesttype="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</value>
</data>
</event>2.分析死锁报告
首先，在死锁发生的过程中，我们可以通过以下sql查询当前表锁持有的锁有哪些。
--将usertoken换成自己的表名
select * from sys.dm_tran_locks where resource_type = 'object' and resource_database_id = db_id() and resource_associated_entity_id = object_id('usertoken');
我们可以看到在死锁发生的过程中，usertoken表上有2把ix锁（意向排他锁）。应该就是上面执行存储过程中的2条insert语句产生的。
接下来，我们来详细分析一下死锁报告的内容，以了解为什么会出现死锁。
a.牺牲的进程
从报告上我们可以看到<victimprocess>，牺牲的进程是 process19f4497c108，它被suspend并等待共享锁在一个关键资源上。在sqlserver中当发生死锁时，sqlserver会选择牺牲其中的一个死锁，释放它所持有的锁，从而打破死循环。
b.进程列表
通过<process-list>我们可以看到本次有两个进程参与了死锁。
process19f4497c108（被牺牲的进程）
process19f4497e4e8
两个进程都在执行 sp_login 存储过程，该过程将新记录插入到 usertoken 表中，然后根据 loginid 列选择和删除记录。从<executionstack>可以看到是在执行select @id = id from usertoken where loginid = @loginid的时候阻塞了，也就是去根据loginid去查询的时候阻塞了。
这两个进程分别等待的资源是：key: 6:72057594058768384 (e8a66f387cfa)和key: 6:72057594058768384 (11ea04af99f6)。
key值的含义：key表示等待的资源是一个键，也就是索引中的特定行或行范围。以key: 6:72057594058768384 (e8a66f387cfa)为例。6代表数据库id，72057594058768384代表被锁索引（index）的id，也就是某一个索引，(e8a66f387cfa)代表索引中内部id，也就是在该索引中具体是哪一行，可以帮我们定位到表中特定的数据行。
关于前两个，比较简单可以通过系统表查询出来。
--72057594058768384替换为死锁报告中的key: 6:72057594058768384 (e8a66f387cfa)的中间数字部分
select db_id() as database_id, o. name, i. name, i. type from sys. indexes i
inner join sys.objects o on i.object_id = o.object_id
inner join sys.partitions p on p.index_id = i.index_id and p. object_id = i. object_id
where p.partition_id = 72057594058768384从下面的结果中可以看到和报告下面index_name一致，锁定就是主键索引
关于(e8a66f387cfa)代表索引中内部id，可以通过一个未公布的系统函数 %%lockres%% 查看得到，如下
with cte as
(
select %%lockres%% as resource_key, id from usertoken with(index(pk__usertoke__3213e83fcab09e1a))--替换为自己的表名和死锁报告中冲突的索引
)
select * from cte where resource_key in ( '(e8a66f387cfa)', '(11ea04af99f6)');--替换为死锁报告中等待的resource_keyc.资源列表
从<resource-list>中可以看到，有两个关键的锁在usertoken表上。
lock19f4f504a00：由 process19f4497e4e8 拥有，具有排他（x）锁模式
lock19f4f509180：由 process19f4497c108 拥有，具有排他（x）锁模式
死锁发生是因为每个进程都在等待共享锁在一个资源上（usertoken 表的 pk__usertoke__3213e83fcab09e1a 索引），而该资源已经被另一个进程以排他锁模式拥有的。
d.死锁场景
下面是死锁报告中描述的死锁场景：
- process19f4497c108将一条新记录插入到usertoken表中，并获取了索引（pk__usertoke__3213e83fcab09e1a）的排他锁（mode='x'）。
- process19f4497e4e8将一条新记录插入到usertoken表中，并获取了索引（pk__usertoke__3213e83fcab09e1a）的排他锁（mode='x'）。
- process19f4497c108 尝试根据 loginid 去查询usertoken表中的数据，由于process19f4497e4e8 持有了索引的排他锁，所以process19f4497c108必须等待锁的释放。
- process19f4497e4e8 尝试根据 loginid 去查询usertoken表中的数据，由于process19f4497c108持有了索引的排他锁，所以process19f4497e4e8 必须等待锁的释放。
- 此时，两个进程都在等待对方释放锁，结果导致死锁。
e.结论
死锁是由于 sp_login 存储过程的并发执行导致的，这导致了 usertoken 表上的争用。每个进程在 索引上的排他锁阻止了另一个进程执行其选择和删除操作，导致死锁。因为两个进程都持有了 usertoken 表的 pk__usertoke__3213e83fcab09e1a 索引的排他锁（mode='x'），每个进程都在等待另一个进程释放其锁。
要解决这个问题，我们可以优化存储过程以减少 usertoken 表上的争用。
四、解决死锁问题
有了上面对死锁报告的详细分析，我们了解到了死锁产生的原因是锁竞争。那么我们可以减少一层锁，以避免锁的竞争。修改后存储过程如下：
-- 2.创建一个存储过程，以模拟登录过程
create procedure sp_login
@loginid varchar(50)
as
begin
-- 插入一个新记录
insert into usertoken (loginid, token) values (@loginid, 'token_' + convert(varchar(50), newid()));
-- 直接根据loginid删除记录，减少一次查询，减少一次s锁的获取
delete from usertoken where loginid = @loginid;
end;
-- 3. 在第一个窗口中模拟第一个线程
declare @loginid varchar(50) = 'user1';
begin transaction;
exec sp_login @loginid;
commit transaction;
-- 4. 在第二个窗口中模拟第二个线程
declare @loginid varchar(50) = 'user2';
begin transaction;
exec sp_login @loginid;
commit transaction;
-- 5. 在两个窗口中同时运行，模拟并发登录，并观察执行结果
再次多次执行上面的存储过程，没有再遇到过死锁了。
新的存储过程分析：
在这个修改后的场景中，我们可以看到，每个窗口中都执行了一个事务，该事务包括插入一条记录、删除该记录、并提交事务。
在这种情况下，死锁的可能性非常小，因为每个窗口中的事务都是自包含的，不会等待另一个窗口中的事务释放锁。
- 当第一个窗口执行
insert语句时，它会获取该索引的 x 锁，并插入一条记录。然后，它执行delete语句，删除该记录，并释放 x 锁。最后，它提交事务。 - 同样，第二个窗口执行
insert语句时，它会获取该索引的 x 锁，并插入一条记录。然后，它执行delete语句，删除该记录，并释放 x 锁。最后，它提交事务。 - 由于每个窗口中的事务都是独立的，不会等待另一个窗口中的事务释放锁，因此死锁的可能性非常小。
通过以上步骤，成功解决这个死锁问题。
发表评论