在MySQL的RC(READ-COMMITTED)隔离级别下,是否存在类似于Gap Lock的锁定行为?是否会发生由于这种锁定行为导致的堵塞现象?带着这些疑问,下面通过示例来进一步了解Gap锁的机制以及RC隔离级别下类Gap锁的现象。
1.Gap锁介绍
MySQL的Gap Lock是InnoDB存储引擎的一种锁定机制,它主要在事务隔离级别设置为可重复读(REPEATABLE READ)时使用。Gap Lock的主要功能是锁定记录间的间隙,从而防止其他事务在这些间隙中插入新的记录,这样做可以有效地防止幻读的发生。下面是在可重复读隔离级别下使用Gap Lock的示例,这个示例可以更好地帮助我们理解Gap锁的工作机制。
#模拟数据:
mysql> DROP TABLE IF EXISTS T1;
mysql> CREATE TABLE `t1` (
`id` int NOT NULL,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
#没有插入9的数据:
mysql> INSERT INTO t1(id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(10);
在REPEATABLE-READ隔离级别下:
在这个例子中,FOR UPDATE子句告诉MySQL需要锁定id>2查询结果集中的所有记录,并且要使用Gap Lock锁定结果集外的间隙。通过INNODB STATUS信息,也可以看到触发的gap锁“lock_mode X locks gap ”。
mysql> SHOW ENGINE INNODB STATUS\G
INSERT INTO t1(id) VALUES (9)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 653 page no 4 n bits 80 index PRIMARY of table `demo`.`t1` trx id 388429 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000005ed45; asc E;;
2: len 7; hex 82000000d9015e; asc ^;;
3: len 5; hex 99b36b245e; asc k$^;;
2.RC隔离级别下UPDATE操作类Gap锁
1)二级索引,类Gap锁
在官方Gap锁介绍中,存在一段这样的说明:
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
大致意识是,在使用READ-COMMITTED隔离级别还有其他影响。MySQL评估WHERE条件后,将释放不匹配行的记录锁。对于UPDATE语句,InnoDB进行“semi-consistent”半一致读取,从而将最新提交的版本返回给MySQL,以便MySQL可以确定该行是否符合UPDATE的WHERE条件。
类Gap锁导致堵塞示例如下:
#表结构:
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;
#模拟数据
mysql> INSERT INTO t(a,b,c) VALUES (1,2,3),(2,2,4);
如果WHERE条件包括二级索引列,并且InnoDB使用二级索引,则在获取和保留记录锁时只考虑索引列。如下模拟堵塞场景示例。第一个UPDATE在b=2的每一行上获取并保留一个x锁。第二个UPDATE在尝试获取相同记录上的x锁时会阻塞,因为它还使用了在列b上定义的索引。如果二级索引上匹配的行很多,那锁住的范围就是很大范围了。
查看InnoDB状态,是索引的X锁。
mysql> SHOW ENGINE INNODB STATUS\G
UPDATE t SET b = 4 WHERE b = 2 AND c = 4
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 656 page no 5 n bits 72 index b of table `demo`.`t` trx id 388604 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
备注:SEMI-CONSISTENT概念是READ COMMITTED与CONSISTENT READ两者的结合。一个UPDATE语句,如果读到一行已经加锁的记录,此时InnoDB返回记录最近提交的版本,由MySQL上层判断此版本是否满足UPDATE的WHERE条件。若满足(需要更新),则MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。InnoDB在REPEATABLE READ中不使用半一致性读取。
2)主键,类Gap锁
使用上述T1表,在RC隔离级别下UPDATE操作如下:
在UPDATE语句也会出现类Gap锁。
mysql> SHOW ENGINE INNODB STATUS\G
SELECT * FROM t1 WHERE ID >= 7 AND ID INSERT INTO T1(id) values(9);
在RC隔离级别下UPDATE操作:无堵塞
这个示例,按照逻辑有明显不合理设计。
总结
在MySQL中,进行数据更新操作时,如果可能的话,应尽量基于主键进行,并尽量保持SQL语句的简洁性。对于复杂的操作,建议放在业务端进行处理。为了尽量避免堵塞现象,可以通过设置innodb_lock_wait_timeout参数来进行控制。
参考:
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
https://bugs.mysql.com/bug.php?id=115078