专栏首页天马行空布鲁斯浅析一个postgresql的死锁问题

浅析一个postgresql的死锁问题

之前写过一篇文章(浅析一个sql server数据库事务死锁问题),简单分析了一个sql server数据库的死锁问题及索引对执行计划的影响;这里继续分享一个postgresql的死锁问题。

一般来说,数据库死锁问题都是由于两个或多个复杂事务产生了对锁的循环依赖造成的。最简单的例子就是,事务1执行两个update语句:

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务2以相反的顺序执行同样的update语句:

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

在高并发的情况下,就容易产生死锁。

其实不光是update+update语句会产生死锁;正如之前文章中提到的例子,insert+select语句也会产生死锁,原因是由于索引的创建方式不一样,导致锁的粒度不一样。而这里要分享的死锁问题是两个insert语句产生的。

问题原型

同样的,抽象问题的原型如下:

有一张学生表,表结构如下(仅有四个字段加id主键约束):

CREATE TABLE public.student (
id int4 NOT NULL,
address varchar(255) NULL,
email varchar(255) NULL,
"name" varchar(255) NULL,
CONSTRAINT student_pkey PRIMARY KEY (id)
);

接下来有两个事务执行批量插入操作,事务1先插入student3,再插入student4,如下:

begin transaction
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('3','Jack','Dallas, TX','jack@gmail.com');
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('4','Jack','Dallas, TX','jack@gmail.com');
commit

而事务2先插入student4,再插入student3,如下:

begin transaction
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('4','Jack','Dallas, TX','jack@gmail.com');
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('3','Jack','Dallas, TX','jack@gmail.com');
commit

在两个事务并行执行的场景下,有时候会产生死锁问题,具体的错误如下:

SQL Error [40P01]: ERROR: deadlock detected
Detail: Process 55 waits for ShareLock on transaction 569; blocked by process 57.
Process 57 waits for ShareLock on transaction 568; blocked by process 55.
Hint: See server log for query details.
Where: while inserting index tuple (0,14) in relation "student_pkey"

问题原因

后面发现,当上面两个事务(事务1和事务2)交叉并行执行时,就会发生死锁。具体流程是:

事务1先执行完第一条insert语句;接着事务2开始执行,事务2第一条insert语句执行没有问题,执行第二条insert语句时会pending住;这时,事务1继续执行第二条inset语句,死锁就发生了。

那么,为什么事务2执行第二条insert语句会pending住呢?

原因是,事务1先插入了student3,事务2先插入了student4,它们都没有commit,但是由于id上有一个index,index存储会把没有commit的数据也存起来;当事务2执行第二条insert语句时,发现student3在index里面已经有了,于是就等待事务1结束;这时事务1继续执行第二条insert语句,同样的,发现student4在index里面也有了,于是就等待事务2结束;在这种情况下,死锁就产生了。具体可参考下面解释:

A Postgres index stores not only committed data, but also stores data written by ongoing transactions. Postgres will look for the tuple we’re attempting to insert in both the committed and “dirty” (not-yet-committed) sections of the index. https://rcoh.svbtle.com/postgres-unique-constraints-can-cause-deadlock

通过查看数据库lock情况也可以看出,当事务2执行第二条insert语句时pending在获取一个sharelock上。

(事务1和事务2都执行完第一条语句时的lock情况)

(事务2执行完第二条语句时的lock情况)

延申

  1. 由于JDBC事务是基于连接实现,如果用DBeaver测试上面死锁问题,你可能需要设置成打开一个editor tab就打开一个新的连接,如下:
  1. postgresql里面死锁的发生可能跟下面几个timeout参数的设置都有关系,请注意: deadlock_timeout lock_timeout statement_timeout

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

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

原始发表时间:2020-09-21

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ​空谈Security之Authentication和Authorization

    authentication和authorization这两个单词看起来很像,并且它们经常被一起提及到,但是,请注意,它们指代的是不同的概念,authentic...

    Bruce Li
  • 那些年用过的开源项目(一)

    近期微软收购github新闻热搜各大媒体,微软,曾经开源的极力反对者,近几年也是积极拥抱开源。我司近来也是积极的拥抱开源,贡献了包括ui5,cloudfoudr...

    Bruce Li
  • Security知识阶段汇总

    去年参与了很多公司组织的security活动,并且给自己team,其他team做过一些security相关的分享,今年公司security相关的活动又陆续开始了...

    Bruce Li
  • 运维必备--如何彻底解决数据库的锁超时及死锁问题

    之前有介绍过,我主要是做数据仓库运维的,业余也会动手写 python 程序,django 应用,vue 的 app,有兴趣可以加我好友一起学习。最近比较让我头疼...

    somenzz
  • MySQL锁

      MySQL的锁机制,就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。

    lyb-geek
  • Python -- 文件/目录 方法

    py3study
  • flash退出舞台,网站视频加密该如何做?

    随着谷歌等浏览器不在支持flash,网站视频课程加密也必须考虑新的方案,而不能在使用原来的是flash加密,那么替代方案是什么呢?能不能达到和flash一样的加...

    点量小芹DolitQin520
  • LintCode-408.二进制求和

    悠扬前奏
  • 20181111_ARTS_week20

    When to use the :empty and :blank CSS pseudo selectors

    Bob.Chen
  • C#基于RSA加密算法实现软件注册实战演练

    2、加密算法有几种分类? 1)、对称式加密技术 2)、非对称式加密技术 3)、对称式加密与非对称式加密特点对比

    跟着阿笨一起玩NET

扫码关注云+社区

领取腾讯云代金券