本文主要介绍InnoDB存储引擎的Next-Key Lock

MVCC

  1. InnoDB支持MVCC，与之MVCC相对的是LBCC
  2. MVCC中读操作分两类：Snapshot Read(不加锁)和Current Read（加锁）
  3. MVCC的好处：**Snapshot Read不加锁**，并发性能好，适用于常规的JavaWeb项目（OLTP应用）

隔离级别

InnoDB支持4种事务隔离级别（Isolation Level）

隔离级别 描述
READ UNCOMMITTED (RUC) 可以读取到其他事务中尚未提交的内容，生产环境中不会使用
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，读写冲突，并发性能差

行锁

  1. InnoDB实现了两种标准的行锁（Row-Level Lock）：共享锁（Shared(S) Lock）、排它锁（Exclusive(X) Lock）
  2. S Lock：允许事务持有该锁去*读取一行数据*
  3. X Lock：允许事务持有该锁去*更新或删除一行数据*

S Lock与X Lock的兼容性

S X
S Y N
X N N

锁的算法

Record Lock

  1. Record Lock即行锁，用于锁住Index Record（索引记录），分为S Lock和X Lock
  2. 如果表中没有显式定义的主键或非NULL唯一的索引，InnoDB将自动创建6 Bytes的ROWID的隐藏主键

Gap Lock

  1. 用于锁住Index Record之间的间隙
  2. 如果是通过唯一索引来搜索一行记录的时候，不需要使用Gap Lock，此时Next-Key降级为Record Lock
  3. Gap S-Lock与Gap X-Lock是兼容的
  4. Gap Lock只能_**阻止其他事务在该Gap中插入记录_，但无法阻止**其他事务获取同一个Gap上的Gap Lock
  5. 禁用Gap Lock的两种方式
    • 将事务隔离级别设置为READ COMMITTED
    • 将变量innodb_locks_unsafe_for_binlog（已弃用）设置为1

Next-Key Lock

  1. Next-Key Lock = Record Lock + Gap Lock
  2. 若索引a为10、11、13、20，可锁定的区间为(-∞, 10]、(10, 11]、(11, 13]、(13, 20]、(20, +∞)
    • 若执行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
    • 也可以分解为在a=13和a=20上有2个X Lock，在(11,13)和(13,20)上有2个Gap Lock
  3. 在InnoDB默认事务隔离级别REPEATABLE READ(RR)下，支持Next-Key Lock

11个实例

  1. 下面11个实例仅仅考虑RC与RR的事务隔离级别
  2. RR支持Next-Key Lock、Gap Lock和Record Lock，RC仅支持Record Lock

RC/RR+Clustered Index+Equal Match

  1. 事务隔离级别READ COMMITTED(RC)或REPEATABLE READ(RR)
  2. 存在显式定义主键
  3. WHERE等值匹配成功

注：RR支持Next-Key Lock，在通过唯一索引来搜索一行记录时，Next-Key Lock降级为Record Lock，此时与RC一致，下面实例仅以RC进行说明

表初始化

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1322763通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上30的X Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 成功插入a=25和a=35，说明在(20,30)和(30,40)上没有Gap Lock
  3. 事务1322764尝试通过SELECT...LOCK IN SHARE MODE获得a=30的S Lock，由于S lock与X Lock不兼容，且此时事务1322763持有对应的X Lock，所以事务1322764被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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
  1. lock_index为PRIMARY，说明锁住的是聚集索引a（Clustered Index）
  2. trx id 1322764 lock mode S locks rec but not gap表示事务1322764想要获得S Lock，不需要Gap Lock

示意图

RC+Clustered Index+Equal Not Match

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义主键
  3. WHERE等值匹配不成功

表初始化

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1322801尝试通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，并不加任何锁

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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)
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 成功插入a=34和a=36，说明在(30,40)上没有Gap Lock
  3. 成功插入a=35，说明在a=35上没有X Lock

