InnoDB -- Next-Key Lock

本文转载自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会加锁存在幻读现象OracleSQL Server的默认事务隔离级别为RC
REPEATABLE READ (RR) 保证事务的隔离性Current Read会加锁,同时会加Gap Lock不存在幻读现象InnoDB的默认事务隔离级别为RR
SERIALIZABLE MVCC退化为LBCC,不区分Snapshot ReadCurrent Read操作加S Lock操作加X Lock,读写冲突,并发性能差

行锁

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

S LockX Lock的兼容性

S X
S Y N
X N N

锁的算法

Record Lock

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

Gap Lock

  1. 用于锁住Index Record之间的间隙

  2. 如果是通过唯一索引来搜索一行记录的时候,不需要使用Gap Lock,此时Next-Key降级为Record Lock

  3. Gap S-LockGap 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=13a=20上有2个X Lock,在(11,13)(13,20)上有2个Gap Lock
  3. 在InnoDB默认事务隔离级别REPEATABLE READ(RR)下,支持Next-Key Lock

11个实例

  1. 下面11个实例仅仅考虑RCRR的事务隔离级别
  2. RR支持Next-Key LockGap LockRecord LockRC仅支持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进行说明

表初始化

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)
  1. Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1322763通过SELECT...FOR UPDATE操作获得了聚集索引aClustered Index)上30X 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
  1. Session B的事务隔离级别设置为READ COMMITTED
  2. 成功插入a=25a=35,说明在(20,30)(30,40)上没有Gap Lock
  3. 事务1322764尝试通过SELECT...LOCK IN SHARE MODE获得a=30S Lock,由于S lockX 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
  1. lock_indexPRIMARY,说明锁住的是聚集索引aClustered Index
  2. trx id 1322764 lock mode S locks rec but not gap表示事务1322764想要获得S Lock,不需要Gap Lock

示意图

InnoDB -- Next-Key Lock

RC+Clustered Index+Equal Not Match

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义主键
  3. 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)
  1. Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1322801尝试通过SELECT...FOR UPDATE操作获得了聚集索引aClustered Index)上35X 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)
  1. Session B的事务隔离级别设置为READ COMMITTED
  2. 成功插入a=34a=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等值匹配不成功

表初始化

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)
  1. Session A的事务隔离级别设置为REPEATABLE-READ
  2. 事务1323280尝试通过SELECT...FOR UPDATE操作获得了聚集索引aClustered Index)上35X 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
  1. Session B的事务隔离级别设置为REPEATABLE-READ
  2. Session B的事务尝试插入a=35,但由于事务1323280已经持有了(30,40)上的Gap Lock,因此被阻塞(详细信息见下节)

InnoDB -- Next-Key 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
  1. 在事务隔离级别为REPEATABLE READ时,尝试给不存在的值上锁,会产生Gap Lock
  2. 在事务1323280插入a=35成功,因为其他事务(1323281)暂不持有包含a=35Gap Lock,因此无法阻塞事务1323280的插入操作
  3. 插入成功后,事务1323280持有a=35X Lock

InnoDB -- Next-Key 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=35X Lock

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

InnoDB -- Next-Key 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

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义主键
  3. 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)
  1. Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1323886将获得聚集索引a203040上的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 * FROM t WHERE a=30 FOR UPDATE; # Blocked
  1. Session B的事务隔离级别设置为READ COMMITTED
  2. 事务1323887成功插入a=25a=35,表明(20,30)(30,40)上不存在Gap Lock
  3. 因为事务1323886已经持有a=30X 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)

示意图

InnoDB -- Next-Key Lock

