记一次 MySql 数据库死锁问题排查过程

未来技术站 2020-04-10

155

作者 | 小牛

Java 工程师，关注服务端技术

通过观察生产环境日志发现了一个数据库死锁问题。下面是问题排查过程的记录。

项目使用的是 MySql 数据库，版本为 5.7.24-log，使用默认存储引擎 InnoDB，默认事务隔离级别：Repeatable Read（可重复读），相关数据表如下：

CREATE TABLE `bookingproperty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookingId` int(11) NOT NULL COMMENT '预约Id',
  `bookingType` tinyint(4) NOT NULL COMMENT '预约类型',
  `type` tinyint(4) NOT NULL COMMENT '属性类型',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '属性名',
  `value` varchar(500) NOT NULL DEFAULT '' COMMENT '属性值',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unqBookingIdTypeName` (`bookingId`,`bookingType`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=615 DEFAULT CHARSET=utf8mb4 COMMENT='预约属性';
复制

一、死锁原因

1.1 死锁日志分析

通过下面的语句查看死锁日志：

show engine innodb status;
复制

日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-29 16:20:38 0x700002de5000
*** (1) TRANSACTION:
TRANSACTION 208825, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 947, OS thread handle 123145350713344, query id 40607 localhost 127.0.0.1 root update
insert into `clinic`.`bookingproperty` (`bookingId`, `bookingType`, `type`, `name`, `value`) values (99, 0, 0, 'INSERT_TEST', '0') on duplicate key update `clinic`.`bookingproperty`.`bookingId` = 99, `clinic`.`bookingproperty`.`bookingType` = 0, `clinic`.`bookingproperty`.`type` = 0, `clinic`.`bookingproperty`.`name` = 'INSERT_TEST', `clinic`.`bookingproperty`.`value` = '0'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1196 page no 4 n bits 624 index unqBookingIdTypeName of table `clinic`.`bookingproperty` trx id 208825 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 156 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 800000ca; asc     ;;
 1: len 1; hex 82; asc  ;;
 2: len 21; hex 564552494649434154494f4e5f53484545545f4944; asc VERIFICATION_SHEET_ID;;
 3: len 4; hex 80000032; asc    2;;

*** (2) TRANSACTION:
TRANSACTION 208824, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 946, OS thread handle 123145350434816, query id 40601 localhost 127.0.0.1 root update
insert into `clinic`.`bookingproperty` (`bookingId`, `bookingType`, `type`, `name`, `value`) values (98, 0, 0, 'INSERT_TEST', '0') on duplicate key update `clinic`.`bookingproperty`.`bookingId` = 98, `clinic`.`bookingproperty`.`bookingType` = 0, `clinic`.`bookingproperty`.`type` = 0, `clinic`.`bookingproperty`.`name` = 'INSERT_TEST', `clinic`.`bookingproperty`.`value` = '0'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1196 page no 4 n bits 624 index unqBookingIdTypeName of table `clinic`.`bookingproperty` trx id 208824 lock_mode X locks gap before rec
Record lock, heap no 156 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 800000ca; asc     ;;
 1: len 1; hex 82; asc  ;;
 2: len 21; hex 564552494649434154494f4e5f53484545545f4944; asc VERIFICATION_SHEET_ID;;
 3: len 4; hex 80000032; asc    2;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1196 page no 4 n bits 624 index unqBookingIdTypeName of table `clinic`.`bookingproperty` trx id 208824 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 156 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 800000ca; asc     ;;
 1: len 1; hex 82; asc  ;;
 2: len 21; hex 564552494649434154494f4e5f53484545545f4944; asc VERIFICATION_SHEET_ID;;
 3: len 4; hex 80000032; asc    2;;

*** WE ROLL BACK TRANSACTION (1)
复制

通过死锁日志获取信息如下：

(1) TRANSACTION:
内容得知事务 1 的语句为：

insert into `clinic`.`bookingproperty` (`bookingId`, `bookingType`, `type`, `name`, `value`) values (99, 0, 0, 'INSERT_TEST', '0') on duplicate key update `clinic`.`bookingproperty`.`bookingId` = 99, `clinic`.`bookingproperty`.`bookingType` = 0, `clinic`.`bookingproperty`.`type` = 0, `clinic`.`bookingproperty`.`name` = 'INSERT_TEST', `clinic`.`bookingproperty`.`value` = '0'
复制

(2) TRANSACTION
信息得知事务 2 的语句为：

insert into `clinic`.`bookingproperty` (`bookingId`, `bookingType`, `type`, `name`, `value`) values (98, 0, 0, 'INSERT_TEST', '0') on duplicate key update `clinic`.`bookingproperty`.`bookingId` = 98, `clinic`.`bookingproperty`.`bookingType` = 0, `clinic`.`bookingproperty`.`type` = 0, `clinic`.`bookingproperty`.`name` = 'INSERT_TEST', `clinic`.`bookingproperty`.`value` = '0'
复制

(1) WAITING FOR THIS LOCK TO BE GRANTED
中的信息：

lock_mode X locks gap before rec insert intention waiting
复制

得知事务 1 等待 ”插入意向锁“；

(2) HOLDS THE LOCK(S)
中的信息：

lock_mode X locks gap before rec
复制

得知事务 2 持有 ”间隙锁“；

(2) WAITING FOR THIS LOCK TO BE GRANTED
中的信息：

lock_mode X locks gap before rec insert intention waiting
复制

得知事务 2 等待 ”插入意向锁“；

分析得知：两个事务同时持有间隙锁，等待插入意向锁，形成死锁。

1.2 规则

在解读死锁日志、找寻死锁发生原因前，我们需要先了解一些相关的约定和规则。

对于几种行锁对应的死锁日志描述如下：

记录锁（LOCKRECNOTGAP）: lockmode X locks rec but not gap
间隙锁（LOCKGAP）: lockmode X locks gap before rec
Next-key 锁（LOCKORNIDARY）: lockmode X
插入意向锁（LOCKINSERTINTENTION）: lock_mode X locks gap before rec insert intention

行锁兼容矩阵（横向是已持有锁，纵向是正在请求的锁）

1.3 锁机制

Mysql 的锁机制可以简单划分为：锁模式（lockmode）和锁类型（locktype）。

锁模式

LOCK_S：读锁
共享锁（Share locaks，S 锁），其他事务可读取，不能修改，可以同时有多个事务对记录加读锁。
LOCK_X：写锁
排他锁（Exclusive locks，X 锁），其他事务都不可以读取和修改，同一时间只能有一个事务加写锁。
LOCK_IS：读意向锁
LOCK_IX：写意向锁
表级锁，当事务试图读或写某一条记录时，会先在表上加上意向锁，然后在要操作的记录上加读锁或写锁。这样可以很方便地判断表中是否有记录加锁。
LOCKAUTOINC：自增锁
简称 AI 锁，当插入数据且表中有自增列时，会在生成自增值前为该表加 AI 锁。

锁类型

锁类型可以分为表锁和行锁。表锁会对整张表加锁，由 Mysql 服务器实现。行锁会锁住某一行、某几行或行之间的间隙，由存储引擎实现，如InnoDb。

下面主要介绍一下上文提到的几种行锁。

记录锁（Record Lock）

单个行记录下的锁，会锁住索引值记录。如果没有设置索引会使用隐式的主键来锁定。

间隙锁（Gap Lock）

又称范围锁（Range Locks），加在两个索引之间或第一个索引之前，或者最后一个索引之后。并不锁定索引本身。

在可重复读隔离级别下才会产生间隙锁, 它可以防止其他事物在加锁范围内插入或修改记录，保证两次读取加锁范围内的记录数据不变，避免幻读发生。

在可重复读隔离级别下，标准的 SQL 规范是会存在幻读问题的，但 MySql 通过间隙锁的技术避免了幻读。但有利也有弊，通过间隙锁虽然解决了幻读问题，但增加了数据库的开销，影响了数据库的并发性能。

产生间隙锁的场景：

使用普通索引
使用多列唯一索引
使用唯一索引

范围查询，或索引对应记录不存在，会产生间隙锁

上文中的间隙锁就是由于使用了多列唯一索引产生的。

Next-Key 锁（Next-Key Locks）

可以认为是记录锁和间隙锁的组合，既锁定范围，又锁定索引记录本身。

插入意向锁（Insert Intention Locks）

是一种特殊的间隙锁，也简写成 II GAP。与上文中提到的读、写意向锁是两个不同的概念，只会在 INSERT 时才会有这个锁。

这个时候再来看上文中的行锁兼容矩阵，在持有间隙锁或 Next-key 锁时，请求插入意向锁会有冲突。因为间隙锁的作用就是防止幻读，而正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行，进而避免了幻读的产生。

二、场景重现

为了模拟并发操作，重现死锁问题和测试死锁修复代码。利用 CountDownLatch
简单写了个测试类如下：

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
//@Ignore
public class PropertyTest {

    **
     * 并发数量
     */
    public static final int THREAD_NUM = 100;
    private static long startTime = 0L;
    private static AtomicInteger atomicInteger = new AtomicInteger(0);
    @Autowired
    private PropertyTestService propertyTestService;

    @Test
    public void testConcurrentInsert() {
        try {
            startTime = System.currentTimeMillis();
            log.info("test started at: " + startTime);

             初始化计数器
            CountDownLatch startLatch = new CountDownLatch(1);
            CountDownLatch endLatch = new CountDownLatch(THREAD_NUM);

            for (int i = 0; i < THREAD_NUM; i++) {
                new Thread(new TestRun(startLatch, endLatch)).start();
            }

             启动多个线程
            startLatch.countDown();
            endLatch.await();
        } catch (Exception e) {
            Assert.assertFalse(e instanceof SQLException);
        }
    }

    @After
    public void afterTest() {
        propertyTestService.deleteTestProperty();
    }

    **
     * 线程类
     */
    @AllArgsConstructor
    private class TestRun implements Runnable {
        private final CountDownLatch startLatch;
        private final CountDownLatch endLatch;

        @Override
        public void run() {

             线程等待
            try {
                startLatch.await();
            } catch (InterruptedException e) {
                log.error(e.getMessage());
            }

             执行操作
            propertyTestService.insertTestProperty(atomicInteger.incrementAndGet());

            long endTime = System.currentTimeMillis();
            log.info(Thread.currentThread().getName() + " ended at: " + endTime + ", cost: " + (endTime - startTime) + " ms.");
            endLatch.countDown();
        }
    }


}

复制

在 propertyTestService.insertTestProperty 中加了 Thread.sleep(THREAD_SLEEP_TIME) 用来模拟长事务。

通过测试发现，在较短的事务下，同时发起几十个请求并没有触发死锁问题。而随着事务的加长，死锁的概率也逐渐加大。通过查阅相关代码，发现发生死锁的方法被包在一个大的事务里面，这个事务甚至还包含了多次远程调用。

其实本项目已经对分布式事务做了处理，保证了事物的一致性，同时也避免了长事务的产生。只不过项目较大，开发时间跨度也长，有些地方遗漏了处理。

三、总结

避免死锁或者减少死锁情况发生及降低死锁损失的方法：

避免长事务，尽量将长事务拆成多个短事务来处理；因为长事务占用资源多，耗时长，与其他事务冲突的概率也高；
合理设置索引；
业务场景允许的情况下，可以减低事务的隔离级别，避免间隙锁的产生；
如上文场景，可以直接使用 insert
或者 insert ignore
；
设置 innodb_lock_wait_timeout
，可以避免高并发时大量事务无法获取到锁而挂起，从而引发更严重的问题；
业务场景允许的情况下可以使用悲观锁。

全文完

以下文章您可能也会感兴趣：

浅谈 BI 与数据分析的可视化
Hadoop 生态之 MapReduce 及 Hive 简介
从对称加密到非对称加密再到认证中心 -- https 的证书申请
简单聊聊 TCP 的可靠性
一篇文章带你搞懂 Swagger 与 SpringBoot 整合
延时队列：基于 Redis 的实现
你真的懂 Builder 设计模式吗？论如何实现真正安全的 Builder 模式
锁优化的简单思路
iOS开发：Archive、ipa 和 App 包瘦身
压力测试必知必会

我们正在招聘 Java 工程师，欢迎有兴趣的同学投递简历到 rd-hr@xingren.com 。

mysql

文章转载自未来技术站，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

记一次 MySql 数据库死锁问题排查过程

一、死锁原因

1.1 死锁日志分析

1.2 规则

1.3 锁机制

锁模式

锁类型

记录锁（Record Lock）

间隙锁（Gap Lock）

Next-Key 锁（Next-Key Locks）

插入意向锁（Insert Intention Locks）

二、场景重现

三、总结

Hadoop 生态之 MapReduce 及 Hive 简介

评论

相关阅读