相关文章推荐
无邪的单杠  ·  c# - How to put delay ...·  1 年前    · 
个性的小蝌蚪  ·  Laravel SMTP Gmail失败 ...·  1 年前    · 
粗眉毛的蚂蚁  ·  Ubuntu 20.04 安装 ...·  1 年前    · 
分析MySQL unique key check问题

分析MySQL unique key check问题

更新时间:

本文将介绍MySQL unique key check问题现象以及产生该问题的原因。

InnoDB unique check问题

Unique secondary index是用户经常使用的场景,用来保证index上record的唯一性。但是大量的用户在使用unique secondary index后,会发现偶尔会有死锁或者锁等待异常的情况。

理论上, PolarDB 默认使用read-commit isolation level,在RC隔离级别下绝大部分场景不会使用GAP lock。因此,死锁的概率比较低。

关于InnoDB事务锁介绍请参见 InnoDB lock sys

使用 MySQL Bugs#68021 中的case描述一下这个问题。

-- Prepare test data
CREATE TABLE `ti` (
  `session_ref_id` bigint(16) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(16) DEFAULT NULL,
  `client_id` int(2) DEFAULT '7',
  `app_id` smallint(2) DEFAULT NULL,
  PRIMARY KEY (`session_ref_id`),
  UNIQUE KEY `uk1` (`customer_id`,`client_id`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (4000, 8000, 10, 5);
INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (4090, 9000, 10, 5);
INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (6000, 10000, 10, 5);
INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (7000, 14000, 10, 5);

session 1 :删除行(4090, 9000, 10, 5),然后插入一行(5000, 9000, 10, 5)。

-- session 1
session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)
session1 > DELETE FROM ti WHERE session_ref_id = 4090;
Query OK, 1 row affected (0.00 sec)
session1 > INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (5000, 9000, 10, 5);
Query OK, 1 row affected (0.00 sec)

session 2 :插入(NULL, 8001, 10, 5)时出现了锁等待。理论上不应该有锁等待, 因为pk是自增的,而二级索引(8001, 10, 5)并没有和任何record冲突。插入(NULL, 7999, 10, 5)时却没有问题,二级索引(7999, 10, 5)同样也没有和任何二级索引冲突。

-- session 2
session2 > set innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.00 sec)
session2 > start transaction;
Query OK, 0 rows affected (0.00 sec)
session2 > INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (NULL, 8001, 10, 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session2 > INSERT INTO ti (session_ref_id, customer_id, client_id, app_id) VALUES (NULL, 7999, 10, 5);
Query OK, 1 row affected (0.00 sec)

通过以下命令,查看事务锁信息。

说明

您需要使用高权限账号或拥有performance_schema库上的SELECT权限的普通账号,执行以下命令。

select ENGINE_TRANSACTION_ID, index_name, lock_type, lock_mode, LOCK_STATUS, lock_data  from performance_schema.data_locks;

显示结果如下:

+-----------------------+------------+-----------+------------------------+-------------+--------------+
| ENGINE_TRANSACTION_ID | index_name | lock_type | lock_mode              | LOCK_STATUS | lock_data    |
+-----------------------+------------+-----------+------------------------+-------------+--------------+
|              99537179 | NULL       | TABLE     | IX                     | GRANTED     | NULL         |
|              99537179 | uk1        | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 9000, 10, 5  |
|              99537176 | NULL       | TABLE     | IX                     | GRANTED     | NULL         |
|              99537176 | PRIMARY    | RECORD    | X,REC_NOT_GAP          | GRANTED     | 4090         |
|              99537176 | uk1        | RECORD    | X,REC_NOT_GAP          | GRANTED     | 9000, 10, 5  |
|              99537176 | uk1        | RECORD    | S                      | GRANTED     | 9000, 10, 5  |
|              99537176 | uk1        | RECORD    | S                      | GRANTED     | 10000, 10, 5 |
|              99537176 | uk1        | RECORD    | S,GAP                  | GRANTED     | 9000, 10, 5  |
+-----------------------+------------+-----------+------------------------+-------------+--------------+

可以看出, session 1 在uk1上持有(10000, 10, 5)和(9000, 10, 5)上的next-key lock。 session 2 在插入(8001,10,5)记录时, 使用的是正常的 INSERT 逻辑,在插入数据时需要申请insert record的下一个key上的GAP|insert_intention lock,但和trx1上持有的(9000,10,5)next-key lock冲突,所以需要等待。

如果插入(7999,10,5),需要申请的insert record下一个key是(8000,10,5)的GAP|insert_intention lock,并且没有冲突,那么就能够插入成功。

通过以下pseudocode了解二级索引的unique check的流程。

find the B-tree page in the secondary index you want to insert the value to
    assert the B-tree page is latched
    equal-range = the range of records in the secondary index which conflict with your value 
    if(equal-range is not empty){
      release the latches on the B-tree and start a new mini-transaction
      for each record in equal-range
        lock gap before it, and the record itself (this is what LOCK_S does)
      also lock the gap after the last(equal-range)
      also (before Bug #32617942 was fixed) lock the record after last(equal-range)
      once you are done with all of the above, find the B-tree page again and latch it again
    insert the record into the page and release the latch on the B-tree page.

可以看出,在二级唯一索引插入record时,分成了两个阶段。

  1. 判断当前的物理记录上是否有冲突的record(delete-marked 是不冲突)。

  2. 如果没有冲突,执行插入操作。

阶段1和阶段2之间必须有锁来保证(可以是lock,也可以是latch)。否则,阶段1判断没有冲突可以执行插入操作,但在阶段1和阶段2之间另外一个事务插入了一个冲突的record,那么阶段2再插入时,会产生冲突。

所以当前的实现为:如果gap上存在至少一个相同的record,大概率是delete-marked record。那么,需要给整个range都加上gap X lock。加了gap X lock后,就可以禁止其他事务在这个gap区间插入数据,也就是通过lock来保证阶段1和阶段2的原子性。

如果gap上没有相同的record,那么就不需要任何gap lock。例如,一个只包含pk、sk的table。

已经存在的二级索引记录(1, 1)、(4, 2)、(10(delete-mark),3)、(10(d), 8)、(10(d), 11)、(10(d), 21)和(15, 9)需要插入二级索引(10, 6),那么就需要给(10,3)、(10, 8)、(10,11)、(10,21)和(15, 9) 都加上next-key lock。

说明

您需要为(15, 9)加上next-key lock,来保证类似(10,100)这样的record也不允许被插入。但是,如果这里(15, 9)修改为(15000, 9),这里被锁的gap区间非常大。

如果将next-key lock去掉,会带来严重的 bug ,导致二级索引的唯一性约束出现问题,unique-index上会出现相同的record。

简化上述二级索引,因为(10, 5)是一样的,可以将(9000,10, 5)简化成9000。二级索引在page上的简化结构如下图:

二级索引在page上的简化结构

红色表示record已经被删除,蓝色表示未被删除。

将next-key lock改为record lock后,如果这个时候插入record(99,13000)和(120,13000)。

第一个record在unique check时对(13000,100)、(13000,102)、(13000,108)...(13000,112)所有的二级索引加records lock, insert时对(13000, 100)加GAP|insert_intention lock。

第二个record在unique check时对(13000, 100)、(13000, 102)、 (13000, 108)...(13000, 112)所有的二级索引加records lock,插入时对(13000,112)加GAP|inser_intention lock。

这时两个record都可以同时插入成功,但unique key约束失效。Mtr case详情参见 bug

MySQL官方修复InnoDB unique check问题的两个思路

  • 区分用于事务的lock和用于Unique check的lock

    InnoDB的lock必须遵守2PL的原则。基于此,next-key lock用做unique check,判断完成以后不能马上释放,必须等事务结束才能够释放。因此,MySQL官方希望区分真正的用于事务的lock和用于unique check的lock,这两种类型的lock的生命周期应该是不一样的,前者需要等到事务结束才能够释放,后者可以在当前STATEMENT结束以后就可以释放,Issue中Fungo提出:理论上,unique check判断完成后就应该马上释放。但STATEMENT生命周期太长,在 INSERT INTO VALUES 场景,前面的 INSERT 会影响后面的 INSERT

    MySQL官方已经在一些地方增加了lock_duation_t::AT_LEAST_STATEMENT类型,但是,InnoDB的lock还存在锁继承和锁复用。例如,当前需要申请一个GAP lock时,当前事务因为unique check已经存在该GAP lock,且当前的实现默认是所有的lock都在事务提交的时候一起释放,那么这次申请直接返回true。但是,如果unique check申请的GAP lock提前释放,这里就会发生冲突。因此,锁复用时也需要考虑生命周期问题。

    另外,如果在gap中有record被purge或者插入一个新的record,那么就继承了一个生命周期为STATEMENT的场景,unique check引入的GAP lock释放时该lock也要释放。

  • 通过latch来做unique check

    Latch的生命周期远远小于lock,通常来说latch=short-lived,lock=long-lived,可以在mtr提交时释放。

    如果有大量的delete-marked record,就会覆盖多个page,mtr持有的latch就会很多,mtr是InnoDB Btree修改的最小单位,如果mtr持有的page latch过多,Btree的并发性能会下降。

    另外,因为Undo purge等操作需要持有page latch,可能在持有page latch的过程中进行IO操作,那么持有latch的时间较长,造成unique check判断的时间也会过长。latch的冲突和lock的冲突处理方式不一样,latch冲突为当前线程等待的方式。lock冲突后,当前事务会进入到事务锁等待,需要等发生冲突的lock释放以后重新唤醒。详情可以参见 Goetz 的文章

如果在 row_ins_scan_sec_index_for_duplicate() 函数中将next_key lock修改为record lock,在 INSERT 阶段,将 INSERT 时申请的 LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION; 修改为 LOCK_X | LOCK_ORDINARY | LOCK_INSERT_INTENTION; 那么就演变成持有record lock,等待LOCK_ORDINARY | LOCK_INSERT_INTENTION,session2和session3就会互相冲突,无法同时插入。

执行INSERT操作时,为什么要持有LOCK_GAP而不是LOCK_ORDINARY?

例如,原来已经存在record 1、4、10,需要插入record 6、7。

Trx抢的是record 10的lock,且record 10是next record。此时record6、7 都还未在Btree中,如果为record 10加上LOCK_ORDINARY,那么插入record 6、7 就会互相等待死锁。因此只能为record 10加LOCK_GAP。

对于有可能冲突的sk,会出现互相等待死锁的现象。

例如,如果现有record(1,1)、(4,2)、(10(delete-mark),3)、 (10(d),8)、(10(d),11)、 (10(d),21)、(15,9)。需要插入trx1:(10,6)、trx2:(10,7)。您需要在trx1插入成功后,再插入trx2。

首先,您需要给(10,3)、(10,8)、(10,11)和(10,21)加records lock。插入的位置是在(10,3)和(10,8)之间,那么在申请(10,8)的LOCK_X | LOCK_ORDINARY | insert_intention时,和已经持有的records lock互相冲突,处于死锁状态。

插入(10,6)和(10,9)也一样,需要给所有(10,x)都加records lock。插入时trx1申请(10, 8)的LOCK_ORDINARY,且持有trx2需要的(10, 11)的records lock。trx2申请(10, 11)的LOCK_X 或LOCK_ORDINARY,持有trx1想要的(10, 8)的records lock。因此也会出现死锁冲突。

更多信息

Primary key也是unique key index,为什么primary key不存在此问题?

在secondary index中,由于MVCC的存在,当删除一个record,再在插入一个新的record时,保留delete marked record。

在primary index中, DELETE 后又 INSERT 一个数据,会将该record delete marked标记修改为non-delete marked,然后在undo log中记录一个delete marked的record。如果查询历史版本,会通过MVCC从undo log中恢复该数据。因此,不会出现相同的delete mark record跨多个page的情况,也就不会出现上述case中(13000,100)在page1, (13000,112)在page3的情况。

INSERT 时,和上面的二级索引插入2阶段类似,需要有latch或者lock进行保护。primary index通过持有page X latch就可以保证两个阶段的原子性,从而两次的 INSERT 不可能同时插入成功,从而避免了这个问题。

结论

DELETE + INSERT INSERT ON DUPLICATE KEY UPDATE REPLACE INTO 等场景中,为了实现判断插入记录与现有物理记录是否冲突和插入记录这两个阶段的原子性,unique check时会给所有的相同的record和下一个record加next-key lock。导致后续insert record虽然没有冲突,但仍然会被block,进而可能会出现死锁问题。