MySQL 最佳实践：一次 InnoDB 死锁 Bug 排查

/* 1. 表结构 */CREATE TABLE t  (    id BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'id, 无实际意义',    account_id VARCHAR (64) NOT NULL COMMENT '用户id，不同app下的account_id可能重复',    type TINYINT UNSIGNED NOT NULL COMMENT '余额类型 1:可用余额',    balance BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '余额',    state INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '账户状态 1:NORMAL; 2:FROZE',    UNIQUE KEY uk_account (account_id, type))ENGINE = INNODB DEFAULT CHARSET utf8mb4 COMMENT '测试';
/* 2. 其中 UNIQUE INDEX 为 uk_account(account_id, type) */
/* 3. 插入数据 */insert into t values(1,'1',1,100,1);insert into t values(2,'2',1,100,1);insert into t values(3,'3',1,100,1);insert into t values(4,'4',1,100,1);insert into t values(5,'5',1,100,1);
/* 4. 查询所有数据. */select * from t;
/* 5. 执行以下 SQL, 注意事务隔离级别为 (RR) */t1-1：(session1)begin; /* 显式开启事务, 排除 autocommit 的影响. */select * from t where account_id = '1' and type =1 for update;
t2: (session2)begin;select * from t where account_id = '1' and type =1 for update;
t1-2: (session1)update t set state = 2 where account_id = '1';

MySQL [sbtest]> select * from t where account_id = '1' and type =1 for update;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

> begin;> select * from t where account_id = '1' and type =1 for update;> update t set state = 2 where account_id = '1';> set global innodb_status_output_locks = on; /* 暂不提交，以查看事务锁的持有信息. */> show engine innodb status\G
/* 事务 t1 持有 5 个 lock, 4 个 row lock, 1 个 table lock (暂时忽略). */---TRANSACTION 2068, ACTIVE 16 sec5 lock struct(s), heap size 1200, 4 row lock(s)MySQL thread id 9, OS thread handle 140737025267456, query id 74 127.0.0.1 myadmin startingshow engine innodb status
/* 下列为 table lock (笔者注). */TABLE LOCK table `sbtest`.`t` trx id 2068 lock mode IX
/* 下列为 Record: 1,'1',1,100,1 的 X record lock 基于索引 uk_account (笔者注). */RECORD LOCKS space id 2 page no 5 n bits 72 index uk_account of table `sbtest`.`t` trx id 2068 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 31; asc 1;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000001; asc         ;;
/* 下列为 Record: 1,'1',1,100,1 的 X record lock 基于主键索引 (笔者注). */RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `sbtest`.`t` trx id 2068 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 0000000000000001; asc         ;; 1: len 6; hex 000000000809; asc       ;; 2: len 7; hex 01000001160151; asc       Q;; 3: len 1; hex 31; asc 1;; 4: len 1; hex 01; asc  ;; 5: len 8; hex 0000000000000064; asc        d;; 6: len 4; hex 00000002; asc     ;;
/* 下列为 Record: 1,'1',1,100,1 的 Next-key record lock 基于索引 uk_account. (笔者注). */RECORD LOCKS space id 2 page no 5 n bits 72 index uk_account of table `sbtest`.`t` trx id 2068 lock_mode XRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 31; asc 1;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000001; asc         ;;
/* 下列为 Record: 2,'2',1,100,1 的 GAP lock 基于索引 uk_account. (笔者注). */RECORD LOCKS space id 2 page no 5 n bits 72 index uk_account of table `sbtest`.`t` trx id 2068 lock_mode X locks gap before recRecord lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 32; asc 2;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000002; asc         ;;

