数据库内核月报 - 2015 / 06

MySQL · 捉虫动态 · 唯一键约束失效

唯一键是数据库设计中常用的索引类型,主要用于约束数据,不允许出现重复的键值记录。可以想象,如果唯一键约束失效了,将可能产生可怕的逻辑错误。本文主要讨论下最近MySQL爆出来的两个唯一键约束失效导致二级索引corruption的问题。

问题一: 检查重复键加锁逻辑不当

影响版本:MySQL 5.6.21之前,5.6.12之后的版本

介绍分析

在5.6.12之前的版本中,当插入一条带唯一约束的记录时,如果表上已经存在了这条记录,或者有一条标记删除的相同键值记录时,就需要对这条记录加S GAP (类型为LOCK_ORDINARY)锁,即使你使用的是READ-COMMIT的隔离级别。因此有人report了bug#68021 ,认为在RC级别下,检查duplicate key时无需加GAP锁,在MySQL 5.6.12版本里针对RR级别依然加LOCK_ORDINARY类型的S锁,而针对RC级别加LOCK_REC_NOT_GAP类型的S锁。

上述修复带来了严重的退化,在RC隔离级别下,使用DELETE + 并发INSERT冲突键值的场景,将可能触发唯一键失效,我们简单描述下冲突产生的过程:

  1. 开启一个session,执行flush tables tbname for export,这会使purge操作停下来;
  2. 删除某条记录,其二级索引为uk1, 执行的是标记删除,由于purge被我们人为的停止,因此这条记录不会立刻被清理掉;
  3. 插入记录,包含唯一索引记录uk1,由于step 2的记录还在(没被purge),因此需要检查唯一性,在函数row_ins_scan_sec_index_for_duplicate中,根据隔离级别在记录上加S NOT GAP 锁,唯一性检查后提交mtr释放block锁;
  4. 和step 3 类似,另外一个session也插入uk1, 同样加上S NOT GAP锁,因为S锁是相容的,因此可以成功加上锁,提交mtr释放block锁;
  5. 两个session现在可以进行插入,因为受block x锁限制,插入过程是顺序的。但两次插入都能成功,原因是在做插入锁检查时,会检查相邻记录是否存在与(LOCK_X LOCK_GAP LOCK_INSERT_INTENTION)相冲突的锁,而GAP 锁和NOT GAP的S锁是不冲突的(参考函数lock_rec_has_to_wait), 因此两次插入都能顺利进行下去。

修复

直接把针对 bug#68021 的补丁给revert了。也就是说,在检查duplicate key时总是加GAP类型的S锁(LOCK_ORDINARY),这样上述过程的加锁类型可以归纳为:

SESSION1 持有 LOCK_ORDINARY S LOCK
SESSION2 持有 LOCK_ORDINARY S LOCK
SESSION1 INSERT RECORD ... CONFLICT, ENQUEUE (LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION) ——> WAIT
SESSION2 INSERT RECORD ... CONFLICT, ENQUEUE LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION ——> DEAD LOCK HAPPEN

如上描述,这里会有一定的几率发生死锁,并且死锁信息通常让人无法捉摸,如果你发现两条插入相同唯一键的SQL出现在死锁信息里,那有很大的可能是这个问题导致的。

