首页 > 数据库技术 > 详细

InnoDB -- Next-Key Lock

时间：2020-05-23 11:09:59      阅读：60      评论：0      收藏：0      [点我收藏+]

本文转载自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进行说明

表初始化

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操作获得了聚集索引a（Clustered Index）上30的X Lock

Session B

mysql> SET SESSION TX_ISOLATION=‘READ-COMMITTED‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT 25;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SELECT 35;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+-----------------------+---------------------+
| trx_id  | trx_state | trx_requested_lock_id | trx_isolation_level |
+---------+-----------+-----------------------+---------------------+
| 1322764 | RUNNING   | NULL                  | READ COMMITTED      |
| 1322763 | RUNNING   | NULL                  | READ COMMITTED      |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM t WHERE a=30 LOCK IN SHARE MODE; # Blocked
  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

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等值匹配不成功

表初始化

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操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，并不加任何锁

Session B

mysql> SET SESSION TX_ISOLATION=‘READ-COMMITTED‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT 34;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SELECT 36;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SELECT 35;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+-----------------------+---------------------+
| trx_id  | trx_state | trx_requested_lock_id | trx_isolation_level |
+---------+-----------+-----------------------+---------------------+
| 1322802 | RUNNING   | NULL                  | READ COMMITTED      |
| 1322801 | RUNNING   | NULL                  | READ COMMITTED      |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.00 sec)
  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等值匹配不成功

表初始化

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操作获得了聚集索引a（Clustered Index）上35的X Lock，但a=35不存在，在(30,40)上加上Gap Lock

Session B

mysql> SET SESSION TX_ISOLATION=‘REPEATABLE-READ‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT 35; # Blocked
  1. 将Session B的事务隔离级别设置为REPEATABLE-READ
  2. Session B的事务尝试插入a=35，但由于事务1323280已经持有了(30,40)上的Gap Lock，因此被阻塞（详细信息见下节）

技术分享图片

Session A

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1323281:391:3:5 | 1323281     | X,GAP     | RECORD    | `test`.`t` | PRIMARY    |        391 |         3 |        5 | 40        |
| 1323280:391:3:5 | 1323280     | X,GAP     | RECORD    | `test`.`t` | PRIMARY    |        391 |         3 |        5 | 40        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1323281           | 1323281:391:3:5   | 1323280         | 1323280:391:3:5  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+-----------------------+---------------------+
| trx_id  | trx_state | trx_requested_lock_id | trx_isolation_level |
+---------+-----------+-----------------------+---------------------+
| 1323281 | LOCK WAIT | 1323281:391:3:5       | REPEATABLE READ     |
| 1323280 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1323281, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 140546164094720, query id 119 localhost root executing
INSERT INTO t SELECT 35
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 391 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1323281 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000028; asc    (;;
 1: len 6; hex 000000142f41; asc     /A;;
 2: len 7; hex a7000001fd0137; asc       7;;
---TRANSACTION 1323280, ACTIVE 99 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 4, OS thread handle 140546164295424, query id 123 localhost root starting

mysql> INSERT INTO t SELECT 35;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
  1. 在事务隔离级别为REPEATABLE READ时，尝试给不存在的值上锁，会产生Gap Lock
  2. 在事务1323280插入a=35成功，因为其他事务（1323281）暂不持有包含a=35的Gap Lock，因此无法阻塞事务1323280的插入操作
  3. 插入成功后，事务1323280持有a=35的X Lock

技术分享图片

Session B

mysql> INSERT INTO t SELECT 35; # Timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM t WHERE a=37 FOR UPDATE;
Empty set (0.00 sec)

事务1323280持有(30,40)的Gap Lock，但无法阻止事务1323281获得(35,40)上的Gap Lock（事务1323280已获得a=35的X Lock）

技术分享图片

Session A

mysql> INSERT INTO t SELECT 33;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SELECT 36; # Blocked
  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

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将获得聚集索引a上20、30、40上的X Lock

Session B

mysql> SET SESSION TX_ISOLATION=‘READ-COMMITTED‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

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

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

表初始化

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将获得聚集索引a上20的X Lock，并将对应地获得(10,20)上的Gap Lock
  3. 依据Next-Key Lock，事务1332194还将获得聚集索引a上30的X 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. 成功插入5、35、45、55，表明事务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并不持有聚集索引a上10的X Lock，事务1332195可以顺利获取聚集索引a上10的X Lock
  2. 事务1332194持有聚集索引a上30的X 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)

示意图

技术分享图片

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将获得辅助唯一索引b上30、40的X Lock，并获得对应的聚集索引a上50、40上的X Lock

Session B

mysql> SET SESSION TX_ISOLATION=‘READ-COMMITTED‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE b=30 FOR UPDATE; # Blocked
  1. 将Session B的事务隔离级别设置为READ COMMITTED
  2. 事务1324402已经持有辅助唯一索引b上30的X Lock，因此会被阻塞（详细信息见下节）

