死锁相关 变量 与 PURGE 线程停止

时间：2023-12-19 12:30:32

http://www.tuicool.com/articles/NzAFZn

https://github.com/percona/percona-server/pull/83/commits/0910ae6f52d0e7725a94cb5236115d17f0220c1a

show engine innodb status

pt-deadlock-logger

innodb_print_all_deadlocks={on|off}

mysql> show variables like "%purge%";

+-----------------------------------------+-------+

| Variable_name                           | Value |

+-----------------------------------------+-------+

| gtid_purged                             |       |

| innodb_max_purge_lag                    |      |

| innodb_max_purge_lag_delay              |      |

| innodb_purge_batch_size                 |    |

| innodb_purge_run_now                    | OFF   |

| innodb_purge_stop_now                   | OFF   |

| innodb_purge_threads                    |      |

| innodb_trx_purge_view_update_only_debug | OFF   |

| relay_log_purge                         | ON    |

+-----------------------------------------+-------+

 rows in set (0.02 sec)
set global innodb_purge_stop_now=1
会话1:
mysql> CREATE TABLE t1 (

    -> a INT NOT NULL,

    -> b INT NOT NULL,

    -> PRIMARY KEY(b),

    -> UNIQUE KEY(a)) ENGINE=INNODB;

Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO t1 VALUES (1,1),(2,2);

Query OK, 2 rows affected (0.16 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> DELETE FROM t1;

Query OK, 2 rows affected (0.17 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> REPLACE INTO t1 VALUES (1,2);

Query OK, 1 row affected (0.00 sec)
会话2：
show engine innodb status\G

---TRANSACTION 107849, ACTIVE 4 sec

4 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1

MySQL thread id 3, OS thread handle 0x2ab31a1d2940, query id 49 localhost root cleaning up

TABLE LOCK table `test`.`t1` trx id 107849 lock mode IX

RECORD LOCKS space id 237 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 107849 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;                  主健锁主的记录锁

 1: len 6; hex 00000001a549; asc      I;;

 2: len 7; hex 2c000001b0188e; asc ,      ;;

 3: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 237 page no 4 n bits 72 index `a` of table `test`.`t1` trx id 107849 lock_mode X       //已经删除了的记录 [1,1] heap no 2

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

 0: len 4; hex 80000001; asc     ;;

 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits                             //已经删除了的记录 [2,2] heap no 3

 0: len 4; hex 80000002; asc     ;;

 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 237 page no 4 n bits 72 index `a` of table `test`.`t1` trx id 107849 lock_mode X locks gap before rec

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                            //heap no 4

 0: len 4; hex 80000001; asc     ;;      二级索引CAP锁

 1: len 4; hex 80000002; asc     ;;
                                                 [MySQL bug] unique key corruption again…..

原文  http://mysqllover.com/?p=1477

最近Percona的研发人员report了一个uk corruption的bug，这个Bug不同于之前发现的bug（见我的另外一篇博客http://mysqllover.com/?p=1041），而是影响从5.1 到5.8全系列MySQL版本，应该算是设计上的缺陷吧。

创建测试表

CREATE TABLE t1 (

a INT NOT NULL,

b INT NOT NULL,

PRIMARY KEY(b),

UNIQUE KEY(a)) ENGINE=INNODB;

INSERT INTO t1 VALUES (1,1),(2,2);

0. 停止Purge操作：SET GLOBAL innodb_purge_stop_now = ON; 防止标记删除的记录被purge掉

删除表上的数据：DELETE FROM t1;  //这时候表上物理记录还存在，只是被标记删除了。

1. SESSION 1: REPLACE INTO t1 VALUES (1,2);

由于有一条标记删除的记录，检查pk上duplicate key，在聚集索引上加锁：mode=1027 (1024 +3 = LOCK_REC_NOT_GAP | LOCK_X)

堆栈：

row_ins_clust_index_entry

|--> row_ins_clust_index_entry_low

  |--> row_ins_duplicate_error_in_clust

    |--> row_ins_set_exclusive_rec_lock           //x record lock

      |-->lock_clust_rec_read_check_and_lock


检查二级索引duplicate key，由于存在标记删除的记录，这里需要加锁，类型为X锁

堆栈：

row_ins_index_entry

|-->row_ins_sec_index_entry

  |--> row_ins_sec_index_entry_low

    |--> row_ins_scan_sec_index_for_duplicate

      |--> row_ins_set_exclusive_rec_lock        //x record lock

        |--> lock_sec_rec_read_check_and_lock

当完成duplicate key检测后 （当然这里是成功的），我们让SESSION1稍微等一会（设置DEBUG SYNC同步点）

2. SESSION 2：REPLACE INTO t1 VALUES (1,3);  

尝试插入第二条记录，这条记录和SESSION1插入的uk是冲突的，pk不冲突，因此先插入pk成功，然后检查uk上的duplicate key加锁：mode =3，但是session1已经在uk为1的记录上加X锁了，因此Session 2进入锁等待

加锁堆栈：

row_ins_index_entry

|--> row_ins_sec_index_entry

  |--> row_ins_sec_index_entry_low

    |--> row_ins_scan_sec_index_for_duplicate

      |--> row_ins_set_exclusive_rec_lock

        |--> lock_sec_rec_read_check_and_lock

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)

3. 开启Purge操作：SET GLOBAL innodb_purge_run_now=ON;

后台Purge线程会去清理标记删除的二级索引记录，但之前session1和session2都有请求uk上的排他锁，记录没有了，这些锁对象也需要做对应的处理，理论上锁请求应该被下一条记录锁继承，
并转换成GAP锁

堆栈：

row_purge_step

|--> row_purge

  |--> row_purge_record_func

    |--> row_purge_del_mark

      |--> row_purge_remove_sec_if_poss

        |--> row_purge_remove_sec_if_poss_leaf

          |--> btr_cur_optimistic_delete_func

            |--> lock_update_delete

              |--> lock_rec_inherit_to_gap


我们来看看锁继承的逻辑，函数为lock_rec_inherit_to_gap：

for (lock = lock_rec_get_first(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);

　　}

}
说明:

