浅析一个sql server数据库事务死锁问题

之前遇到过一个sql server数据库事务死锁问题，这里记录下来分享给大家。

问题的原型

为了描述方便，这里抽象问题的原型如下：

一个学生管理系统，数据库是sql server，有一个Web API用于创建student。student对象的表结构如下：

CREATE TABLE [public].[A_Student](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [name] [nvarchar](50) NOT NULL,
  [remark] [nvarchar](50) NULL
) ON [PRIMARY]

其中id是primary key。（note: primary key会自动创建一个clustered index）

创建一个student的实现逻辑可以简化为下面一个事务（包含一个插入语句和一个查询语句）：

BEGIN TRAN
INSERT INTO public.[A_Student] ([name] ,[gender] ,[remark]) VALUES ('john', 'male','good student!')
SELECT [id] from public.[A_Student] where name = 'john'
COMMIT TRAN

在高并发测试过程中发现，这段逻辑会发生事务死锁问题，异常信息如下：

"Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

问题的原因

后来研究发现，当上面的创建逻辑有两个并行事务（T1和T2）交叉执行时，死锁问题就会发生。具体原因如下：

T1和T2同时执行完insert语句，都会对新增的行加X锁；然后，当T1和T2都执行select语句时，都需要申请所有行的S锁（note: 由于name字段没有加index，所以需要执行clustered index scan），这时T1就pending在T2的X锁上，T2则pending在T1的X锁上，死锁就发生了。

针对这个问题，有两个解决方案：

1. 把name字段加一个index；
2. 把select语句加上with nolock

对于方案1，加上index之后，select语句就不会再有一个clustered index scan，只会是index seek，意味着只会申请某条记录的S锁，所以就不会发生死锁。

对于方案2，把select语句加上with nolock后，语句执行时直接就不加锁，锁循环依赖就不存在了，死锁也就解决了。当然，不加锁，一定程度会出现脏读，但是在这个业务场景下，不影响。

延申

一、没有添加任何索引的时候，查询语句（select id from table where name = 'john'）的执行计划是table scan；

当给id加上clustered index之后，语句的执行计划是clustered index scan；

当给name加上index之后，语句的执行计划就是index seek了。

为什么select的字段是id，where的条件是字段name，这里会走index seek呢？

一般来说，select的字段需要是执行计划用到的index包含的字段，这样才会走index seek，如下面语句：

select name from table where name = 'john'

但这里走index seek却应用到了另外一个概念”覆盖查询“，具体含义如下：

当索引包含查询中的所有列时，性能可以提升。 查询优化器可以找到索引内的所有列值；不会访问表或聚集索引数据，这样就减少了磁盘 I/O 操作。 使用具有包含列的索引来添加覆盖列，而不是创建宽索引键。 如果表有聚集索引，则该聚集索引中定义的列将自动追加到表上每个非聚集索引的末端。 这可以生成覆盖查询，而不用在非聚集索引定义中指定聚集索引列。 例如，如果一个表在 C列上有聚集索引，则 B 和 A 列的非聚集索引将具有其自己的键值列 B、 A和 C。 https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#Nonclustered

从上面介绍可以看到，聚集索引会自动加到每个非聚集索引的后面形成覆盖查询，这就是为什么上面select id直接走index seek的原因。

二、另外，在测试过程中发现，当给name加上index之后，下面这条语句（select所有字段）的执行计划是clustered index scan，而不是index seek + key lookup。

select * from table where name = 'John'

原因是，在sql server中当表的数据量达到一个阈值（tipping point）的时候，执行计划可能会发生变化。当时测试过程中，表的数据量都很小，所以执行计划是clustered index scan；后来，向表中插入1503条记录之后，执行计划就变成了make sense的index seek + key lookup。关于这个机制，可以参考：

• https://www.cnblogs.com/CreateMyself/p/6117352.html
• https://stackoverflow.com/questions/25443315/index-scan-index-seek-and-table-scan/25443843#25443843

扩展

1. 关于Index的实现原理，一般来说，index的实现都是基于B树或者B+树（在二叉查找树BST的基础上，减少磁盘IO）；同时，很多数据库都还支持一些其他类型的index，比如哈希index，其实哈希index的底层原理就类似于java里面的HashMap，c#里面的Dictionary。
2. 关于聚集索引和非聚集索引，其实有的数据库并没有实现这个概念，比如postgres。sql server实现了这两个概念，详细的介绍可以参考（Clustered index: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#Clustered和non-clusterd index：https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#Nonclustered）
3. Microsoft sql server managment studio中查看执行计划快捷键Ctrl+L；查看锁使用情况EXEC sp_lock。