RR+Clustered Index+Equal Not Match

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义主键
  3. WHERE等值匹配不成功

表初始化

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 将Session A的事务隔离级别设置为REPEATABLE-READ
  2. 事务1323280尝试通过SELECT...FOR UPDATE操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，在(30,40)上加上Gap Lock

Session B

1
2
3
4
5
6
7
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
  1. 将Session B的事务隔离级别设置为REPEATABLE-READ
  2. Session B的事务尝试插入a=35，但由于事务1323280已经持有了(30,40)上的Gap Lock，因此被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
  1. 在事务隔离级别为REPEATABLE READ时，尝试给不存在的值上锁，会产生Gap Lock
  2. 在事务1323280插入a=35成功，因为其他事务（1323281）暂不持有包含a=35的Gap Lock，因此无法阻塞事务1323280的插入操作
  3. 插入成功后，事务1323280持有a=35的X Lock

Session B

1
2
3
4
5
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

1
2
3
4
5
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
  1. 事务1323280持有(30,40)上的Gap Lock，另一个事务1323281持有(35,40)上的Gap Lock
  2. 插入a=33不被阻塞，插入成功后事务1323280持有a=33的X Lock
  3. 插入a=36被事务1323281持有(35,40)上的Gap Lock阻塞（详细信息见下节）

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义主键
  3. WHERE采用RANGE匹配

表初始化

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1323886将获得聚集索引a上20、30、40上的X Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 * FROM t WHERE a=30 FOR UPDATE; # Blocked
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 事务1323887成功插入a=25和a=35，表明(20,30)和(30,40)上不存在Gap Lock
  3. 因为事务1323886已经持有a=30的X Lock，因此事务1323887被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义主键
  3. WHERE采用RANGE匹配

表初始化

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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<25 FOR UPDATE;
+----+
| a  |
+----+
| 20 |
+----+
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 |
+---------+-----------+-----------------------+---------------------+
| 1332194 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
1 row in set (0.00 sec)
  1. 将Session A的事务隔离级别设置为REPEATABLE READ
  2. 事务1332194将获得聚集索引a上20的X Lock，并将对应地获得(10,20)上的Gap Lock
  3. 依据Next-Key Lock，事务1332194还将获得聚集索引a上30的X Lock以及(20,30)上的Gap Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 35;
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 29; # Blocked
  1. 将Session B的事务隔离级别设置为REPEATABLE READ
  2. 成功插入5、35、45、55，表明事务1332194并没有持有(-∞,10)、(30,40)、(40,50)和(50,+∞)上的Gap Lock
  3. 事务1332194已持有(20,30)上的Gap Lock，因此事务1332194插入29会被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1332195:486:3:4 | 1332195     | X,GAP     | RECORD    | `test`.`t` | PRIMARY    |        486 |         3 |        4 | 30        |
| 1332194:486:3:4 | 1332194     | X         | RECORD    | `test`.`t` | PRIMARY    |        486 |         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 |
+-------------------+-------------------+-----------------+------------------+
| 1332195           | 1332195:486:3:4   | 1332194         | 1332194:486:3:4  |
+-------------------+-------------------+-----------------+------------------+
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 |
+---------+-----------+-----------------------+---------------------+
| 1332195 | LOCK WAIT | 1332195:486:3:4       | REPEATABLE READ     |
| 1332194 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.00 sec)

Session B

1
2
3
4
mysql> INSERT INTO t SELECT 29; # Timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO t SELECT 11; # Blocked

事务1332195插入11会被阻塞，原因同插入29一致，不再赘述，详细信息见下节

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1332195:486:3:3 | 1332195     | X,GAP     | RECORD    | `test`.`t` | PRIMARY    |        486 |         3 |        3 | 20        |
| 1332194:486:3:3 | 1332194     | X         | RECORD    | `test`.`t` | PRIMARY    |        486 |         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 |
+-------------------+-------------------+-----------------+------------------+
| 1332195           | 1332195:486:3:3   | 1332194         | 1332194:486:3:3  |
+-------------------+-------------------+-----------------+------------------+
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 |
+---------+-----------+-----------------------+---------------------+
| 1332195 | LOCK WAIT | 1332195:486:3:3       | REPEATABLE READ     |
| 1332194 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.00 sec)