RR+Clustered Index+Range

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义主键
  3. 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<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将获得聚集索引a20X Lock,并将对应地获得(10,20)上的Gap Lock
  3. 依据Next-Key Lock,事务1332194还将获得聚集索引a30X Lock以及(20,30)上的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 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. 成功插入5354555,表明事务1332194并没有持有(-∞,10)(30,40)(40,50)(50,+∞)上的Gap Lock
  3. 事务1332194已持有(20,30)上的Gap Lock,因此事务1332194插入29会被阻塞(详细信息见下节)

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 |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 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

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

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

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并不持有聚集索引a10X Lock,事务1332195可以顺利获取聚集索引a10X Lock
  2. 事务1332194持有聚集索引a30X Lock,事务1332195被阻塞(详细信息见下节)

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 |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 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)

示意图

InnoDB -- Next-Key Lock

RC+Secondary Unique Index+Range

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在唯一辅助索引
  3. 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)
  1. Session A的事务隔离级别设置为READ COMMITTED
  2. 事务1324402将获得辅助唯一索引b3040X Lock,并获得对应的聚集索引a5040上的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
  1. Session B的事务隔离级别设置为READ COMMITTED
  2. 事务1324402已经持有辅助唯一索引b30X 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已经持有聚集索引b50X 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)

示意图

InnoDB -- Next-Key Lock

RR+Secondary Unique Index+Range

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义唯一辅助索引
  3. 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)
  1. Session A的事务隔离级别设置为REPEATABLE READ
  2. 事务1324512将获得唯一辅助索引b607080上的X Lock以及(50,60)(60,70)(70,80)上的Gap Lock,相应地也会获得聚集索引a405030X Lock
  3. 依据Next-Key Lock,事务1324512将获得唯一辅助索引b90上的X Lock以及(80,90)上的Gap Lock,相应地获得聚集索引a10X Lock
  4. 事务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)
  1. Session B的事务隔离级别设置为REPEATABLE READ
  2. 唯一辅助索引b50尚未被其他事务锁定,事务1324513可以顺利获得唯一辅助索引b50X Lock
  3. 事务1324512已持有唯一辅助索引b90X 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)
  1. 聚集索引a20尚未被其他事务锁定,事务1324513可以顺利获得聚集索引a20X Lock
  2. 事务1324512已持有聚集索引a10X 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
  1. 唯一聚集索引b(-∞,50)的尚未被其他事务锁定,因此事务1324513成功插入(5,45)
  2. 事务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)

示意图

InnoDB -- Next-Key Lock

RC+Secondary Index+Range

  1. 事务隔离级别READ COMMITTED(RC)
  2. 存在显式定义非唯一辅助索引
  3. 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)
  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

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

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
  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

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)

示意图

InnoDB -- Next-Key Lock

RR+Secondary Index+Range

  1. 事务隔离级别REPEATABLE READ(RR)
  2. 存在显式定义非唯一辅助索引
  3. 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)
  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

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

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
  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

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
  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

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(b=20,a=110)~(b=30,a=70)Gap Lock,事务1324568插入(a=115,b=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)

示意图

InnoDB -- Next-Key Lock

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)
  1. Session A的事务隔离级别设置为READ COMMITTED
  2. 由于列b上无索引,只能通过聚集索引a进行全表扫描,事务1324624将持有聚集索引a3050X 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
  1. 聚集索引a上的102040并未被其他事务锁定,事务1324625能成功获取它们的X Lock
  2. 事务1324624持有聚集索引a上的30X 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上的50X 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)

示意图

InnoDB -- Next-Key Lock

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)
  1. Session A的事务隔离级别设置为REPEATABLE READ
  2. 由于列b上无索引,只能通过聚集索引a进行全表扫描,事务1324610将持有聚集索引a1020304050X Lock,并持有聚集索引a(-∞,10)(10,20)(20,30)(30,40)(40,50)(50,+∞)上的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
  1. +∞supremum pseudo-record,相关信息请参照「InnoDB备忘录 - 数据页格式」
  2. 事务1324610持有聚集索引a(50,+∞)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持有聚集索引a50X 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

参考资料

  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

InnoDB -- Next-Key Lock

上一篇:linux脚本实现excel文件内容读取到数据库


下一篇:SQL server 自带存储过程 sp_rename