这是我参与8月更文挑战的第21天，活动详情查看：8月更文挑战

记一次SQL Serer中模拟死锁出现的事务未提交问题（这可能只是一个非常意外的问题）。

什么样的事务未提交问题？

先说明下是什么样的问题：

模拟两个事务执行且产生死锁；死锁“牺牲品”的事务(通常为事务1)被终止并回滚；正常情况下，事务2会执行完并提交。

但发生的问题是，事务2的会话似乎没有提交，虽然返回了受影响的行数，但是使用查询语句，查询事务中涉及的两个表时，发生阻塞，无法执行结束！！！

后面查找，是事务2的会话在阻塞执行，其会话内执行select @@TRANCOUNT;返回1，即存在未结束的事务！整个过程事务2的会话除了执行事务2以外，就执行了select @@TRANCOUNT;。

也就是，发生死锁的两个事务，作为死锁“牺牲品”的事务被终止回滚，而剩下应该正常执行完的事务，虽然返回执行了的消息，但是内部并没有提交，一直持有锁，导致所在的表无法被访问查询！！！

当然这个问题只出现了一次，后续进行了n多次测试，“牺牲品”事务终止回滚，另一个事务也执行提交，不存在未结束的事务。

后续没有重现出最开始的问题。SQL Server2016，SSMS使用的是V18.9。

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )

此处做个记录。也记录下如何查找阻塞执行的事务进程，终止会话等问题。

模拟死锁

下面模拟实现死锁，将两个事务暂停10秒和11秒分别执行（使用WAITFOR DELAY）：

事务1

-- 事务1
BEGIN TRANSACTION
UPDATE Product
	SET product_name='deadlock'
	where product_id='0001';
WAITFOR DELAY '00:00:10';
UPDATE ProductCopy
	SET product_name='deadlock'
	where product_id='0012';
COMMIT;

事务2

-- 事务2
BEGIN TRANSACTION
UPDATE ProductCopy
	SET product_name='deadlock'
	where product_id='0012';
WAITFOR DELAY '00:00:11';
DELETE FROM Product
	where product_id='0001';
COMMIT;

分别登陆两次SSMS（打开两个SSMS进程），分别在SSMS中黏贴好事务1和事务2的SQL语句。

然后依次先点击事务2的SSMS执行，和事务1的SSMS执行。然后产生死锁。

事务1作为死锁“牺牲品”，被中止执行并回滚到事务开始前：

(1 row affected)
Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

事务2正常执行并返回消息：

(1 row affected)

(1 row affected)

发现问题-死锁发生后的阻塞问题

虽然上面的死锁由SQL Server处理完了。但是，此时查询product或productcopy表，会发生阻塞...，无法正常使用两个表。

如下图所示，两个表中的数据一共分别只有4、5条或7、8条，

也就是说，发生死锁后，虽然数据库引擎自动处理了死锁并回滚牺牲品事务，执行了其他事务。但仍然出现了问题，产生阻塞，导致无法使用表。

下面查找阻塞执行的会话部分，可以看到显示阻塞执行的连接的SPID，是发生死锁的事务中，正常执行的事务所在的会话（即非死锁受害者的事务）。

查看阻塞执行的进程/会话SPID

保持一个被阻塞的查询一直进行着，然后查看确认是哪个会话产生的阻塞。

sp_who存储过程

sp_who存储过程可以查看SQL Server实例中的用户、session和processes（进程）的相关信息。

sp_who返回结果中的blk可看到阻塞进程的sessionID（如果存在），不存在则为0。

查看阻塞进程的session

EXEC sp_who;

`sys.dm_exec_requests`系统视图

sys.dm_exec_requests系统视图返回的blocking_session_id同样可以显示阻塞进程的sessionID。

sys.dm_exec_requests返回有关在 SQL Server 中执行的每个请求的信息

查看阻塞进程的session

-- 查看阻塞进程的session
select * from sys.dm_exec_requests where blocking_session_id>0;

dm表示Dynamic management view

`select @@spid;`查看当前会话id

select @@spid;可以返回当前连接的会话id。

解决办法

方法1：结束事务

通过上面查找阻塞的会话SPID，可以看到是67。而该会话id正是死锁的两个事务中，应该且已经显示正常执行了的事务2所在的连接。

此时，在正常执行的事务2所在会话中，查询@@TRANCOUNT;，可以看到仍然存在未结束的事务，也就是事务2执行了但是还未结束！

SELECT @@SPID;

select @@TRANCOUNT;

当前的session_id（67）正是导致阻塞其他事务执行的会话。

由于存在未结束的事务，单独执行一次提交（或者回滚），结束当前的事务，这样可以解决对其他事务的阻塞：

COMMIT;

方法2：中止阻塞的会话

1）Activity Monitor中止阻塞的会话或进程

使用SSMS的活动监视器（Activity Monitor）中止会话

如下，在SSMS中打开SSMS Activity Monitor：

在process进程一项中，找到要终止的SPID：

右键当前进程，选择“杀死进程”（Kill Process）

2）使用KILL命令终止会话

直接使用KILL <session_id>|<spid>;终止阻塞进程的会话。

KILL 67;

KILL spid并不是粗暴的终止会话，而是有需要回滚的事务会进行回滚，以及其它的处理操作（比如备份操作的终止恢复）。

因此执行 KILL spid 通常并不会立刻结束，但是也是一种非常安全的结束会话或事务的方式。

Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed by the uncompleted transaction and database objects should be returned to the original state before these transactions. Due to this reason, you can see the status of SPID as KILLED\ROLLBACK once the rollback is in progress. Rollback might take less time in most of the case; however, it entirely depends upon the changes it needs to rollback.

KILL spid 和 KILL spid WITH STATUSONLY的区别

如果想kill任何的会话连接，可以直接使用

KILL spid;

使用KILL中止会话，如果会话内存在需要回滚的事务，将会开始执行回滚。

有时回滚事务需要花费很长时间，如果想知道正在回滚（即KILL）的状态，则可以运行KILL (spid) WITH STATUSONLY查看。

KILL spid WITH STATUSONLY显示正在回滚的事务的状态，除此，基本不做任何事情。

其他事务死锁的模拟

后面为了重现这个问题，重新执行了上面类似的事务多次；后面又将事务2的DELETE改为UPDATE，如下，多次测试，仍未出现上面的问题...

-- 事务1
BEGIN TRANSACTION
UPDATE Product
	SET product_name='deadlock'
	where product_id='0002';
WAITFOR DELAY '00:00:10';
UPDATE ProductCopy
	SET product_name='deadlock'
	where product_id='0012';
COMMIT;

-- 事务2
BEGIN TRANSACTION
UPDATE ProductCopy
	SET product_name='deadlock'
	where product_id='0012';
WAITFOR DELAY '00:00:11';
UPDATE Product
	SET product_name='deadlock'
	where product_id='0002';
COMMIT;

两者事务2和事务1先后执行。

数据库教程21：sp_who如何查找阻塞执行的会话sessionID并中止，及KILL spid WITH STATUSONLY详解