Session B

1
2
3
4
5
6
7
8
9
10
11
12
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=30 FOR UPDATE; # Blocked
  1. 事务1332194并不持有聚集索引a上10的X Lock，事务1332195可以顺利获取聚集索引a上10的X Lock
  2. 事务1332194持有聚集索引a上30的X Lock，事务1332195被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1332195:486:3:4 | 1332195     | X         | RECORD    | `test`.`t` | PRIMARY    |        486 |         3 |        4 | 30        |
| 1332194:486:3:4 | 1332194     | X         | RECORD    | `test`.`t` | PRIMARY    |        486 |         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 |
+-------------------+-------------------+-----------------+------------------+
| 1332195           | 1332195:486:3:4   | 1332194         | 1332194:486:3:4  |
+-------------------+-------------------+-----------------+------------------+
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 |
+---------+-----------+-----------------------+---------------------+
| 1332195 | LOCK WAIT | 1332195:486:3:4       | REPEATABLE READ     |
| 1332194 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.01 sec)

示意图

RC+Secondary Unique Index+Range

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在唯一辅助索引
  3. WHERE通过RANGE匹配

表初始化

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1324402将获得辅助唯一索引b上30、40的X Lock，并获得对应的聚集索引a上50、40上的X Lock

Session B

1
2
3
4
5
6
7
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
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 事务1324402已经持有辅助唯一索引b上30的X Lock，因此会被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义唯一辅助索引
  3. WHERE通过RANGE匹配

表初始化

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)
  1. 将Session A的事务隔离级别设置为REPEATABLE READ
  2. 事务1324512将获得唯一辅助索引b上60、70、80上的X Lock以及(50,60)、(60,70)、(70,80)上的Gap Lock，相应地也会获得聚集索引a上40、50、30的X Lock
  3. 依据Next-Key Lock，事务1324512将获得唯一辅助索引b上90上的X Lock以及(80,90)上的Gap Lock，相应地获得聚集索引a上10的X Lock
  4. 事务1324512不会在聚集索引a上进行Gap Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)
  1. 将Session B的事务隔离级别设置为REPEATABLE READ
  2. 唯一辅助索引b上50尚未被其他事务锁定，事务1324513可以顺利获得唯一辅助索引b上50的X Lock
  3. 事务1324512已持有唯一辅助索引b上90的X Lock，事务1324513被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
11
12
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)
  1. 聚集索引a上20尚未被其他事务锁定，事务1324513可以顺利获得聚集索引a上20的X Lock
  2. 事务1324512已持有聚集索引a上10的X Lock，事务1324513被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
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
  1. 唯一聚集索引b上(-∞,50)的尚未被其他事务锁定，因此事务1324513成功插入(5,45)
  2. 事务1324512持有唯一聚集索引b上(50,60)的Gap Lock，因此事务1324513插入(6,55)时会被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义非唯一辅助索引
  3. WHERE通过RANGE匹配

表初始化

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1324589持有辅助索引b上(b=20,a=80)、(b=20,a=110)、(b=30,a=70)、(b=30,a=100)的X Lock，并相应地持有聚集索引a上(a=80,b=20)、(a=110,b=20)、(a=70,b=30)、(a=100,b=30)的X Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 辅助索引b上(b=10,a=120)和(b=40,a=90)尚未被其他事务锁定，事务1324590能成功获取辅助索引b上(b=10,a=120)和(b=40,a=90)的X Lock
  3. 事务1324589持有辅助索引b上(b=30,a=70)的X Lock，因此事务1324590被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
  1. 聚集索引a上(a=120,b=10)和(a=90,b=40)尚未被其他事务锁定，事务1324590能成功获取聚集索引a上(a=120,b=10)和(a=90,b=40)的X Lock
  2. 事务1324589持有聚集索引a上(a=100,b=30)的X Lock，因此事务1324590被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义非唯一辅助索引
  3. WHERE通过RANGE匹配