问题二:锁继承逻辑缺陷导致约束失效(bug#76927)

影响版本:MySQL 5.1 ~ MySQL 5.7全系列版本,上游已确认,尚未fix。

介绍分析

这个问题是最近Percona的开发人员Alexey发现的,触发条件是一次DELETE + 并发REPLACE INTO操作,DELETE和REPLACE操作相同的唯一键值。

和INSERT操作不同,通过REPLACE INTO、LOAD DATAFILE REPLACE、INSERT…ON DUPLICATE执行的SQL,在检查唯一建约束时,总是给冲突的记录加LOCK_ORDINARY类型的X锁 (而非上例的S锁)。

问题产生的场景如下:

  1. 和上例一样,先让purge线程暂时停止下来;
  2. 删除包含uk1的记录,由于purge已经停止了,记录会留在物理文件中不会被及时清理掉;
  3. 执行REPLACE INTO,插入一条包含uk1的记录,由于存在标记删除但尚未清理的冲突键值,且当前操作为replace into,因此给记录加LOCK_ORDINARY类型的X锁;完成冲突检测后,提交mtr释放block锁;
  4. 开启另外一个session执行REPLACE INTO,同样插入冲突键值UK1,由于Step 3 已经加了X锁,因此这里再加X锁产生锁等待,进入等待队列。这时候我们查看innodb_locks表,会发现已经存在两个锁对象了

     mysql> select * from information_schema.innodb_locks;
     +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
     | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
     +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
     | 1300:6:4:2 | 1300 | X | RECORD | `test`.`t1` | a | 6 | 4 | 2 | 1 |
     | 1299:6:4:2 | 1299 | X | RECORD | `test`.`t1` | a | 6 | 4 | 2 | 1 |
     +------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
     2 rows in set (0.00 sec)
    
  5. 开启purge线程,purge操作会清理掉之前标记删除的物理记录,然而在step3 和step4上已经在这条记录上加了记录锁,记录被清掉了,对应的锁记录也需要做处理,InnoDB会尝试将锁继承给下一条记录,我们来看看锁继承的逻辑,调用函数lock_rec_inherit_to_gap

     for (lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no);
          lock != NULL;
          lock = lock_rec_get_next(heap_no, lock)) {
    
             if (!lock_rec_get_insert_intention(lock)
                 && !((srv_locks_unsafe_for_binlog
                       || lock->trx->isolation_level
                       <= TRX_ISO_READ_COMMITTED)
                      && lock_get_mode(lock) == LOCK_X)) {
    
                     lock_rec_add_to_queue(
                             LOCK_REC | LOCK_GAP | lock_get_mode(lock),
                             heir_block, heir_heap_no, lock->index,
                             lock->trx, FALSE);
             }
     }
    

    当满足如下条件时,不会做锁继承:

    • 锁类型为插入意向锁
    • srv_locks_unsafe_for_binlog打开且锁类型为X锁
    • 锁对应事务的隔离级别小于等于RC且锁类型为X锁

    由于当前的隔离级别为RC,并且REPLACE INTO操作加的是X锁,因此锁没有被相邻记录继承,我们从INNODB_LOCKS系统表中也可以发现这一点:

     mysql> select * from information_schema.innodb_locks;
     Empty set (0.00 sec)
    
  6. 唤醒第二个replace 操作(正在等待X锁),执行插入操作成功;
  7. 唤醒第一个replace 操作,由于已经完成duplicate key检测,插入成功。

修复

从上述逻辑可以看出,当purge线程被激活后,记录和记录锁对象都被移除了,purge操作悄悄的破坏了InnoDB的加锁协议。

修复的方法也比较简单,InnoDB认为只可能加S锁来维持一致性约束,因此当记录被物理删除时,只有S类型的锁才被继承。但对于REPLACE这样的操作,加的是X类型的锁,这种锁类型必须也要考虑进去,将其继承给下一条记录。Alexey已经将patch push到percona server,改动也就一行,可以参考Percona的 补丁

问题三:事务可见性导致的唯一键“失效”

我们来看看另外一个在REPEATABLE READ 隔离级别下,唯一键“失效”的问题,考虑如下执行序列。

创建测试表:create table t1 (a int primary key, b int unique key) engine = innodb;

session 1:
mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)

session 2:
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)

session 1:

mysql> insert into t1 values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)

b列是唯一键,session1成功插入一条刚被删除的相同键值,并且能查询出来两条相同键值的记录。看起来似乎是唯一键约束被破坏了,这实际上和InnoDB的内部实现有关。

在上述序列中,session 2执行删除操作,将唯一键进行标记删除,由于session1 已经开启了一个活跃的视图,根据REPEATABLE-READ的可见性原则,session 2所做的数据变更对session 1而言是不可见的,purge线程也无法去物理清理该记录。只要session 1不提交事务,总应该能看到被标记删除的记录(1,2)。

当session 1插入相同唯一键值记录(2,2)时,会检查到文件中存在冲突的唯一建,但修改该唯一键的事务已经提交,因此session 1认为插入记录(2,2)是合法的,完成插入后,唯一索引页上就存在两条物理记录,并且对session 1都是可见的。

这个问题是不是bug很难界定,毕竟他没有违反RR级别下可见性原则,唯一索引数据本身也是完好的,据我所知,PostgreSQL也遵循相同的逻辑。