(gdb) b storage/innobase/lock/lock0lock.cc:7125
/* 1. 按顺序执行 t1-1, t2, t1-2 直到 t1-2 触发死锁检测. *//* 2. DeadlockChecker 会设置一个 m_start 即发起死锁检测的 trx 和 m_wait_lock 即 m_start 等待的 lock. */
(gdb) p m_start /* 事务t1 */$9 = (const trx_t *) 0x7fffe506cc78
(gdb) p *m_wait_lock  /* 事务 t1 尝试申请的 lock, 目前在等待状态. */$53 = {trx = 0x7fffe506cc78, trx_locks = {prev = 0x7fffe00101f8, next = 0x0}, index = 0x7ff8740971f8, hash = 0x0, {tab_lock = {      table = 0x500000002, locks = {prev = 0x48, next = 0x0}}, rec_lock = {space = 2, page_no = 5, n_bits = 72}},  m_psi_internal_thread_id = 43, m_psi_event_id = 66, type_mode = 291, m_seq = 72}
/* 注意 m_wait_lock 的 type_mode */(gdb) p /t 291$13 = 100100011 /* 即 LOCK_X | LOCK_REC | LOCK_WAIT
(gdb) p m_wait_lock->index->name$22 = {m_name = 0x7ff874097538 "uk_account"} /* 可以确认是等待在唯一索引 uk_account 的 record lock. */
/* 3. s 进入函数 DeadlockChecker::search(), 会首先 get_first_lock(&heap_no) 即从 m_wait_lock 对应的 record 上的 heap_no 从 lock_sys->rec_hash 找到第一个 lock. */const lock_t *lock = get_first_lock(&heap_no);
(gdb) p lock$15 = (const ib_lock_t *) 0x7fffe0010098
(gdb) p *lock$14 = {trx = 0x7fffe506cc78, trx_locks = {prev = 0x7fffe0010cb8, next = 0x7fffe00101f8}, index = 0x7ff8740971f8, hash = 0x7fffe0010358, {    tab_lock = {table = 0x500000002, locks = {prev = 0x48, next = 0x0}}, rec_lock = {space = 2, page_no = 5, n_bits = 72}},  m_psi_internal_thread_id = 43, m_psi_event_id = 50, type_mode = 1059, m_seq = 54}
/* record 上的第一个 record 锁是 0x7fffe0010098, 它属于我们目前发起的事务 m_start 即 0x7fffe506cc78.   type_mode 是 1059 即 LOCK_X | LOCK_REC | LOCK_REC_NOT_GAP. */
/* 我们通过 lock->hash 迭代来查看整个 record 上的锁排列信息. */(gdb) p *(ib_lock_t*)0x7fffe0010098  /* 第一个 record lock */$48 = {trx = 0x7fffe506cc78, trx_locks = {prev = 0x7fffe0010cb8, next = 0x7fffe00101f8}, index = 0x7ff8740971f8, hash = 0x7fffe0011918, {    tab_lock = {table = 0x500000002, locks = {prev = 0x48, next = 0x0}}, rec_lock = {space = 2, page_no = 5, n_bits = 72}},  m_psi_internal_thread_id = 43, m_psi_event_id = 65, type_mode = 1059, m_seq = 69}
(gdb) p *(ib_lock_t*)0x7fffe0011918  /* 第二个 record lock. */$49 = {trx = 0x7fffe506d090, trx_locks = {prev = 0x7fffe00124c8, next = 0x0}, index = 0x7ff8740971f8, hash = 0x7fffe0010358, {tab_lock = {      table = 0x500000002, locks = {prev = 0x48, next = 0x0}}, rec_lock = {space = 2, page_no = 5, n_bits = 72}},  m_psi_internal_thread_id = 44, m_psi_event_id = 36, type_mode = 1315, m_seq = 71}
(gdb) p *(ib_lock_t*)0x7fffe0010358  /* 第三个 record lock. */$50 = {trx = 0x7fffe506cc78, trx_locks = {prev = 0x7fffe00101f8, next = 0x0}, index = 0x7ff8740971f8, hash = 0x0, {tab_lock = {      table = 0x500000002, locks = {prev = 0x48, next = 0x0}}, rec_lock = {space = 2, page_no = 5, n_bits = 72}},  m_psi_internal_thread_id = 43, m_psi_event_id = 66, type_mode = 291, m_seq = 72}
/* 当前事务与 record 的关系如下:发起死锁检测是 trx 是: 0x7fffe506cc78, trx 0x7fffe506cc78 等待的 lock 是 0x7fffe0010358.
lock: 0x7fffe0010098 所属的 trx: 0x7fffe506cc78 type_mode: 1059 LOCK_X | LOCK_REC | LOCK_REC_NOT_GAP 即持有 X record lock.lock: 0x7fffe0011918 所属的 trx: 0x7fffe506d090 type_mode: 1315 LOCK_X | LOCK_WAIT | LOCK_REC | LOCK_REC_NOT_GAP 即等待 X record lock.lock: 0x7fffe0010358 所属的 trx: 0x7fffe506cc78 type_mode: 291  LOCK_X | LOCK_REC | LOCK_WAIT 即等待 X record lock. */
/* 死锁检测流程结束后选择的 victim_trx 是: */(gdb) p victim_trx$58 = (const trx_t *) 0x7fffe506d090

/* 使用 8.0.26 最新版分析 lock 持有情况. * 执行 t1：    select * from t where account_id = '1' and type =1 for update;    update t set state = 2 where account_id = '1';    show engine innodb status\G; */
show engine innodb statusTABLE LOCK table `sbtest`.`t` trx id 1323 lock mode IX
/* 下列为 Record: 1,'1',1,100,1 的 X record lock 基于索引 uk_account. (笔者注). */RECORD LOCKS space id 2 page no 5 n bits 72 index uk_account of table `sbtest`.`t` trx id 1323 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 31; asc 1;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000001; asc         ;;
/* 下列为 Record: 1,'1',1,100,1 的 X record lock 基于主键索引. (笔者注). */RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `sbtest`.`t` trx id 1323 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 0000000000000001; asc         ;; 1: len 6; hex 00000000052b; asc      +;; 2: len 7; hex 020000011b0110; asc        ;; 3: len 1; hex 31; asc 1;; 4: len 1; hex 01; asc  ;; 5: len 8; hex 0000000000000064; asc        d;; 6: len 4; hex 00000002; asc     ;;
/* 下列为 Record: 1,'1',1,100,1 的 GAP record lock 基于索引 uk_account, 与上面的 uk_account 的 X lock 组成 Next-key Lock. (笔者注). */RECORD LOCKS space id 2 page no 5 n bits 72 index uk_account of table `sbtest`.`t` trx id 1323 lock_mode X locks gap before recRecord lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 31; asc 1;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000001; asc         ;;
/* 下列为 Record: 2,'2',1,100,1 的 GAP record lock 基于索引 uk_account. (笔者注: lock 的类型与上列一致, InnoDB 在这里对相同类型的 lock 做了省略). */Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 32; asc 2;; 1: len 1; hex 01; asc  ;; 2: len 8; hex 0000000000000002; asc         ;