精华内容
下载资源
问答
  • 如何排查死锁问题

    2021-01-18 21:28:42
    死锁是进程死锁的简称,是由Dijkstra于1965年研究银行家算法时首先提出来的。它是计算机系统乃至并发程序设计中最难处理的问题之一。我们平时比较会常遇到的应该就是数据库死锁了...

    死锁是进程死锁的简称,是由 Dijkstra 于 1965 年研究银行家算法时首先提出来的。它是计算机系统乃至并发程序设计中最难处理的问题之一。我们平时比较会常遇到的应该就是数据库死锁了,例如下面就是我最近排查的一个死锁问题:

    本篇文章就借这个死锁问题的分析过程,来给大家讲一讲如何分析死锁问题。

    死锁原理

    在排查死锁问题前,我们先了解一下死锁相关的一些基本概念以及产生死锁的原理,之前我在公众号也发过一篇相关的文章,感兴趣可以详细读一下:用个通俗的例子讲一讲死锁

    假设我们有一把蓝钥匙,可以打开一扇蓝色的门;以及一把红钥匙,可以打开一扇红色的门。两把钥匙被保存在一个皮箱里。同时我们定义六种行为:获取蓝钥匙,打开蓝色门,归还蓝钥匙,获取红钥匙,打开红色门,归还红钥匙。

    游戏规则是:一个人(线程)必须通过排列六种指令的顺序,打开两扇门,最后归还钥匙。假设我们现在有两个线程来同时进行上面的操作:

    当两个线程都运行到第三步的时候,线程A在等线程B归还红钥匙,线程B在等线程A归还蓝钥匙,因而两个线程都永远卡在那里无法前进。这就是形成了死锁。

    般来说死锁的出现必须满足以下四个必要条件:

    互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放。

    只有一副钥匙

    请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放。

    拿着红钥匙的人在没有归还红钥匙的情况下,又提出要蓝钥匙

    不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放。

    人除非归还了钥匙,不然一直占用着钥匙

    环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合{P0,P1,P2,···,Pn}中的P0正在等待一个P1占用的资源;P1正在等待P2占用的资源,……,Pn正在等待已被P0占用的资源。

    要避免出现死锁的问题,只需要破坏四个条件中的任何一个就可以了。

    Mysql 死锁

    锁的类型

    在 MySQL 中锁的种类有很多,但是最基本的还是表锁和行锁:表锁指的是对一整张表加锁,一般是 DDL 处理时使用,也可以自己在 SQL 中指定;而行锁指的是锁定某一行数据或某几行,或行和行之间的间隙。

    mysql> lock table products read;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> select * from products where id = 100;
     
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)
    

    行锁的加锁方法比较复杂,但是由于只锁住有限的数据,对于其它数据不加限制,所以并发能力强,通常都是用行锁来处理并发事务。行锁和表锁对比如下:

    • 表锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;

    • 行锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    一般发生死锁的情况也都是在行锁,所以我们下面重点看看行锁。

    锁和索引

    我们都知道,数据库中索引的作用是方便服务器根据用户条件快速查找数据库中的数据,mysql innodb 的锁是通过锁索引来实现的。例如我们执行一条查询语句 select for update  如果字段没有索引,即使使用 wehre 条件也会进行表级锁。如果有索引,会锁定对应 where 条件中索引值的所有行,可理解为对该索引值进行了索引 有索引,而且使用了不同的索引值查数据,但是查询的结果是同一行,可以理解为真正的数据行锁。

    行锁的类型

    在 MySQL 的源码中定义了四种类型的行锁,这里我们简单提一下。

    • 记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap

      • 最简单的行锁,将锁锁在行上,这一行记录不能被其他人修改。

    • 间隙锁(LOCK_GAP): lock_mode X locks gap before rec

      • 加在两个索引之间的锁,使用间隙锁可以防止其他事务在这个范围内插入或修改记录,保证两次读取这个范围内的记录不会变,从而不会出现幻读现象。

    • Next-key 锁(LOCK_ORNIDARY): lock_mode X

      • 是记录锁和间隙锁的组合,它指的是加在某条记录以及这条记录前面间隙上的锁。

    • 插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

      • 种特殊的间隙锁,这个锁表示插入的意向,只有在 INSERT 的时候才会有这个锁。

    行锁的模式

    上面我们介绍了锁的类型,其实 Mysql 中的锁还有不同的模式,表示具体加的是什么锁,比如常见的锁模式有读锁和写锁,写锁又称排他锁或独占锁,对记录加了排他锁之后,只有拥有该锁的事务可以读取和修改,其他事务都不可以读取和修改,并且同一时间只能有一个事务加写锁。

    一个死锁案例

    好,上面的信息已经可以让我们分析一些死锁出现的常见 case 了,死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的,比如我们来看一个最经典的死锁 case:

    首先,事务 A 获取 id = 20 的锁,事务 B 获取id = 30 的锁;然后,事务 A 试图获取id = 30 的锁,而该锁已经被事务 B 持有,所以事务 A 等待事务 B 释放该锁,然后事务 B 又试图获取 id = 20 的锁,这个锁被事务 A 占有,于是两个事务之间相互等待,导致死锁。

    Mysql 死锁日志查询

    现在我们回来看死锁的报警,通过日志可以定位问题大概发生在什么位置,但是仍然无法定位是什么语句,这时我们可以查询数据库中的死锁日志来帮助我们分析问题到底处在哪里。

    我们可以到数据库服务器执行 SHOW ENGINE INNODB STATUS命令,这个命令可以获取系统最近一次发生死锁时的加锁情况。

    分析死锁日志

    上面的日志比较长,下面我们来逐行分析一下上面的死锁日志的含义。

    *** (1) TRANSACTION:
    
    TRANSACTION 519070***, ACTIVE 0 sec fetching rows
    

    ACTIVE 0 sec 表示事务活动时间,inserting 为事务当前正在运行的状态,可能的事务状态有:fetching rows,updating,deleting,inserting 等。

    mysql tables in use 3, locked 3
    
    LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
    

    tables in use 3 表示有3个表被使用,locked 3 表示有3个表锁。LOCK WAIT 表示事务正在等待锁,5 lock struct(s) 表示该事务的锁链表的长度为 5,每个链表节点代表该事务持有的一个锁结构,包括表锁,记录锁以及 autoinc 锁等。

    heap size 1136 为事务分配的锁堆内存大小。3 row lock(s) 表示当前事务持有的行锁个数。

    MySQL thread id 6716703, OS thread handle 1401379786***, query id 235468*** 10.245.**.** arg99446*** Searching rows for update
    

    事务的线程信息,以及数据库 IP 地址和数据库名,对我们分析死锁用处不大。

    UPDATE issues_scm SET scm_id=188,issues_id=75,code_rule_id=83,code=2,result=0 WHERE issues_id = 75 AND scm_id = 188
    

    这里显示的是正在等待锁的 SQL 语句,我们还要结合应用程序去具体分析这个 SQL 之前还执行了哪些其他的 SQL 语句

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    
    RECORD LOCKS space id 183 page no 4 n bits 424 index issue**** of table ****.issues_scm trx id 519070*** `lock_mode X locks rec but not gap waiting`
    

    这里显示的是事务正在等待什么锁,可以看出要加锁的索引为 issue****。lock_mode X 表示该记录锁为排他锁, rec but not gap waiting 表示要加的锁为记录锁,并处于锁等待状态。

    *** (2) TRANSACTION:
    
    TRANSACTION 51907**, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1011
    
    mysql tables in use 3, locked 3
    
    5 lock struct(s), heap size 1136, 7 row lock(s)
    
    MySQL thread id 20264658, OS thread handle 1401379****, query id 2354687863 10.245**** arg994**** Searching rows for update
    
    UPDATE issues_scm SET scm_id=187,issues_id=75,code_rule_id=83,code=2,result=0 WHERE issues_id = 75 AND scm_id = 187
    
    *** (2) HOLDS THE LOCK(S):
    
    RECORD LOCKS space id 183 page no 4 n bits 424 index issue**** of table ****.issues_scm trx id 51907**** lock_mode X locks rec but not gap
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    
    RECORD LOCKS space id 183 page no 6 n bits 424 index PRIMARY of table ****.issues_scm trx id 51907**** lock_mode X locks rec but not gap waiting
    
    *** WE ROLL BACK TRANSACTION (2)
    

    事务二和事务一的日志基本类似,不过它多了一部分 HOLDS THE LOCK(S),表示事务二持有什么锁,这个锁往往就是事务一处于锁等待的原因。这里可以看到事务二正在等待索引 issue**** 上的记录锁。

    好了日志分析完了,我们再回想一下分析一个死锁需要的必要信息

    • 事务 1 持有的锁,事务 1 等待的锁

    • 事务 2  持有的锁,事务 2 等待的锁

    再来看日志中的信息:

    • 第一个框:事务1的等待

    • 第二个框:事务2的持有

    • 第三个框:事务2的等待

    如果,再有 事务2等待 == 事务1持有,死锁就成立了,但是 log 中看不到:事务1持有,但是因为死锁已经成立了,所以我们断定,事务2的等待 == 事务1的持有。

    接下来,我们只要分析,为什么事务1会持有这个锁就好了。

    解决方案

    通过上面的死锁 log ,我们只能获取一部分信息,要知道为什么事务1会持有这个锁 还要根据具体业务进行分析,其实关键问题即出在下面这两条 sql:

    UPDATE issues_scm SET scm_id=188,issues_id=75,code_rule_id=83,code=2,result=0 WHERE issues_id = 75 AND scm_id = 188
    
    UPDATE issues_scm SET scm_id=187,issues_id=75,code_rule_id=83,code=2,result=0 WHERE issues_id = 75 AND scm_id = 187
    

    我们再来看看数据库的表结构:

    可以看到,issues_id、scm_id 两个字段分别被加了索引,实际上 issues_id、scm_id 的一个值都分别会对应多个字段的,达成事务隔离级别的要求,事务会尽可能的把所有影响的行都锁住。所以两条 sql 锁住的行的范围可能是有重叠的,这样在并发执行的时候,如果两条 sql 加锁的顺序不一致,就会出现死锁。

    实际上,在刚才的操作中,我们并不希望两条 sql 都分别对范围加锁,因为【issues_id、scm_id】实际上可以决定一条唯一记录,我们两个事务也只需要锁住单行就可以了。

    解决方法就是将两个字段建成 uniq 索引,这样并发时不同的 sql 也只是会锁住各自更新的单行,不会出现有 gap 的情况,也就不会发生死锁。

    在众多 Mysql 死锁问题中,这只是相对简单的一个,但是只要掌握上面分析的要点,把两个事务的持有、等待的锁分析清楚,那么一定能找到问题原因以及解决方案,祝大家好运!

    关于奇舞精选

    《奇舞精选》是360公司专业前端团队「奇舞团」运营的前端技术社区。关注公众号后,直接发送链接到后台即可给我们投稿。

    奇舞团是360集团最大的大前端团队,代表集团参与W3C和Ecma会员(TC39)工作。奇舞团非常重视人才培养,有工程师、讲师、翻译官、业务接口人、团队Leader等多种发展方向供员工选择,并辅以提供相应的技术力、专业力、通用力、领导力等培训课程。奇舞团以开放和求贤的心态欢迎各种优秀人才关注和加入奇舞团。

    展开全文
  • 某天晚上,同事正在发布,突然线上大量报警,很多是关于数据库死锁的,报警提示信息如下: {"errorCode":"SYSTEM_ERROR","errorMsg":"nestedexceptionisorg.apache.ibatis.exceptions.PersistenceException: ...

    一、现象

    某天晚上,同事正在发布,突然线上大量报警,很多是关于数据库死锁的,报警提示信息如下:

    {"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org.apache.ibatis.exceptions.PersistenceException: 
    
    Error updating database. Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] 
    
    Deadlock found when trying to get lock; 
    
    The error occurred while setting parameters\n### SQL: 
    
    update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW'

    通过报警,我们基本可以定位到发生死锁的数据库以及数据库表。先来介绍下本文案例中涉及到的数据库相关信息。

    二、背景情况

    我们使用的数据库是Mysql 5.7,引擎是InnoDB,事务隔离级别是READ-COMMITED。

    1、数据库版本查询方法:

    SELECT version();

    2、引擎查询方法:

    show create table fund_transfer_stream;

    建表语句中会显示存储引擎信息,形如:ENGINE=InnoDB

    3、事务隔离级别查询方法:

    select @@tx_isolation;

    4、事务隔离级别设置方法(只对当前Session生效):

    set session transaction isolation level read committed;

    PS:注意,如果数据库是分库的,以上几条SQL语句需要在单库上执行,不要在逻辑库执行。

    发生死锁的表结构及索引情况(隐去了部分无关字段和索引):

    CREATE TABLE `fund_transfer_stream` ( 
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `gmt_create` datetime NOT NULL COMMENT '创建时间',
      `gmt_modified` datetime NOT NULL COMMENT '修改时间', 
      `pay_scene_name` varchar(256) NOT NULL COMMENT '支付场景名称', 
      `pay_scene_version` varchar(256) DEFAULT NULL COMMENT '支付场景版本',
      `identifier` varchar(256) NOT NULL COMMENT '唯一性标识',
      `seller_id` varchar(64) NOT NULL COMMENT '卖家Id',
      `state` varchar(64) DEFAULT NULL COMMENT '状态', `fund_transfer_order_no` varchar(256) 
      DEFAULT NULL COMMENT '资金平台返回的状态', 
      PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier` 
      (KEY `idx_seller` (`seller_id`),
      KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水';

    该数据库共有三个索引,1个聚簇索引(主键索引),2个非聚簇索(非主键索引)引。

    聚簇索引:

    PRIMARY KEY (`id`)

    非聚簇索引:

    KEY `idx_seller` (`seller_id`),
    
    KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

    以上两个索引,其实idx_seller_transNo已经覆盖到了idx_seller,由于历史原因,因为该表以seller_id分表,所以是先有的idx_seller,后有的idx_seller_transNo

    三、死锁日志

    当数据库发生死锁时,可以通过以下命令获取死锁日志:

    show engine innodb status

    发生死锁,第一时间查看死锁日志,得到死锁日志内容如下:

    Transactions deadlock detected, dumping detailed information.
    2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB: 
    
    *** (1) TRANSACTION:
    TRANSACTION 173268495, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1
    MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating
    
    update `fund_transfer_stream` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))
    2019-03-19T21:44:23.516321+08:00 5877341 [Note] InnoDB: 
    
    *** (1) HOLDS THE LOCK(S):
    RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap
    Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
    
    2019-03-19T21:44:23.516565+08:00 5877341 [Note] InnoDB: 
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting
    Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
    2019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB: 
    
    *** (2) TRANSACTION:
    TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81
    mysql tables in use 1, locked 1
    302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1
    MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating
    
    update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))
    2019-03-19T21:44:23.517855+08:00 5877341 [Note] InnoDB: 
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap
    Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
    
    2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB: 
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waiting
    Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
    
    2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

    简单解读一下死锁日志,可以得到以下信息:

    1、导致死锁的两条SQL语句分别是:

    update `fund_transfer_stream_0056` 
    set `gmt_modified` = NOW(), `state` = 'PROCESSING' 
    where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))

    update `fund_transfer_stream_0056` 
    set `gmt_modified` = NOW(), `state` = 'PROCESSING' 
    where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))

    2、事务1,持有索引idx_seller_transNo的锁,在等待获取PRIMARY的锁。

    3、事务2,持有PRIMARY的锁,在等待获取idx_seller_transNo的锁。

    4、因事务1和事务2之间发生循环等待,故发生死锁。

    同时,通过cloud DBA也能看到发生死锁时资源占有及等待情况如下图,和死锁日志中得到的信息基本一致。

    5、事务1和事务2当前持有的锁均为:lock_mode X locks rec but not gap

    两个事务对记录加的都是X 锁,No Gap锁,即对当行记录加锁,并为加间隙锁。

    X锁:排他锁、又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

    与之对应的是S锁:共享锁,又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

    Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。

    Next-Key Lock:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。

     

    四、问题排查

    根据我们目前已知的数据库相关信息,以及死锁的日志,我们基本可以做一些简单的判定。

    首先,此次死锁一定是和Gap锁以及Next-Key Lock没有关系的。因为我们的数据库隔离级别是RC(READ-COMMITED)的,这种隔离级别是不会添加Gap锁的。前面的死锁日志也提到这一点。

    然后,就要翻代码了,看看我们的代码中事务到底是怎么做的。核心代码及SQL如下:

    @Transactional(rollbackFor = Exception.class)
    public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
        fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
        return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo, FundTransferStreamState.PROCESSING.name());
    }

    该代码的目的是先后修改同一条记录的两个不同字段,updateFundStreamId SQL:

    update fund_transfer_stream
            set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo}
            where id = #{id} and seller_id = #{sellerId}复制代码

    updateStatus SQL:

    update fund_transfer_stream
        set gmt_modified=now(),state = #{state}
        where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId}
        and state = 'NEW'复制代码

    可以看到,我们的同一个事务中执行了两条Update语句,这里分别查看下两条SQL的执行计划:

     

    1

     

    updateFundStreamId执行的时候使用到的是PRIMARY索引。

     

    1

     

    updateStatus执行的时候使用到的是idx_seller_transNo索引。

    通过执行计划,我们发现updateStatus其实是有两个索引可以用的,执行的时候真正使用的是idx_seller_transNo索引。这是因为MySQL查询优化器是基于代价(cost-based)的查询方式。因此,在查询过程中,最重要的一部分是根据查询的SQL语句,依据多种索引,计算查询需要的代价,从而选择最优的索引方式生成查询计划。

    我们查询执行计划是在死锁发生之后做的,事后查询的执行计划和发生死锁那一刻的索引使用情况并不一定相同的。但是,我们结合死锁日志,也可以定位到以上两条SQL语句执行的时候使用到的索引。即updateFundStreamId执行的时候使用到的是PRIMARY索引,updateStatus执行的时候使用到的是idx_seller_transNo索引。

    有了以上这些已知信息,我们就可以开始排查死锁原因及其背后的原理了。通过分析死锁日志,再结合我们的代码以及数据库建表语句,我们发现主要问题出在我们的idx_seller_transNo索引上面:

     KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

    索引创建语句中,我们使用了前缀索引,为了节约索引空间,提高索引效率,我们只选择了fund_transfer_order_no字段的前20位作为索引值。

    因为fund_transfer_order_no只是普通索引,而非唯一性索引。又因为在一种特殊情况下,会有同一个用户的两个fund_transfer_order_no的前20位相同,这就导致两条不同的记录的索引值一样(因为seller_id 和fund_transfer_order_no(20)都相同 )。

    就如本文中的例子,发生死锁的两条记录的fund_transfer_order_no字段的值:99010015000805619031958363857和99010015000805619031957477256这两个就是前20位相同的。

     

    1

     

    那么为什么fund_transfer_order_no的前20位相同会导致死锁呢?

    五、加锁原理

    我们就拿本次的案例来看一下MySql数据库加锁的原理是怎样的,本文的死锁背后又发生了什么。

    我们在数据库上模拟死锁场景,执行顺序如下:

     

    1

     

    我们知道,在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

    主键索引的叶子节点存的是整行数据。在InnoDB中,主键索引也被称为聚簇索引(clustered index)

    非主键索引的叶子节点的内容是主键的值,在InnoDB中,非主键索引也被称为非聚簇索引(secondary index)

    所以,本文的示例中涉及到的索引结构(索引是B+树,简化成表格了)如图:

     

    1

     

    死锁的发生与否,并不在于事务中有多少条SQL语句,**死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。**那么接下来就看下上面的例子中两个事务的加锁顺序是怎样的:

     

    1

     

    下图是分解图,每一条SQL执行的时候加锁情况:

     

    1

     

    结合以上两张图,我们发现了导致死锁的原因: 事务1执行update1占用PRIMARY = 1的锁 ——> 事务2执行update1 占有PRIMARY = 2的锁; 事务1执行update2占有idx_seller_transNo = (3111095611,99010015000805619031)的锁,尝试占有PRIMARY = 2锁失败(阻塞); 事务2执行update2尝试占有idx_seller_transNo = (3111095611,99010015000805619031)的锁失败(死锁);

    事务在以非主键索引为where条件进行Update的时候,会先对该非主键索引加锁,然后再查询该非主键索引对应的主键索引都有哪些,再对这些主键索引进行加锁。)

    六、解决方法

    至此,我们分析清楚了导致死锁的根本原理以及其背后的原理。那么这个问题解决起来就不难了。

    可以从两方面入手,分别是修改索引和修改代码(包含SQL语句)。

    修改索引:只要我们把前缀索引 idx_seller_transNo中fund_transfer_order_no的前缀长度修改下就可以了。比如改成50。即可避免死锁。

    但是,改了idx_seller_transNo的前缀长度后,可以解决死锁的前提条件是update语句真正执行的时候,会用到fund_transfer_order_no索引。如果MySQL查询优化器在代价分析之后,决定使用索引 KEY idx_seller(seller_id),那么还是会存在死锁问题。原理和本文类似。

    所以,根本解决办法就是改代码:

    * 所有update都通过主键ID进行。
    * 在同一个事务中,避免出现多条update语句修改同一条记录。

    七、总结与思考

    在死锁发生之后的一周内,我几乎每天都会抽空研究一会,问题早早的就定位到了,修改方案也有了,但是其中原理一直没搞清楚。

    前前后后做过很多中种推断及假设,又都被自己一次次推翻。最终还是要靠实践来验证自己的想法。于是我自己在本地安装了数据库,实战的做了些测试,并实时查看数据库锁情况。show engine innodb status ;可以查看锁情况。最终才搞清楚原理。

    简单说几点思考:

    1、遇到问题,不要猜!!!亲手复现下问题,然后再来分析。

    2、不要忽略上下文!!!我刚开始就是只关注死锁日志,一直忽略了代码中的事务其实还执行了另外一条SQL语句(updateFundStreamId)。

    3、理论知识再充足,关键时刻不一定想的起来!!!

    4、坑都是自己埋的!!!

     

    展开全文
  • 大家好: 最后我的网站在比较多人用的时候会时不时的出现有记录死锁后导制无法访问 ...另如何来查看:我的哪一条SQL语句长时间占用着记录,或者说是哪一条SQL语句让数据库死锁了 有没有相应的排查工具呢? 谢谢
  • 一旦遇到该如何排查问题呢? 环境: MySQL 5.7.25 引擎 InnoDB 如果你的系统日志突然报这种错误,就问你慌不慌?心想:MD,之前遇到过,但完全不记得该怎么办了!!!完了完了!被领导知道我解决不了这个问题,...

    阅读原文:面对数据库死锁差点跪

    数据库死锁这个问题不知道你有没有遇到过呢?一旦遇到该如何排查问题呢?

    环境: MySQL 5.7.25 引擎 InnoDB

    如果你的系统日志突然报这种错误,就问你慌不慌?心想:MD,之前遇到过,但完全不记得该怎么办了!!!完了完了!被领导知道我解决不了这个问题,不会被开除吧!

    2019-10-23 13:07:17.144 ERROR nested exception is org.springframework.dao.DeadlockLoserDataAccessException: 
    ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    ### The error may involve com.x.x.mapper.XMapper.update-Inline
    ### The error occurred while setting parameters
    ### SQL: UPDATE tb_a SET start_time = ?, end_time = ? WHERE  id = ?
    ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    

    想我一个堂堂工作几年的开发者,挂在这个地方,那岂不是很没面子啊!操练起来。

    什么是死锁?

    当多个进程访问同一数据库时,其中每个进程拥有的其他进程所需的,由此造成每个进程都无法继续下去。 简单的说,进程A等待进程B释放他的资源,B又等待A释放他的资源,这样就互相等待就形成死锁

    查看数据库基本信息

    查看数据库版本:select version();

    事务隔离级别查询方法:select @@tx_isolation

    通过命令show engines查看一下InnoDB的特点

    Engine Support Comment
    InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys
    MyISAM YES MyISAM storage engine

    InnoDB支持事务,行级锁及外键。

    我们平时遇到的就是多个事务之间行级锁导致的。

    分析

    业务日志中的记录太过简单,只知道哪个方法的事务发生了死锁,没有多余的信息,所以我们要到数据库中寻找更多的有用信息,通过命令 show engine Innodb status 查看:

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-10-23 16:46:42 0x7fa919415700
    # 事务1
    *** (1) TRANSACTION:
    TRANSACTION 21010939, ACTIVE 1 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 255825, OS thread handle 140363604055808, query id 179915249 localhost 127.0.0.1 root updating
    UPDATE tb_b SET end_time = 1571821300000 WHERE id = 18199
    # 等待b表的X锁
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1924 page no 284 n bits 80 index PRIMARY of table `dmeeting`.`tb_b` trx id 21010939 lock_mode X locks rec but not gap waiting
    Record lock, heap no 9 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
    
    # 事务2
    *** (2) TRANSACTION:
    TRANSACTION 21010938, ACTIVE 1 sec starting index read
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 255826, OS thread handle 140364249913088, query id 179915304 localhost 127.0.0.1 root updating
    UPDATE tb_a SET  actual_start_time = 1571820362678, actual_end_time = null WHERE id = 14266
    # 持有b表的X锁
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1924 page no 284 n bits 80 index PRIMARY of table `dmeeting`.`tb_b` trx id 21010938 lock_mode X locks rec but not gap
    Record lock, heap no 9 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
    # 等待a表的X锁
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1934 page no 324 n bits 112 index PRIMARY of table `dmeeting`.`tb_a` trx id 21010938 lock_mode X locks rec but not gap waiting
    Record lock, heap no 45 PHYSICAL RECORD: n_fields 38; compact format; info bits 0
    # 回滚事务2
    *** WE ROLL BACK TRANSACTION (2)
    

    分析上面的死锁日志,能够得出以下死锁场景:

    时间序列 事务1 事务2
    1 START TRANSACTION; START TRANSACTION;
    2    
    3   UPDATE tb_b SET start_time = ? WHERE id = 18199<br />持有b表行级X锁
    4 UPDATE tb_b SET end_time = ? WHERE id = 18199<br />申请b表行级X锁  
    5   UPDATE tb_a SET actual_start_time = ?, actual_end_time = ? WHERE id = 14266<br />申请a表行级X锁
    6   Deadlock found when trying to get lock; try restarting transaction(Rollback)

    仅仅根据死锁日志分析,我是百思不得其解,在事务1中并没有显示持有a表的X锁,那么这是怎么造成死锁的呢!我就是个愣头青,就知道面对死锁日志想来想去,浪费了时间,幸得身旁有大神指点,去看看业务系统中这两个事务代码,才发现原来事务1中在时间序列2时对a表进行了更新操作,已经持有了a表的行级锁!这下就完全明白了,两个事务互相等待对方释放锁,这就是造成死锁的原因。

    死锁原因

    原因知道了,那就通过更改代码,让两个事务里表更新的顺序一致即可。

    总结排查步骤

    1. 通过业务系统日志快速定位到发生死锁的代码块
    2. 查看InnoDB的死锁日志,找出各个事务对应的代码块
    3. 通过死锁日志和业务代码推测画出死锁的事务发生场景

    降低发生死锁的概率

    1. 避免大事务,可以拆分成多个小事务,因为大事务耗时长,与其他事务发生的概率就大。
    2. 多个事务操作相同的一些资源,尽量保持顺序一致。
    3. 更新语句尽量只更新必要的字段,内容相同的字段不要更新。

    记录完整的死锁日志

    show engine innodb status 时,显示的信息不全。

    这是mysql客户端的一个bug:BUG#19825,交互式客户端限制了输出信息最大为 64KB,因此更多的信息无法显示。

    但我们可以通过开启锁监控来查看完整的日志,方式如下:

    # 建议排查问题后关闭,15秒输出一次,会导致日志越来越大
    -- 开启标准监控 开ON/关OFF
    set GLOBAL innodb_status_output=ON;
     
    -- 开启锁监控  开ON/关OFF
    set GLOBAL innodb_status_output_locks=ON;
    

    也可以通过一个专门用于记录死锁日志的参数:

    set GLOBAL innodb_print_all_deadlocks=ON;
    

    内容一般输出到 mysql error log 里,查看日志位置:select @@log_error

    锁的种类

    锁级别

    行级锁(引擎INNODB):开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    表级锁(引擎MyISAM):开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

    锁类型

    next KeyLocks锁,同时锁住记录(数据),并且锁住记录前面的Gap

    Gap锁,不锁记录,仅仅记录前面的Gap

    Recordlock锁(锁数据,不锁Gap)

    所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

    锁模式

    首先我们要知道两种最容易理解的锁模式,读加共享锁,写加排它锁。

    • LOCK_S(读锁,共享锁)
    • LOCK_X(写锁,排它锁)

    还有:

    • LOCK_IS(读意向锁)

    • LOCK_IX(写意向锁)

    • LOCK_AUTO_INC (自增锁)

    更加详细的介绍可以去看看这篇文章:https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

    http://www.throwable.club/2019/05/11/mysql-deadlock-troubleshoot-1st/#%E5%AF%BC%E8%87%B4%E6%AD%BB%E9%94%81%E7%9A%84%E5%8E%9F%E5%9B%A0


    本篇文章由一文多发平台ArtiPub自动发布

    展开全文
  • mysql死锁排查

    2020-11-19 15:49:46
    应用访问Mysql数据库的时候,如果业务逻辑写的不... 那如何排查应用的死锁问题呢,下面给大家详细介绍。先看看关于死锁信息打印的参数,默认是关闭 mysql> show variables like 'innodb_print_all_deadlocks';
    应用访问Mysql数据库的时候,如果业务逻辑写的不严谨,不规范,就会发生死锁,如果此业务逻辑调用并发高,则业务日志经常会有死锁的错误日志产生。应用发生死锁,于是dba就去排查,看数据库的错误日志,就会发现,没有任何关于死锁的日志告警,这是因为默认配置情况下,数据库是不打印任何死锁的日志信息。
    
     那如何去排查应用的死锁问题呢,下面给大家详细介绍。先看看关于死锁信息打印的参数,默认是关闭
    
    mysql> show variables like 'innodb_print_all_deadlocks';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | innodb_print_all_deadlocks | OFF   |
    +----------------------------+-------+
    1 row in set (0.01 sec)
    

    想要在发生死锁的情况打印相关信息,需要开启这个参数

    mysql> set global innodb_print_all_deadlocks=on;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show variables like 'innodb_print_all_deadlocks';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | innodb_print_all_deadlocks | ON    |
    +----------------------------+-------+
    1 row in set (0.01 sec)
    

    开启之后,下面模拟一个死锁的场景
    开启会话1,执行如下语句

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update t_test10 set name='test33' where id='1';
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    mysql> update t_test10 set name='testkii' where id='4';
    Query OK, 0 rows affected (5.38 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    

    开启会话2,执行如下语句

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update t_test10 set name='testkii' where id='4';
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    mysql> update t_test10 set name='test33' where id='1';
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    

    在会话2里,可以很明显的看到,mysql检测到Deadlock,并回滚了会话2的sql,让会话的事物能继续进行。那mysql怎么选择回滚那个会话呢,主要从回滚代价上去考虑的,谁的锁持有的少,则回滚对应的会话事物。下面看看数据库的死锁详细信息看看
    在这里插入图片描述
    在这里插入图片描述
    在数据库告警日志可以找死锁发生时,对应的sql语句和应用访问用户,应用访问IP,有了这些只会,再去找开发,看对应的代码逻辑,就能很容易的定位到问题,并解决。

    给大家举一个实际的业务场景,在电商场景,下单的时候,会有主订单表和扩展订单表,如果有一个代码接口更新订单表顺序为(主订单表,扩展订单表),而另一个代码接口更新订单表顺序为(扩展订单表,主订单表),在并发高的时候,就很容易就发送死锁。

    参考链接 :

    mysql死锁排查 :https://mp.weixin.qq.com/s/mrk3mcXpkNSdvvtIIrbSHA

    展开全文
  • 码上实战理论实践相结合,做个好好学习的宝宝!^_^关注 数据库死锁这个问题不知道你有没有遇到过呢?一旦遇到该如何排查问题呢?环境: MySQL 5.7.25...
  • 对于存在多用户并发访问的项目,如果遇到了数据库deadLock 时 ,我们如何根据服务器的日志进行排查原因呢? 当你在服务器的日志中发现了在执行某条Insert或者是update 时 发生了deadLock ,很多开发人员都会去查看这...
  • SQLSERVER死锁总结

    2018-10-18 15:32:23
    SQLSERVER死锁总结,里面详述基于SQLSERVER数据库如何排查并处理死锁的办法,及死锁产生的原因
  • 点击上方IT牧场,选择置顶或者星标技术干货每日送达发生死锁了,如何排查和解决呢?本文将跟你一起探讨这个问题准备好数据环境模拟死锁案发分析死锁日志分析死锁结果环境准备数据库隔离级别:...
  • 前言发生死锁了,如何排查和解决呢?本文将跟你一起探讨这个问题准备好数据环境模拟死锁案发分析死锁日志分析死锁结果环境准备数据库隔离级别:mysql> select @@tx_isol...
  • 死锁问题的解决和排查6.平时使用时如何避免死锁的发生 一、不可重复读和幻读的区别 不可重复读的重点是修改: 比如:在事务1中,A读取了自己的工资是1000;在事务2中,财务人员修改A工资为2000,并提交事务;在事务1...
  • 产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。 注:MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍,...
  • MySQL/InnoDB的加锁,一直是一个...产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。 注:MySQL是一个支持插件式存储引...
  • 产生死锁问题如何排查并解决?下面是不同锁等级的区别   表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高 ,并发度最低。 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;...
  • 产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。 注:MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍,...
  • 产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。 注:MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍,...
  • 产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。 注:MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍,...
  • 产生死锁问题如何排查并解决?我在工作过程中,也会经常用到,乐观锁,排它锁,等。于是今天就对这几个概念进行学习,屡屡思路,记录一下。注:MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍,都...
  • 发生死锁了,如何排查和解决呢?本文将跟你一起探讨这个问题 准备好数据环境 模拟死锁案发 分析死锁日志 分析死锁结果 环境准备 数据库隔离级别: mysql> select @@tx_isolation; +-----------------+ | @@tx_...
  • 什么是锁 JVM锁:对象,偏向,公平...如何排查死锁? jstack jvisualVM 类锁:一个类只有一个class 对象锁:一个类可以有多个实例 分布式锁 Redis DB Zookeeper 无锁分布式无锁 CAS同理 Redis分布式无...
  • Java基础篇(4.3)4、Java并发编程→ 线程安全死锁死锁如何排查、线程安全和内存模型的关系→ 锁CAS、乐观锁与悲观锁、偏向锁、轻量级锁、重量级锁、可重入锁、自旋锁、阻塞锁数据库相关锁机制、锁优化、锁消除、...
  • 产生死锁问题如何排查并解决?下面是不同锁等级的区别 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高 ,并发度最低。 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定...

空空如也

空空如也

1 2 3
收藏数 44
精华内容 17
关键字:

数据库如何排查死锁