MySQL的RR和RC事务隔离级别加锁类型验证
先上结轮：MySQL5.7数据库Innodb引擎在默认的 REPEATABLE-READ(可重复读RR) 事务隔离级别时，事务修改类操作对于where范围条件锁定的行区加的是Next-Key Lock 即临键间隙锁，对于确切条件锁定的行加的是RECORD LOCKS 即普通行锁，并且无法读到自身事务范围之外其他事务的提交(而且自身事务锁定的范围无法被其他事务修改或插入新值)；在READ-COMMITTED(读已提交RC)事务隔离级别时，事务修改类操作对于where范围条件或确切条件锁定的行加的都是RECORD LOCKS 即普通行锁，并且能够读到自身事务锁定行范围之外其他事务的提交(但自身事务锁定的行无法被其他事务修改)；
-- 打开Innodb状态输出开关 set GLOBAL innodb_status_output=ON; -- 打开Innodb锁状态输出开关 set GLOBAL innodb_status_output_locks=ON; -- 查看当前数据库隔离级别 select @@transaction_isolation; -- 设置会话的事务隔离级别(RC的设为 'READ-COMMITTED' ) set @@session.transaction_isolation = 'REPEATABLE-READ'; COMMIT; -- 设置会话的隔离级别(RC的设为 COMMITTED READ) set session transaction isolation level REPEATABLE READ; COMMIT; -- 以后脚本手工一行一行执行(在另一个窗口，另个其他事务进行各种场景的验证，并到Linux上的MySQL客户端用 show engine innodb status\G 命令查看上锁状态) BEGIN; SELECT * from test_user WHERE id=1; UPDATE test_user set NAME='1234' WHERE id<4; SELECT * from test_user WHERE id=6; -- 最后执行回滚 ROLLBACK;
到Linux上的MySQL客户端用 show engine innodb status\G; 命令查看上锁状态！
RR事务隔离级别时的锁状态(临键间隙锁)：
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 2, OS thread handle 139641668019968, query id 257 192.168.17.1 xurm Trx read view will not see trx with id >= 6928, sees < 6928 # RR级别多输出的一行，表明了只会产生一次 read view，并确定了其可看到的事务id 范围！ TABLE LOCK table `test`.`test_user` trx id 6928 lock mode IX RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`test_user` trx id 6928 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
RC事务隔离级别时的锁状态(排他锁(X)行锁，但非间隙锁)：
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 2, OS thread handle 139641668019968, query id 630 192.168.17.1 xurm TABLE LOCK table `test`.`test_user` trx id 6941 lock mode IX RECORD LOCKS space id 26 page no 3 n bits 80 index PRIMARY of table `test`.`test_user` trx id 6941 lock_mode X locks rec but not gap Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
我们将事务里的更新条件由小于(<)改成大于(>)：
BEGIN; SELECT * from test_user WHERE id=1; -- 换成>4 UPDATE test_user set NAME='1234' WHERE id>4; SELECT * from test_user WHERE id=6;
RR事务隔离级别时的锁状态(显示有2把锁，但没有输出具体哪2把锁，其实是一把IX排他意向锁和一把(4,+∞)的间隙锁：
---TRANSACTION 7437, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 8 MySQL thread id 3, OS thread handle 139860111124224, query id 239 192.168.17.1 xurm -------- FILE I/O --------
我们将事务里的更新条件改成大于(>=)：
BEGIN; SELECT * from test_user WHERE id=1; -- 换成>4 UPDATE test_user set NAME='1234' WHERE id>=4; SELECT * from test_user WHERE id=6;
RR事务隔离级别时的锁状态(显示有3把锁，但没有输出具体哪3把锁，其实是一把IX排他意向锁、一把针对id=4的行的记录锁(普通行锁)和一把(4,+∞)的间隙锁：
---TRANSACTION 7448, ACTIVE 3 sec 3 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 9 MySQL thread id 3, OS thread handle 139860111124224, query id 361 192.168.17.1 xurm -------- FILE I/O --------
新开一个事务会话验证修改id=4的记录：
BEGIN; SELECT * from test_user WHERE id=4; -- 其他事务验证修改 UPDATE test_user set NAME='4321工12' WHERE id=4; SELECT * from test_user WHERE id=4; -- 最后执行回滚 ROLLBACK;
修改id=4的行时发生了锁等待，查看RR事务隔离级别时的锁状态(新的事务想在id=4的行上加一把 记录锁(普通行锁) 结果需要等待)：
---TRANSACTION 7451, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 139860108728064, query id 394 192.168.17.1 xurm updating UPDATE test_user set NAME='4321工12' WHERE id=4 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 3 n bits 128 index PRIMARY of table `test`.`test_user` trx id 7451 lock_mode X locks rec but not gap waiting Record lock, heap no 62 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000004; asc ;; 1: len 6; hex 000000001d1a; asc ;; 2: len 7; hex 3100000143049c; asc 1 C ;; 3: len 4; hex 31323334; asc 1234;; 4: len 4; hex 8000001f; asc ;; 5: len 7; hex e6b58be8af9531; asc 1;; ------------------ ---TRANSACTION 7450, ACTIVE 12 sec 3 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 9 MySQL thread id 3, OS thread handle 139860111124224, query id 390 192.168.17.1 xurm -------- FILE I/O --------
新开一个事务会话验证在id>=4的后续间隙插入新的记录：
BEGIN; SELECT * from test_user WHERE id=4; -- 其他事务验证插入 INSERT INTO `test`.`test_user` (`name`, `age`, `remark`) VALUES ('Rainy123555', 31, '测试1155'); -- 最后执行回滚 ROLLBACK;
尝试在id>=4的后续间隙插入新的记录行时发生了锁等待，查看RR事务隔离级别时的锁状态(新的事务想在id>=4的后续间隙加一把 插入意向锁 结果需要等待，注意【插入意向锁】跟加在表上的【意向锁】是两类不同的锁)：
---TRANSACTION 7454, ACTIVE 4 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 8, OS thread handle 139860108728064, query id 442 192.168.17.1 xurm update INSERT INTO `test`.`test_user` (`name`, `age`, `remark`) VALUES ('Rainy123555', 31, '测试1155') ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 3 n bits 136 index PRIMARY of table `test`.`test_user` trx id 7454 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION 7453, ACTIVE 21 sec 3 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 9 MySQL thread id 3, OS thread handle 139860111124224, query id 437 192.168.17.1 xurm -------- FILE I/O --------
最后，再配合两种隔离级别下事务范围内的 read view (读视图)生成机制：RR事务隔离级别只在第一次MVCC读(即快照读)时生成一次事务的read view，即一次性确定read view的可见事务范围，因此可以做到可重复读；而RC事务隔离级别会在每一次MVCC读时都重新生成一次事务的read view，因此能够读到其他事务的已提交数据。