表初始化

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
  1. 将Session A的事务隔离级别设置为REPEATABLE READ
  2. 事务1324567持有辅助索引b上的X Lock：(b=20,a=80)、(b=20,a=110)、(b=30,a=70)、(b=30,a=100)
  3. 事务1324567持有辅助索引b上的Gap Lock：(b=10,a=120)~(b=20,a=80)、(b=20,a=80)~(b=20,a=110)、(b=20,a=110)~(b=30,a=70)、(b=30,a=70)~(b=30,a=100)
  4. 事务1324567持有聚集索引a上的X Lock：(a=80,b=20)、(a=110,b=20)、(a=70,b=30)、(a=100,b=30)
  5. 依据Next-Key Lock， 事务1324567还持有辅助索引b上(b=40,a=90)的X Lock和(b=30,a=100)~(b=40,a=90)上的Gap Lock，并相应地持有聚集索引a上(a=90,b=40)的X Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
  1. 将Session B的事务隔离级别设置为REPEATABLE READ
  2. 辅助索引b上(b=10,a=120)尚未被其他事务锁定，事务1324568能成功获取辅助索引b上(b=10,a=120)的X Lock
  3. 事务1324567持有辅助索引b上(b=40,a=90)的X Lock，因此事务1324568被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
11
12
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
  1. 聚集索引a上(a=120,b=10)尚未被其他事务锁定，事务1324568能成功获取聚集索引a上(a=120,b=10)的X Lock
  2. 事务1324567持有聚集索引a上(a=90,b=40)的X Lock，因此事务1324568被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
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
  1. 辅助索引b上(b=40,a=90)~(b=50,a=60)不存在Gap Lock，事务1324568能成功插入(a=95,b=40)
  2. 事务1324567持有辅助索引b上(b=10,a=120)~(b=20,a=80)的Gap Lock，事务1324568插入(a=75,b=20)被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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上(b=20,a=110)~(b=30,a=70)的Gap Lock，事务1324568插入(a=115,b=20)被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

表初始化

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)
  1. 将Session A的事务隔离级别设置为READ COMMITTED
  2. 由于列b上无索引，只能通过聚集索引a进行全表扫描，事务1324624将持有聚集索引a上30、50的X Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
  1. 聚集索引a上的10、20、40并未被其他事务锁定，事务1324625能成功获取它们的X Lock
  2. 事务1324624持有聚集索引a上的30的X lock，事务1324625被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

表初始化

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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)
  1. 将Session A的事务隔离级别设置为REPEATABLE READ
  2. 由于列b上无索引，只能通过聚集索引a进行全表扫描，事务1324610将持有聚集索引a上10、20、30、40、50的X Lock，并持有聚集索引a上(-∞,10)、(10,20)、(20,30)、(30,40)、(40,50)、(50,+∞)上的Gap Lock

Session B

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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
  1. +∞即supremum pseudo-record，相关信息请参照「InnoDB备忘录 - 数据页格式」
  2. 事务1324610持有聚集索引a上(50,+∞)的Gap Lock，事务1324611插入(55,100)被阻塞（详细信息见下节）

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

示意图

参考资料

  1. MySQL技术内幕 - InnoDB存储引擎 V2
  2. MySQL 加锁处理分析
  3. InnoDB Locking
  4. The INFORMATION_SCHEMA INNODB_TRX Table
  5. The INFORMATION_SCHEMA INNODB_LOCKS Table
  6. The INFORMATION_SCHEMA INNODB_LOCK_WAITS Tabl