内容简介：InnoDB备忘录 - Next-Key Lock
本文主要介绍 InnoDB 存储引擎的 Next-Key Lock
MVCC
- InnoDB支持
MVCC，与之MVCC相对的是LBCC - MVCC中
读操作分两类：Snapshot Read(不加锁)和Current Read（加锁） - MVCC的好处：
Snapshot Read不加锁，并发性能好，适用于常规的JavaWeb项目（OLTP应用）
隔离级别
InnoDB支持4种事务隔离级别（ Isolation Level ）
| 隔离级别 | 描述 |
|---|---|
READ UNCOMMITTED |
可以读取到其他事务中 尚未提交 的内容，生产环境中不会使用 |
READ COMMITTED(RC) |
可以读取到其他事务中 已经提交 的内容， Current Read会加锁 ， 存在幻读现象 ， Oracle 和 SQL Server 的默认事务隔离级别为 RC |
REPEATABLE READ(RR) |
保证事务的 隔离性 ， Current Read会加锁 ，同时会加 Gap Lock ， 不存在幻读现象 ， InnoDB 的默认事务隔离级别为 RR |
SERIALIZABLE |
MVCC退化为 LBCC ，不区分 Snapshot Read 和 Current Read ， 读 操作加 S Lock ， 写 操作加 X Lock ，读写冲突，并发性能差 |
行锁
- InnoDB实现了两种标准的
行锁（Row-Level Lock）：共享锁（Shared(S) Lock）、排它锁（Exclusive(X) Lock） -
S Lock：允许事务持有该锁去读取一行数据 -
X Lock：允许事务持有该锁去更新或删除一行数据
S Lock 与 X Lock 的兼容性
| S | X | |
|---|---|---|
| S | Y | N |
| X | N | N |
锁的算法
Record Lock
-
Record Lock即行锁，用于锁住Index Record（索引记录），分为S Lock和X Lock - 如果表中没有
显式定义的主键或唯一非NULL索引，InnoDB将自动创建6Byte的ROWID隐藏列作为主键
Gap Lock
- 用于锁住
Index Record之间的间隙 - 如果是
通过唯一索引来搜索一行记录的时候，不需要使用Gap Lock，此时Next-Key降级为Record Lock -
Gap S-Lock与Gap X-Lock是兼容的 -
Gap Lock只能阻止其他事务在该Gap中插入记录，但无法阻止其他事务获取同一个Gap上的Gap Lock - 禁用
Gap Lock的两种方式- 将事务隔离级别设置为
READ COMMITTED - 将变量
innodb_locks_unsafe_for_binlog（已弃用）设置为1
- 将事务隔离级别设置为
Next-Key Lock
-
Next-Key Lock=Record Lock+Gap Lock - 若索引a为10、11、13、20，可锁定的区间为
(negative infinity, 10]、(10, 11]、(11, 13]、(13, 20]、(20, positive infinity)- 若执行
Select...Where a=13 For Update，将在a=13上有1个X Lock和在(11, 13)有1个Gap Lock -
a=13的下一个键为a=20，将在a=20有1个X Lock，在(13, 20)有1个Gap Lock - 因此，在
a=13上有1个X Lock，在(11, 20]上的有1个Gap Lock
- 若执行
- 在InnoDB默认事务隔离级别
REPEATABLE READ(RR)下，支持Next-Key Lock
11个实例
- 下面11个实例仅仅考虑
RC与RR的事务隔离级别 -
RR支持Next-Key Lock、Gap Lock和Record Lock，RC仅支持Record Lock
RC/RR+Clustered Index+Equal Match
- 事务隔离级别
READ COMMITTED(RC)或REPEATABLE READ(RR) - 存在
显式定义主键 -
WHERE等值匹配成功
表初始化
mysql> CREATE TABLE t ( a INT NOT NULL PRIMARY KEY ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES (10),(20),(30),(40),(50),(60),(70),(80); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_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 a=30 FOR UPDATE; +----+ | a | +----+ | 30 | +----+ 1 row in set (0.01 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1322763 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 事务
1322763通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上30的X Lock
Session B
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SELECT 25; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 35; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1322764 | RUNNING | NULL | READ COMMITTED | | 1322763 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 2 rows in set (0.01 sec) mysql> SELECT * FROM t WHERE a=30 LOCK IN SHARE MODE; # Blocked
- 将
Session B的事务隔离级别设置为READ COMMITTED - 成功插入
a=25和a=35，说明在(20,30)和(30,40)上没有Gap Lock - 事务
1322764尝试通过SELECT...LOCK IN SHARE MODE获得a=30的S Lock，由于S lock与X Lock不兼容，且此时事务1322763持有对应的X Lock，所以事务1322764被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1322764:389:3:4 | 1322764 | S | RECORD | `test`.`t` | PRIMARY | 389 | 3 | 4 | 30 | | 1322763:389:3:4 | 1322763 | X | RECORD | `test`.`t` | PRIMARY | 389 | 3 | 4 | 30 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1322764 | 1322764:389:3:4 | 1322763 | 1322763:389:3:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (1.18 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1322764 | LOCK WAIT | 1322764:389:3:4 | READ COMMITTED | | 1322763 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 2 rows in set (0.00 sec) mysql> SHOW ENGINE INNODB STATUS\G LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1322764, ACTIVE 74 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2 MySQL thread id 139, OS thread handle 140648641087232, query id 2146 localhost root statistics SELECT * FROM t WHERE a=30 LOCK IN SHARE MODE ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 389 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1322764 lock mode S locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000001e; asc ;; 1: len 6; hex 000000142f02; asc / ;; 2: len 7; hex dc000001af012a; asc *;; ---TRANSACTION 1322763, ACTIVE 153 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 138, OS thread handle 140648641488640, query id 2150 localhost root starting
-
lock_index为PRIMARY，说明锁住的是聚集索引a（Clustered Index） -
trx id 1322764 lock mode S locks rec but not gap表示事务1322764想要获得S Lock，不需要Gap Lock
示意图
RC+Clustered Index+Equal Not Match
- 事务隔离级别
READ COMMITTED(RC) - 存在
显式定义主键 -
WHERE等值匹配不成功
表初始化
mysql> CREATE TABLE t ( a INT NOT NULL PRIMARY KEY ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES (10),(20),(30),(40),(50),(60),(70),(80); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_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 a=35 FOR UPDATE; Empty set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1322801 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.01 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 事务
1322801尝试通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，并不加任何锁
Session B
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SELECT 34; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 36; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 35; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1322802 | RUNNING | NULL | READ COMMITTED | | 1322801 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 2 rows in set (0.00 sec)
- 将
Session B的事务隔离级别设置为READ COMMITTED - 成功插入
a=34和a=36，说明在(30,40)上没有Gap Lock - 成功插入
a=35，说明在a=35上没有X Lock
RR+Clustered Index+Equal Not Match
- 事务隔离级别
REPEATABLE READ(RR) - 存在
显式定义主键 -
WHERE等值匹配不成功
表初始化
mysql> CREATE TABLE t ( a INT NOT NULL PRIMARY KEY ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES (10),(20),(30),(40),(50),(60),(70),(80); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE a=35 FOR UPDATE; Empty set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1323280 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为REPEATABLE-READ - 事务
1323280尝试通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，在(30,40)上加上Gap Lock
Session B
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SELECT 35; # Blocked
- 将
Session B的事务隔离级别设置为REPEATABLE-READ -
Session B的事务尝试插入a=35，但由于事务1323280已经持有了(30,40)上的Gap Lock，因此被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1323281:391:3:5 | 1323281 | X,GAP | RECORD | `test`.`t` | PRIMARY | 391 | 3 | 5 | 40 | | 1323280:391:3:5 | 1323280 | X,GAP | RECORD | `test`.`t` | PRIMARY | 391 | 3 | 5 | 40 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1323281 | 1323281:391:3:5 | 1323280 | 1323280:391:3:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1323281 | LOCK WAIT | 1323281:391:3:5 | REPEATABLE READ | | 1323280 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 2 rows in set (0.00 sec) mysql> SHOW ENGINE INNODB STATUS\G LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1323281, ACTIVE 16 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 5, OS thread handle 140546164094720, query id 119 localhost root executing INSERT INTO t SELECT 35 ------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 391 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1323281 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000028; asc (;; 1: len 6; hex 000000142f41; asc /A;; 2: len 7; hex a7000001fd0137; asc 7;; ---TRANSACTION 1323280, ACTIVE 99 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 4, OS thread handle 140546164295424, query id 123 localhost root starting mysql> INSERT INTO t SELECT 35; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
- 在事务隔离级别为
REPEATABLE READ时，尝试给不存在的值上锁，会产生Gap Lock - 在事务
1323280插入a=35成功，因为其他事务（1323281）暂不持有包含a=35的Gap Lock，因此无法阻塞事务1323280的插入操作 - 插入成功后，事务
1323280持有a=35的X Lock
Session B
mysql> INSERT INTO t SELECT 35; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=37 FOR UPDATE; Empty set (0.00 sec)
事务 1323280 持有 (30,40) 的 Gap Lock ，但无法阻止事务 1323281 获得 (35,40) 上的 Gap Lock （事务 1323280 已获得 a=35 的 X Lock ）
Session A
mysql> INSERT INTO t SELECT 33; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 36; # Blocked
- 事务
1323280持有(30,40)上的Gap Lock，另一个事务1323281持有(35,40)上的Gap Lock - 插入
a=33不被阻塞，插入成功后事务1323280持有a=33的X Lock - 插入
a=36被事务1323281持有(35,40)上的Gap Lock阻塞（详细信息见下节）
Session B
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1323280:391:3:5 | 1323280 | X,GAP | RECORD | `test`.`t` | PRIMARY | 391 | 3 | 5 | 40 | | 1323281:391:3:5 | 1323281 | X,GAP | RECORD | `test`.`t` | PRIMARY | 391 | 3 | 5 | 40 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1323280 | 1323280:391:3:5 | 1323281 | 1323281:391:3:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1323281 | RUNNING | NULL | REPEATABLE READ | | 1323280 | LOCK WAIT | 1323280:391:3:5 | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 2 rows in set (0.00 sec) mysql> SHOW ENGINE INNODB STATUS\G; LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1323281, ACTIVE 305 sec 2 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 5, OS thread handle 140546164094720, query id 131 localhost root starting SHOW ENGINE INNODB STATUS ---TRANSACTION 1323280, ACTIVE 388 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 4, OS thread handle 140546164295424, query id 127 localhost root executing INSERT INTO t SELECT 36 ------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 391 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1323280 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000028; asc (;; 1: len 6; hex 000000142f41; asc /A;; 2: len 7; hex a7000001fd0137; asc 7;;
RC+Clustered Index+Range
- 事务隔离级别
READ COMMITTED(RC) - 存在
显式定义主键 - WHERE采用
RANGE匹配
表初始化
mysql> CREATE TABLE t ( a INT NOT NULL PRIMARY KEY ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t VALUES (10),(20),(30),(40),(50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_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 a>15 AND a<45 FOR UPDATE; +----+ | a | +----+ | 20 | | 30 | | 40 | +----+ 3 rows in set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1323886 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 事务
1323886将获得聚集索引a上20、30、40上的X Lock
Session B
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED'; mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SELECT 25; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 35; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t WHERE a=30 FOR UPDATE; # BLocked
- 将
Session B的事务隔离级别设置为READ COMMITTED - 事务
1323887成功插入a=25和a=35，表明(20,30)和(30,40)上不存在Gap Lock - 因为事务
1323886已经持有a=30的X Lock，因此事务1323887被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1323887:399:3:4 | 1323887 | X | RECORD | `test`.`t` | PRIMARY | 399 | 3 | 4 | 30 | | 1323886:399:3:4 | 1323886 | X | RECORD | `test`.`t` | PRIMARY | 399 | 3 | 4 | 30 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1323887 | 1323887:399:3:4 | 1323886 | 1323886:399:3:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.01 sec)
示意图
RR+Clustered Index+Range
- 事务隔离级别
REPEATABLE READ(RR) - 存在
显式定义主键 - WHERE采用
RANGE匹配
表初始化
mysql> CREATE TABLE t ( a INT NOT NULL PRIMARY KEY ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t VALUES (10),(20),(30),(40),(50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE a>15 AND a<35 FOR UPDATE; +----+ | a | +----+ | 20 | | 30 | +----+ 2 rows in set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324370 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为REPEATABLE READ - 事务
1324370将获得聚集索引a上20、30的X Lock，并将对应地获得(10,20)和(20,30)上的Gap Lock - 依据
Next-Key Lock，事务1324370还将获得聚集索引a上40的X Lock以及(30,40)上的Gap Lock
Session B
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SELECT 5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 45; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 55; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t SELECT 39; # Blocked
- 将
Session B的事务隔离级别设置为REPEATABLE READ - 成功插入
5、45、55，表明事务1324370并没有持有(negative infinity,10)、(40,50)和(50,positive infinity)上的Gap Lock - 事务
1324370已持有(30,40)上的Gap Lock，因此事务1324371插入39会被阻塞（详细信息见下节）
Session A
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324371 | 1324371:404:3:5 | 1324370 | 1324370:404:3:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324371:404:3:5 | 1324371 | X,GAP | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 5 | 40 | | 1324370:404:3:5 | 1324370 | X | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 5 | 40 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
Session B
mysql> INSERT INTO t SELECT 39; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t SELECT 11; # Blocked
事务 1324371 插入 11 会被阻塞，原因同插入39一致，不再赘述，详细信息见下节
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324371:404:3:3 | 1324371 | X,GAP | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 3 | 20 | | 1324370:404:3:3 | 1324370 | X | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 3 | 20 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324371 | 1324371:404:3:3 | 1324370 | 1324370:404:3:3 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> INSERT INTO t SELECT 11; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=10 FOR UPDATE; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=40 FOR UPDATE; # Blocked
- 事务
1324370并不持有聚集索引a上10的X Lock，事务1324371可以顺利获取聚集索引a上10的X Lock - 事务
1324370持有聚集索引a上40的X Lock，事务1324371被阻塞（详细信息见下节）
Session A
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324371 | 1324371:404:3:5 | 1324370 | 1324370:404:3:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.01 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324371:404:3:5 | 1324371 | X | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 5 | 40 | | 1324370:404:3:5 | 1324370 | X | RECORD | `test`.`t` | PRIMARY | 404 | 3 | 5 | 40 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
示意图
RC+Secondary Unique Index+Range
- 事务隔离级别
READ COMMITTED(RC) - 存在
唯一辅助索引 -
WHERE通过RANGE匹配
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a),
-> UNIQUE KEY (b)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (10,20),(20,50),(30,10),(40,40),(50,30);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_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>25 AND b<45 FOR UPDATE; +----+----+ | a | b | +----+----+ | 50 | 30 | | 40 | 40 | +----+----+ 2 rows in set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324402 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.01 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 事务
1324402将获得辅助唯一索引b上30、40的X Lock，并获得对应的聚集索引a上50、40上的X Lock
Session B
mysql> SET SESSION TX_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=30 FOR UPDATE; # Blocked
- 将
Session B的事务隔离级别设置为READ COMMITTED - 事务
1324402已经持有辅助唯一索引b上30的X Lock，因此会被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324403:405:4:6 | 1324403 | X | RECORD | `test`.`t` | b | 405 | 4 | 6 | 30 | | 1324402:405:4:6 | 1324402 | X | RECORD | `test`.`t` | b | 405 | 4 | 6 | 30 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.01 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324403 | 1324403:405:4:6 | 1324402 | 1324402:405:4:6 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE b=30 FOR UPDATE; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=50 FOR UPDATE; # Blocked
事务 1324402 已经持有 聚集索引b 上 50 的 X Lock ，因此会被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324403:405:3:6 | 1324403 | X | RECORD | `test`.`t` | PRIMARY | 405 | 3 | 6 | 50 | | 1324402:405:3:6 | 1324402 | X | RECORD | `test`.`t` | PRIMARY | 405 | 3 | 6 | 50 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324403 | 1324403:405:3:6 | 1324402 | 1324402:405:3:6 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
示意图
RR+Secondary Unique Index+Range
- 事务隔离级别
REPEATABLE READ(RR) - 存在显式定义
唯一辅助索引 -
WHERE通过RANGE匹配
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a),
-> UNIQUE KEY (b)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (10,90),(20,50),(30,80),(40,60),(50,70);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b>55 AND b<85 FOR UPDATE; +----+----+ | a | b | +----+----+ | 40 | 60 | | 50 | 70 | | 30 | 80 | +----+----+ 3 rows in set (0.00 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324512 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.01 sec)
- 将
Session A的事务隔离级别设置为REPEATABLE READ - 事务
1324512将获得唯一辅助索引b上60、70、80上的X Lock以及(50,60)、(60,70)、(70,80)上的Gap Lock，相应地也会获得聚集索引a上40、50、30上的X Lock - 依据
Next-Key Lock，事务1324512将获得唯一辅助索引b上90上的X Lock以及(80,90)上的Gap Lock - 事务
1324512不会在聚集索引a上进行Gap Lock
Session B
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b=50 FOR UPDATE; +----+----+ | a | b | +----+----+ | 20 | 50 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE b=90 FOR UPDATE; # Blocked(60/70/80 blocked too)
- 将
Session B的事务隔离级别设置为REPEATABLE READ -
唯一辅助索引b上50尚未被其他事务锁定，事务1324513可以顺利获得唯一辅助索引b上50的X Lock - 事务
1324512已持有唯一辅助索引b上90的X Lock，事务1324513被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324513:410:4:2 | 1324513 | X | RECORD | `test`.`t` | b | 410 | 4 | 2 | 90 | | 1324512:410:4:2 | 1324512 | X | RECORD | `test`.`t` | b | 410 | 4 | 2 | 90 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324513 | 1324513:410:4:2 | 1324512 | 1324512:410:4:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE b=90 FOR UPDATE; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=20 FOR UPDATE; +----+----+ | a | b | +----+----+ | 20 | 50 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=10 FOR UPDATE; # Blocked(40/50/30 blocked too)
-
聚集索引a上20尚未被其他事务锁定，事务1324513可以顺利获得聚集索引a上20的X Lock - 事务
1324512已持有聚集索引a上10的X Lock，事务1324513被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324513:410:3:2 | 1324513 | X | RECORD | `test`.`t` | PRIMARY | 410 | 3 | 2 | 10 | | 1324512:410:3:2 | 1324512 | X | RECORD | `test`.`t` | PRIMARY | 410 | 3 | 2 | 10 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324513 | 1324513:410:3:2 | 1324512 | 1324512:410:3:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE a=10 FOR UPDATE; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t VALUES (5,45); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t VALUES (6,55); # Blocked
-
唯一聚集索引b上(negative infinity,50)的尚未被其他事务锁定，因此事务1324513成功插入(5,45) - 事务
1324512持有唯一聚集索引b上(50,60)的Gap Lock，因此事务1324513插入(6,55)时会被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324513:410:4:5 | 1324513 | X,GAP | RECORD | `test`.`t` | b | 410 | 4 | 5 | 60 | | 1324512:410:4:5 | 1324512 | X | RECORD | `test`.`t` | b | 410 | 4 | 5 | 60 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324513 | 1324513:410:4:5 | 1324512 | 1324512:410:4:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
示意图
RC+Secondary Index+Range
- 事务隔离级别
READ COMMITTED(RC) - 存在显式定义
非唯一辅助索引 -
WHERE通过RANGE匹配
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a),
-> KEY (b)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (60,50),(70,30),(80,20),(90,40),(100,30),(110,20),(120,10);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED'; Query OK, 0 rows affected (0.01 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b>15 AND b<35 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 80 | 20 | | 110 | 20 | | 70 | 30 | | 100 | 30 | +-----+----+ 4 rows in set (1.97 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324589 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.01 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 事务
1324589持有辅助索引b上(20,80)、(20,110)、(30,70)、(30,100)的X Lock，并相应地持有聚集索引a上(80,20)、(110,20)、(70,30)、(100,30)的X Lock
Session B
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED'; Query OK, 0 rows affected (0.01 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b=10 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 120 | 10 | +-----+----+ 1 row in set (0.02 sec) mysql> SELECT * FROM t WHERE b=40 FOR UPDATE; +----+----+ | a | b | +----+----+ | 90 | 40 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE b=30 FOR UPDATE; # Blocked
- 将
Session B的事务隔离级别设置为READ COMMITTED -
辅助索引b上(10,120)和(40,90)尚未被其他事务锁定，事务1324590能成功获取辅助索引b上(10,120)和(40,90)的X Lock - 事务
1324589持有辅助索引b上(30,70)的X Lock，因此事务1324590被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324590 | 1324590:413:4:3 | 1324589 | 1324589:413:4:3 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.01 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324590:413:4:3 | 1324590 | X | RECORD | `test`.`t` | b | 413 | 4 | 3 | 30, 70 | | 1324589:413:4:3 | 1324589 | X | RECORD | `test`.`t` | b | 413 | 4 | 3 | 30, 70 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.01 sec)
Session B
mysql> SELECT * FROM t WHERE b=30 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=120 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 120 | 10 | +-----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=90 FOR UPDATE; +----+----+ | a | b | +----+----+ | 90 | 40 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=100 FOR UPDATE; # Blocked
-
聚集索引a上(120,10)和(90,40)尚未被其他事务锁定，事务1324590能成功获取聚集索引a上(120,10)和(90,40)的X Lock - 事务
1324589持有聚集索引a上(100,30)的X Lock，因此事务1324590被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324590 | 1324590:413:3:6 | 1324589 | 1324589:413:3:6 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324590:413:3:6 | 1324590 | X | RECORD | `test`.`t` | PRIMARY | 413 | 3 | 6 | 100 | | 1324589:413:3:6 | 1324589 | X | RECORD | `test`.`t` | PRIMARY | 413 | 3 | 6 | 100 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
示意图
RR+Secondary Index+Range
- 事务隔离级别
REPEATABLE READ(RR) - 存在显式定义
非唯一辅助索引 -
WHERE通过RANGE匹配
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a),
-> KEY (b)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (60,50),(70,30),(80,20),(90,40),(100,30),(110,20),(120,10);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.01 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b>15 AND b<35 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 80 | 20 | | 110 | 20 | | 70 | 30 | | 100 | 30 | +-----+----+ 4 rows in set (1.97 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324567 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为REPEATABLE READ - 事务
1324567持有辅助索引b上(20,80)、(20,110)、(30,70)、(30,100)的X Lock和(10,120)~(20,80)、(20,80)~(20,110)、(20,110)~(30,70)、(30,70)~(30,100)、(30,100)~(40,90)上的Gap Lock，并相应地持有聚集索引a上(80,20)、(110,20)、(70,30)、(100,30)的X Lock - 依据
Next-Key Lock， 事务1324567还持有辅助索引b上(40,90)的X Lock和(30,100)~(40,90)上的Gap Lock，并相应地持有聚集索引a上(90,40)的X Lock
Session B
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.01 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b=10 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 120 | 10 | +-----+----+ 1 row in set (0.02 sec) mysql> SELECT * FROM t WHERE b=40 FOR UPDATE; # Blocked
- 将
Session B的事务隔离级别设置为REPEATABLE READ -
辅助索引b上(10,120)尚未被其他事务锁定，事务1324568能成功获取辅助索引b上(10,120)的X Lock - 事务
1324567持有辅助索引b上(40,90)的X Lock，因此事务1324568被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324568:412:4:5 | 1324568 | X | RECORD | `test`.`t` | b | 412 | 4 | 5 | 40, 90 | | 1324567:412:4:5 | 1324567 | X | RECORD | `test`.`t` | b | 412 | 4 | 5 | 40, 90 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.03 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324568 | 1324568:412:4:5 | 1324567 | 1324567:412:4:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE b=40 FOR UPDATE; # Timout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=120 FOR UPDATE; +-----+----+ | a | b | +-----+----+ | 120 | 10 | +-----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=90 FOR UPDATE; # Blocked
-
聚集索引a上(120,10)尚未被其他事务锁定，事务1324568能成功获取聚集索引a上(120,10)的X Lock - 事务
1324567持有聚集索引a上(90,40)的X Lock，因此事务1324568被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324568 | 1324568:412:3:5 | 1324567 | 1324567:412:3:5 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.01 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324568:412:3:5 | 1324568 | X | RECORD | `test`.`t` | PRIMARY | 412 | 3 | 5 | 90 | | 1324567:412:3:5 | 1324567 | X | RECORD | `test`.`t` | PRIMARY | 412 | 3 | 5 | 90 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE a=90 FOR UPDATE; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t VALUES (95,40); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t VALUES (75,20); # Blocked
-
辅助索引b上(40,90)~(50,60)不存在Gap Lock，事务1324568能成功插入(95,40) - 事务
1324567持有辅助索引b上(10,120)~(20,80)的Gap Lock，事务1324568插入(75,20)被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324568:412:4:4 | 1324568 | X,GAP | RECORD | `test`.`t` | b | 412 | 4 | 4 | 20, 80 | | 1324567:412:4:4 | 1324567 | X | RECORD | `test`.`t` | b | 412 | 4 | 4 | 20, 80 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324568 | 1324568:412:4:4 | 1324567 | 1324567:412:4:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> INSERT INTO t VALUES (75,20); # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t VALUES (115,20); # Blocked
事务 1324567 持有 辅助索引b 上 (20,110)~(30,70) 的 Gap Lock ，事务 1324568 插入 (115,20) 被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324568:412:4:3 | 1324568 | X,GAP | RECORD | `test`.`t` | b | 412 | 4 | 3 | 30, 70 | | 1324567:412:4:3 | 1324567 | X | RECORD | `test`.`t` | b | 412 | 4 | 3 | 30, 70 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324568 | 1324568:412:4:3 | 1324567 | 1324567:412:4:3 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
示意图
在 RR 隔离级别下，类似 SELECT ... FOR UPDATE 这种 Current Read ，使用 Gap Lock 能保证过滤出来的范围不被其他事务插入新的记录，防止 幻读 的产生
RC+No Index
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (10,50),(20,60),(30,70),(40,80),(50,90);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_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=70 OR b=90 FOR UPDATE; +----+----+ | a | b | +----+----+ | 30 | 70 | | 50 | 90 | +----+----+ 2 rows in set (0.01 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324624 | RUNNING | NULL | READ COMMITTED | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为READ COMMITTED - 由于
列b上无索引，只能通过聚集索引a进行全表扫描，事务1324624将持有聚集索引a上30、50的X Lock
Session B
mysql> SET SESSION TX_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 a=10 FOR UPDATE; +----+----+ | a | b | +----+----+ | 10 | 50 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=20 FOR UPDATE; +----+----+ | a | b | +----+----+ | 20 | 60 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=40 FOR UPDATE; +----+----+ | a | b | +----+----+ | 40 | 80 | +----+----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t WHERE a=30 FOR UPDATE; # Blocked
-
聚集索引a上的10、20、40并未被其他事务锁定，事务1324625能成功获取它们的X Lock - 事务
1324624持有聚集索引a上的30的X lock，事务1324625被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324625 | 1324625:414:3:4 | 1324624 | 1324624:414:3:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324625:414:3:4 | 1324625 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 4 | 30 | | 1324624:414:3:4 | 1324624 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 4 | 30 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
Session B
mysql> SELECT * FROM t WHERE a=30 FOR UPDATE; # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=50 FOR UPDATE; # Blocked
事务 1324624 持有 聚集索引a 上的 50 的 X lock ，事务 1324625 被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324625:414:3:6 | 1324625 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 6 | 50 | | 1324624:414:3:6 | 1324624 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 6 | 50 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324625 | 1324625:414:3:6 | 1324624 | 1324624:414:3:6 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
示意图
RR+No Index
表初始化
mysql> CREATE TABLE t (
-> a INT NOT NULL,
-> b INT NOT NULL,
-> PRIMARY KEY (a)
-> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES (10,50),(20,60),(30,70),(40,80),(50,90);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
Session A
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE b=70 FOR UPDATE; +----+----+ | a | b | +----+----+ | 30 | 70 | +----+----+ 1 row in set (0.01 sec) mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX; +---------+-----------+-----------------------+---------------------+ | trx_id | trx_state | trx_requested_lock_id | trx_isolation_level | +---------+-----------+-----------------------+---------------------+ | 1324610 | RUNNING | NULL | REPEATABLE READ | +---------+-----------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- 将
Session A的事务隔离级别设置为REPEATABLE READ - 由于
列b上无索引，只能通过聚集索引a进行全表扫描，事务1324610将持有聚集索引a上10、20、30、40、50的X Lock，并持有聚集索引a上(negative infinity,10)、(10,20)、(20,30)、(30,40)、(40,50)、(50,positive infinity)上的Gap Lock
Session B
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t VALUES (5,100); # Blocked
事务 1324610 持有 聚集索引a 上 (negative infinity,10) 的 Gap Lock ，事务 1324611 插入 (5,100) 被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324611:414:3:2 | 1324611 | X,GAP | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 2 | 10 | | 1324610:414:3:2 | 1324610 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 2 | 10 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324611 | 1324611:414:3:2 | 1324610 | 1324610:414:3:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> INSERT INTO t VALUES (5,100); # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t VALUES (25,100); # Blocked
事务 1324610 持有 聚集索引a 上 (20,30) 的 Gap Lock ，事务 1324611 插入 (25,100) 被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324611 | 1324611:414:3:4 | 1324610 | 1324610:414:3:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324611:414:3:4 | 1324611 | X,GAP | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 4 | 30 | | 1324610:414:3:4 | 1324610 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 4 | 30 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.01 sec)
Session B
mysql> INSERT INTO t VALUES (25,100); # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO t VALUES (55,100); # Blocked
-
positive infinity即supremum pseudo-record，相关信息请参照「InnoDB备忘录 - 数据页格式」 - 事务
1324610持有聚集索引a上(50,positive infinity)的Gap Lock，事务1324611插入(55,100)被阻塞（详细信息见下节）
Session A
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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+------------------------+ | 1324611:414:3:1 | 1324611 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 1 | supremum pseudo-record | | 1324610:414:3:1 | 1324610 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 1 | supremum pseudo-record | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324611 | 1324611:414:3:1 | 1324610 | 1324610:414:3:1 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
Session B
mysql> INSERT INTO t VALUES (55,100); # Timeout ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM t WHERE a=50 FOR UPDATE; # Blocked
事务 1324610 持有 聚集索引a 上 50 的 X Lock ，事务 1324611 被阻塞（详细信息见下节）
Session A
mysql> select * from information_schema.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 1324611 | 1324611:414:3:6 | 1324610 | 1324610:414:3:6 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec) 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 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ | 1324611:414:3:6 | 1324611 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 6 | 50 | | 1324610:414:3:6 | 1324610 | X | RECORD | `test`.`t` | PRIMARY | 414 | 3 | 6 | 50 | +-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
以上所述就是小编给大家介绍的《InnoDB备忘录 - Next-Key Lock》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
大数据系统构建
Nathan Marz、James Warren / 马延辉、向磊、魏东琦 / 机械工业出版社 / 2017-1 / 79.00
随着社交网络、网络分析和智能型电子商务的兴起，传统的数据库系统显然已无法满足海量数据的管理需求。 作为一种新的处理模式，大数据系统应运而生，它使用多台机器并行工作，能够对海量数据进行存储、处理、分析，进而帮助用户从中提取对优化流程、实现高增长率的有用信息，做更为精准有效的决策。 但不可忽略的是，它也引入了大多数开发者并不熟悉的、困扰传统架构的复杂性问题。 本书将教你充分利用集群硬件优势的La......一起来看看 《大数据系统构建》 这本书的介绍吧!