Session A

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324403:405:4:6 | 1324403     | X         | RECORD    | `test`.`t` | b          |        405 |         4 |        6 | 30        |
| 1324402:405:4:6 | 1324402     | X         | RECORD    | `test`.`t` | b          |        405 |         4 |        6 | 30        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324403           | 1324403:405:4:6   | 1324402         | 1324402:405:4:6  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

Session B

mysql> SELECT * FROM t WHERE b=30 FOR UPDATE; # Timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM t WHERE a=50 FOR UPDATE; # Blocked

事务1324402已经持有聚集索引b上50的X Lock，因此会被阻塞（详细信息见下节）

Session A

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324403:405:3:6 | 1324403     | X         | RECORD    | `test`.`t` | PRIMARY    |        405 |         3 |        6 | 50        |
| 1324402:405:3:6 | 1324402     | X         | RECORD    | `test`.`t` | PRIMARY    |        405 |         3 |        6 | 50        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324403           | 1324403:405:3:6   | 1324402         | 1324402:405:3:6  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

示意图

技术分享图片

RR+Secondary Unique Index+Range

  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将获得唯一辅助索引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

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

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

Session A

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324513:410:3:2 | 1324513     | X         | RECORD    | `test`.`t` | PRIMARY    |        410 |         3 |        2 | 10        |
| 1324512:410:3:2 | 1324512     | X         | RECORD    | `test`.`t` | PRIMARY    |        410 |         3 |        2 | 10        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324513           | 1324513:410:3:2   | 1324512         | 1324512:410:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

Session B

mysql> SELECT * FROM t WHERE a=10 FOR UPDATE; # Timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO t VALUES (5,45);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (6,55); # Blocked
  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)

示意图

技术分享图片

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)

示意图

技术分享图片

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)

示意图

技术分享图片

在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将持有聚集索引a上30、50的X Lock

Session B

mysql> SET SESSION TX_ISOLATION=‘READ-COMMITTED‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE a=10 FOR UPDATE;
+----+----+
| a  | b  |
+----+----+
| 10 | 50 |
+----+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE a=20 FOR UPDATE;
+----+----+
| a  | b  |
+----+----+
| 20 | 60 |
+----+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE a=40 FOR UPDATE;
+----+----+
| a  | b  |
+----+----+
| 40 | 80 |
+----+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE a=30 FOR UPDATE; # Blocked
  1. 聚集索引a上的10、20、40并未被其他事务锁定，事务1324625能成功获取它们的X Lock
  2. 事务1324624持有聚集索引a上的30的X lock，事务1324625被阻塞（详细信息见下节）

Session A

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324625           | 1324625:414:3:4   | 1324624         | 1324624:414:3:4  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324625:414:3:4 | 1324625     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        4 | 30        |
| 1324624:414:3:4 | 1324624     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        4 | 30        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

Session B

mysql> SELECT * FROM t WHERE a=30 FOR UPDATE; # Timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM t WHERE a=50 FOR UPDATE; # Blocked

事务1324624持有聚集索引a上的50的X lock，事务1324625被阻塞（详细信息见下节）

Session A

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324625:414:3:6 | 1324625     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        6 | 50        |
| 1324624:414:3:6 | 1324624     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        6 | 50        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324625           | 1324625:414:3:6   | 1324624         | 1324624:414:3:6  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

示意图

技术分享图片

RR+No Index

表初始化

mysql> CREATE TABLE t (
    -> a INT NOT NULL,
    -> b INT NOT NULL,
    -> PRIMARY KEY (a)
    -> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES (10,50),(20,60),(30,70),(40,80),(50,90);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

Session A

mysql> SET SESSION TX_ISOLATION=‘REPEATABLE-READ‘;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE b=70 FOR UPDATE;
+----+----+
| a  | b  |
+----+----+
| 30 | 70 |
+----+----+
1 row in set (0.01 sec)

mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+-----------------------+---------------------+
| trx_id  | trx_state | trx_requested_lock_id | trx_isolation_level |
+---------+-----------+-----------------------+---------------------+
| 1324610 | RUNNING   | NULL                  | REPEATABLE READ     |
+---------+-----------+-----------------------+---------------------+
1 row in set (0.00 sec)
  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

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持有聚集索引a上50的X Lock，事务1324611被阻塞（详细信息见下节）

Session A

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1324611           | 1324611:414:3:6   | 1324610         | 1324610:414:3:6  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1324611:414:3:6 | 1324611     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        6 | 50        |
| 1324610:414:3:6 | 1324610     | X         | RECORD    | `test`.`t` | PRIMARY    |        414 |         3 |        6 | 50        |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

示意图

技术分享图片

参考资料

  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

原文：https://www.cnblogs.com/yungyu16/p/12941499.html
踩
(0)
赞
(0)
   
举报
评论 一句话评论（0）
分享档案
更多>
2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)
最新文章
更多>
教程昨日排行
更多>
友情链接
汇智网   PHP教程   插件网  
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣，分享程序人生！