专栏首页天马行空布鲁斯浅析一个sql server数据库事务死锁问题

浅析一个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

本文分享自微信公众号 - 天马行空布鲁斯(gh_2feda5c053bd),作者:huazailmh

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-02-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 空谈系统架构设计之高并发、高可用

    对于一个应用系统,特别是互联网系统,高并发、高可用是两个非常重要的非功能性需求,这篇文章尝试从应用系统架构角度分析如何满足这两个特性。

    Bruce Li
  • 空谈stateless和stateful

    其实,关于这两个词语,我们经常听到,网上也有很多文章介绍,大家可以自行搜索。这里只谈一谈个人对它们的理解。

    Bruce Li
  • 关于html的input元素,property和attribute的区别

    之前在项目中遇到一个很tricky的关于html的input元素的问题,个人觉得挺有意思,于是记录下来。这个问题也是在ui的自动化测试中,可能会碰到的一个问题。

    Bruce Li
  • Apache2为什么会自动加载index.php

    我直接输入域名后,Apache2自动加载了对应目录下的index.php, 这是怎么做到的?

    Jerry Wang
  • Elasticsearch Index Setting一览表

    索引的配置项按是否可以更改分为static属性与动态配置,所谓的静态配置即索引创建后不能修改。

    丁威
  • R练习50题 - 第五期

    21. 上证50、沪深300、去除上证50的沪深300指数日收益率的相关系数矩阵?

    用户7652506
  • 【AlexeyAB DarkNet框架解析】七,YOLOV1损失函数代码详解(detection_layer.c)

    灵魂拷问,你真的懂YOLOV1的损失函数吗?进一步,懂了损失函数,你清楚它的反向求导过程吗?为了解决这俩问题,本文就结合DarkNet中的YOLOV1的损失函数...

    BBuf
  • Python_二维数组

    py3study
  • 封装Python列表实现多下标访问

    class MyArray(object): def __init__(self, values): #values can be of...

    Python小屋屋主
  • nginx 配置目录转发

    server { listen 80; autoindex off; server_name image.imooc.com; ...

    Dar_Alpha

扫码关注云+社区

领取腾讯云代金券