对于如下场景，不会做锁继承：

a. 锁类型为插入意向锁

b. srv_locks_unsafe_for_binlog打开且锁类型为X锁

c. 锁对应事务的隔离级别小于等于RC且锁类型为X锁

由于在执行类似REPLACE， LOAD DATAFILE REPLACE， INSERT ON DUPLICATE KEY UPDATE等操作时，当检查duplicate key时加的是X锁，本测试样例的隔离级别为RC，因此锁对象未被继承，而是

直接解除了，随后等待锁的线程(session2)被唤醒（lock_update_delete –> lock_rec_reset_and_release_wait）。

从IS表也可以看出来这一变化：

mysql> select * from information_schema.innodb_locks;

Empty set (0.00 sec)

此时innodb_locks里面已经没有记录了，purge操作“悄悄”的破坏了InnoDB的锁协议。

随后唤醒SESSION2继续操作。

4. SESSION 2: 由于获得了记录锁，因此可以继续插入记录

5. SESSION 1：由于已经完成了duplicate key检查，因此可以继续插入记录

mysql> select * from t1;

+---+---+

| a | b |

+---+---+

| 1 | 2 |

| 1 | 3 |

+---+---+

2 rows in set (0.00 sec)

mysql> check table t1;

+---------+-------+----------+-----------------------------------------------+

| Table   | Op    | Msg_type | Msg_text                                      |

+---------+-------+----------+-----------------------------------------------+

| test.t1 | check | Warning  | InnoDB: The B-tree of index "a" is corrupted. |

| test.t1 | check | error    | Corrupt                                       |

+---------+-------+----------+-----------------------------------------------+

2 rows in set (0.00 sec)

问题及解决

问题的原因Alexey其实在bug上已经解释的很清楚，Innodb认为只可能加S锁来维持一致性约束，因此当记录被物理删除时，只有S类型的锁才被继承。但对于REPLACE这样的操作，加的是X类型的锁，这种锁类型必须也要考虑进去，将其继承给下一条记录。Alexey已经将patch push到percona server，改动也就一行：

Ref:

bug链接   https://bugs.mysql.com/bug.php?id=76927

补丁地址： https://github.com/percona/percona-server/pull/83

相关文章