MySQL 行锁lock 放大现象Oracle DBA无法理解
MySQL事务隔离级别默认的Repeatable Reade 下有Gap Lock问题，在更低的隔离级别下，如读已提交（Read Committed）或读未提交（Read Uncommitted），Gap Lock不会被使用，在READ-COMMITTED下同样存在类似“Gap Lock”的问题，如果Oracle 数据库向基于MySQL innodb的数据库迁移后，不知是否会出现原有应用经常行锁等待的现象，等待innodb_lock_wait_timeout参数配置的时间后报错, 而oracle lock是在行头(row header)上，MySQL这种导致锁定记录增加的现象，让从事oracle DBA转型的无法理解，下面记录几个现象。
MySQL的Gap Lock
MySQL的Gap Lock是一种行级锁,在可重复读（Repeatable Read）隔离级别下生效，用于防止幻读（phantom reads）并确保事务的隔离级别达到可重复读（Repeatable Read）。Gap Lock主要在InnoDB存储引擎中使用，它的作用是锁定索引记录之间的“间隙”，而不仅仅是锁定实际的索引记录。
- Gap Lock的目的：
- 防止幻读：在一个事务中，防止其他事务在其读取的数据之间插入新的行。
- 保证查询结果的稳定性：确保一个事务在查询某个范围内的记录时，其他事务不能在这个范围内插入新的记录，从而保持结果的一致性。
- Gap Lock的范围：
- Gap Lock锁定的是索引记录之间的间隙，而不是具体的记录。它在索引记录之间、索引记录之前、索引记录之后创建“间隙锁”。
- Gap Lock会和Next-key Lock一起使用，其中Next-key Lock是一种组合锁，包含了行锁和Gap Lock，锁定一个记录以及它之前的间隙。
- Gap Lock的影响：
- 因为Gap Lock锁定的是索引记录之间的间隙，所以多个事务在试图插入或更新相邻记录时可能会导致死锁。应谨慎设计应用程序逻辑，以减少死锁的风险。
- 由于Gap Lock锁定的是间隙而不是具体的行，可能会对并发性和性能产生影响。需要根据应用程序的需求和负载情况，权衡使用Gap Lock带来的好处和可能的性能损失。
Repeatable Read
demo 1
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.36 | +-----------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS T1; Query OK, 0 rows affected (10.02 sec) mysql> CREATE TABLE `t1` ( -> `id` int NOT NULL, -> `update_time` datetime DEFAULT CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1(id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(10); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from t1; +----+---------------------+ | id | update_time | +----+---------------------+ | 1 | 2024-06-11 19:08:01 | | 2 | 2024-06-11 19:08:01 | | 3 | 2024-06-11 19:08:01 | | 4 | 2024-06-11 19:08:01 | | 5 | 2024-06-11 19:08:01 | | 6 | 2024-06-11 19:08:01 | | 7 | 2024-06-11 19:08:01 | | 8 | 2024-06-11 19:08:01 | | 10 | 2024-06-11 19:08:01 | +----+---------------------+ 9 rows in set (0.00 sec)
| SESSION 1 | SESSION 2 |
| mysql> set session transaction_isolation=’repeatable-read’;
mysql> begin; |
mysql> set session transaction_isolation=’repeatable-read’;
mysql> begin; |
| mysql> select * from t1 where id>8 for update; +—-+———————+ | id | update_time | +—-+———————+ | 10 | 2024-06-11 19:08:01 | +—-+———————+ |
|
| mysql> insert into t1 values(9,now()); — WAITING ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
session2 被堵塞，查询等待
mysql> SELECT THREAD_ID,ENGINE,LOCK_TYPE,INDEX_NAME,OBJECT_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks; +-----------+--------+-----------+------------+-------------+------------------------+-------------+------------------------+ | THREAD_ID | ENGINE | LOCK_TYPE | INDEX_NAME | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+--------+-----------+------------+-------------+------------------------+-------------+------------------------+ | 51 | INNODB | TABLE | NULL | t1 | IX | GRANTED | NULL | | 51 | INNODB | RECORD | PRIMARY | t1 | X,GAP,INSERT_INTENTION | WAITING | 10 | | 50 | INNODB | TABLE | NULL | t1 | IX | GRANTED | NULL | | 50 | INNODB | RECORD | PRIMARY | t1 | X,REC_NOT_GAP | GRANTED | 8 | | 50 | INNODB | RECORD | PRIMARY | t1 | X | GRANTED | supremum pseudo-record | | 50 | INNODB | RECORD | PRIMARY | t1 | X | GRANTED | 10 | | 51 | INNODB | RECORD | PRIMARY | t1 | X,GAP | GRANTED | 10 | +-----------+--------+-----------+------------+-------------+------------------------+-------------+------------------------+ 7 rows in set (0.00 sec)
demo 2
mysql> CREATE TABLE `t` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `a` int NOT NULL,
-> `b` int DEFAULT NULL,
-> `c` int DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `b` (`b`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t(a,b,c) VALUES (1,2,3),(2,2,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| t | 1 | b | 1 | b | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
mysql> select * from t;
+----+---+------+------+
| id | a | b | c |
+----+---+------+------+
| 1 | 1 | 2 | 3 |
| 2 | 2 | 2 | 4 |
+----+---+------+------+
2 rows in set (0.00 sec)
mysql> explain format=tree select * from t where b=2 and c=3;
+----------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------+
| -> Filter: (t.c = 3) (cost=0.6 rows=1)
-> Index lookup on t using b (b=2) (cost=0.6 rows=2)
|
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
| SESSION 1 | SESSION 2 |
| mysql> set session transaction_isolation=’repeatable-read’;
mysql> begin; |
mysql> set session transaction_isolation=’repeatable-read’;
mysql> begin; |
| mysql> select * from t where b=2 and c=3 for update; +—-+—+——+——+ | id | a | b | c | +—-+—+——+——+ | 1 | 1 | 2 | 3 | +—-+—+——+——+ 1 row in set (0.00 sec) |
|
| mysql> select * from t where b=2 and c=4 for update; — waiting ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
查询等待
mysql> SELECT THREAD_ID,ENGINE,LOCK_TYPE,INDEX_NAME,OBJECT_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
+-----------+--------+-----------+------------+-------------+---------------+-------------+------------------------+
| THREAD_ID | ENGINE | LOCK_TYPE | INDEX_NAME | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+--------+-----------+------------+-------------+---------------+-------------+------------------------+
| 51 | INNODB | TABLE | NULL | t | IX | GRANTED | NULL |
| 51 | INNODB | RECORD | b | t | X | WAITING | 2, 1 |
| 50 | INNODB | TABLE | NULL | t | IX | GRANTED | NULL |
| 50 | INNODB | RECORD | b | t | X | GRANTED | supremum pseudo-record |
| 50 | INNODB | RECORD | b | t | X | GRANTED | 2, 1 |
| 50 | INNODB | RECORD | b | t | X | GRANTED | 2, 2 |
| 50 | INNODB | RECORD | PRIMARY | t | X,REC_NOT_GAP | GRANTED | 1 |
| 50 | INNODB | RECORD | PRIMARY | t | X,REC_NOT_GAP | GRANTED | 2 |
+-----------+--------+-----------+------------+-------------+---------------+-------------+------------------------+
什么是supremum pseudo-record
在MySQL的InnoDB存储引擎中，supremum pseudo-record（简称supremum伪记录）是一个特殊的记录，它在每个B+树索引的叶节点的末尾，用来表示一个范围的上限。Supremum伪记录有助于实现间隙锁（gap lock）和Next-key锁机制，从而确保事务的隔离性和一致性。Supremum伪记录是InnoDB内部使用的记录，用户无法通过普通的SQL查询直接看到它们。它们仅存在于内部数据结构中，用于管理和维护索引范围。
辅助Gap Lock和Next-key Lock：
- Gap Lock需要锁定索引记录之间的间隙，包括在叶节点末尾的“开区间”。Supremum伪记录为这些操作提供了明确的终点，确保锁定范围正确。
- Next-key Lock锁定索引记录和它之前的间隙，supremum伪记录帮助定义最后一个记录之后的间隙。
READ-COMMITTED
模拟上面相同的事务顺序
demo1
| SESSION 1 | SESSION 2 |
| mysql> set transaction_isolation=’READ-COMMITTED’; mysql> begin; |
mysql> set transaction_isolation=’READ-COMMITTED’; mysql> begin; |
| mysql> select * from t1 where id>8 for update; +—-+———————+ | id | update_time | +—-+———————+ | 10 | 2024-06-11 19:08:01 | +—-+———————+ 1 row in set (0.00 sec) |
|
| mysql> insert into t1 values(9,now()); Query OK, 1 row affected (0.00 sec) |
Note:
注意session 2并没有被堵塞。
mysql> SELECT THREAD_ID,ENGINE,LOCK_TYPE,INDEX_NAME,OBJECT_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
| THREAD_ID | ENGINE | LOCK_TYPE | INDEX_NAME | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
| 51 | INNODB | TABLE | NULL | t1 | IX | GRANTED | NULL |
| 50 | INNODB | TABLE | NULL | t1 | IX | GRANTED | NULL |
| 50 | INNODB | RECORD | PRIMARY | t1 | X,REC_NOT_GAP | GRANTED | 10 |
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
demo2
| SESSION 1 | SESSION 2 |
| mysql> set transaction_isolation=’READ-COMMITTED’; Query OK, 0 rows affected (0.00 sec)mysql> begin; Query OK, 0 rows affected (0.00 sec) |
mysql> set transaction_isolation=’READ-COMMITTED’; Query OK, 0 rows affected (0.00 sec)mysql> begin; Query OK, 0 rows affected (0.00 sec) |
| mysql> select * from t where b=2 and c=3 for update; +—-+—+——+——+ | id | a | b | c | +—-+—+——+——+ | 1 | 1 | 2 | 3 | +—-+—+——+——+ 1 row in set (0.00 sec) |
|
| mysql> select * from t where b=2 and c=4 for update; — waiting ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
|
Note:
即使是在RC模式下，修改不同的行，
查看锁等待
mysql> SELECT THREAD_ID,ENGINE,LOCK_TYPE,INDEX_NAME,OBJECT_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
| THREAD_ID | ENGINE | LOCK_TYPE | INDEX_NAME | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
| 51 | INNODB | TABLE | NULL | t | IX | GRANTED | NULL |
| 51 | INNODB | RECORD | b | t | X,REC_NOT_GAP | WAITING | 2, 1 |
| 50 | INNODB | TABLE | NULL | t | IX | GRANTED | NULL |
| 50 | INNODB | RECORD | b | t | X,REC_NOT_GAP | GRANTED | 2, 1 |
| 50 | INNODB | RECORD | PRIMARY | t | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+--------+-----------+------------+-------------+---------------+-------------+-----------+
mysql> SHOW ENGINE INNODB STATUS\G
...
---TRANSACTION 1432, ACTIVE 302 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 11, OS thread handle 34224, query id 196 localhost ::1 root executing
select * from t where b=2 and c=4 for update
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 5 n bits 72 index b of table `anbob`.`t` trx id 1432 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000001; asc ;;
Note:
两个会话更新的虽然是不同的行记录，但是都 是基于二级索引B更b=2的值增加了x锁，互斥，所以session 2要等待，这点与oracle极大不同。MySQL dev反馈是READ COMMITTED使用与其他隔离级别相同的锁。唯一的区别在于并发事务……所做更改的可见性除了SERIALIZABLE之外，锁行和锁间隙在所有隔离级别中都是完全相同的。
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.
SEMI-CONSISTENT概念是READ COMMITTED与CONSISTENT READ两者的结合。一个UPDATE语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足UPDATE的WHERE条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)（听上去有点像oracle的更新重启动）。InnoDB在REPEATABLE READ中不使用半一致性读取。
小结
MySQL在RR模式下的gap lock问题会导致锁范围的放大，并且无论是在RR还是RC模式下，二级索引列的相同值where条件update或for update时，也会产生相互堵塞，注意差异点，当从其它数据库向MySQL Innodb迁移改造时，注意设计应用基于PK更新减少锁争用。
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)