05mysql的锁分析_读已提交有共享锁吗

写在前面

本文如无特殊说明，使用的存储引擎都是InnoDB.

只要是共享的资源就会存在并发访问数据导致的数据一致性问题，数据库也是如此，数据库需要合理的设置数据的访问规则，而这种用来设置数据访问规则的数据结构我们就叫做是锁。本文就一起来看下MySQL都提供了哪些锁机制。

1：MySQL提供了哪些锁

按照锁的力度，分为全局锁，表锁，行锁，如下思维导图：

接下来我们按照锁力度从大到小的顺序来依次共同学习下，下面我们就开始吧！

2：全局锁

server层提供，与存储引擎层无关，一般是执行器负责具体执行。

全局锁即给整个库加锁，执行的语句是flush tables with read lock;，增加的是全局的共享读锁，也可以简单叫做是全局读锁（不知道有没有全局写锁！），因此如insert，update，delete，alter table等非读操作，其他会话的该类操作都会被阻塞，本会话的该类操作是直接报错。该锁的生命周期是会话，当会话结束后，锁会自动释放，下面通过一个例子看下如何使用。

• 启动会话A，会话B
• 会话A执行FTWRL

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.07 sec)
1
2

• 会话A执行查询正常执行

mysql> select * from t;
+-----+---+----+
| ID  | k | s  |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

• 会话A执行更新直接返回失败

mysql> update t set s='aa' where id=1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> select * from information_schema.innodb_locks;
1
2
3

这里为什么不阻塞等待呢？是因为DTRWL FTWRL的排它读锁只有在会话结束时才会被释放，而这里又阻塞等待，则会话永远不会结束，就互相等待了，很明显不能出现这种情况。

• 会话B执行查询

mysql> select * from t;
+-----+---+----+
| ID  | k | s  |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

正常执行。

• 会话B执行更新

阻塞等待，直到会话A结束，才可正常执行。

3：表级锁

MySQL目前支持的表级锁有两种，一种是表锁，一种是MDL（meta data lock）,其中第一种是针对表业务数据的锁，第二种是针对表元数据（如列信息，索引信息，表名称等）的锁。

3.1：表锁

对应的语法是lock tables ... read/write，解锁unlock tables/会话结束，当执行了语句后，会话内只能对加了相关锁的表进行操作，未加锁的表不能做任何操作，包括读操作，这是因为MySQL规范要求一次申请所需要的所有锁，这样做的原因是为了避免死锁的发生，当然也可以将这种不可以操作其他表的行为看做是MySQL的约定,其中读锁是共享读锁（即读和读不互斥），写锁是排他写锁（即写和写互斥），读写锁之间的关系如下:

写锁因为是共享写锁，所以多个线程可以同时执行读操作。
读锁和写锁之间互斥，即当有写锁时，无法加读锁，反之亦然。
写锁和写锁之间互斥，即当有写锁时，无法加写锁。
1
2
3

一起看个例子:

• 启动会话A，会话B
• 会话A对表t，t1分别加读锁，写锁

mysql> lock tables t read, t1 write;
Query OK, 0 rows affected (0.00 sec)
1
2

• 会话A对表t执行读操作

mysql> select * from t;
+-----+---+----+
| ID  | k | s  |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

因为lock tables t read, t1 write;加了t的读锁，所以可以正常执行。

• 会话B对表t执行读操作

mysql> select * from t;
+-----+---+----+
| ID  | k | s  |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

因为读锁是共享的，所以可以执行。

• 会话A对表x执行写操作重要！

mysql> update t set s=1 where id=1;
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> select * from x;
ERROR 1100 (HY000): Table 'x' was not locked with LOCK TABLES
1
2
3
4

从错误Table 'x' was not locked with LOCK TABLES可以看出原因是因为没有在lock tables语句中申请该锁。

• 会话B对表x执行写操作

mysql> update x set status=1 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3

可以正常执行，因为并未显式执行过lock tables语句。

3.2：MDL

考虑这样的一种场景，我们正在执行数据查询，但是其他线程执行ddl将某个列删除了，这个时候已经返回给我们的数据和数据库的表结构就对不上了，这样肯定是不行的，为此，MySQL在5.5版本中引入了MDL，用来对类似于DDL这种会改变表结构的操作进行加锁。MDL分为MDL读锁和MDL写锁，且MDL没有显式加锁和释放锁的方式，都是隐式的自动加锁和释放锁，在执行增删改查操作时，会自动获取MDL读锁，事务结束自动释放锁，执行DDL时会自动获取MDL写锁，DDL语句执行完毕自动释放锁，其中读锁是共享读锁，写锁是排它写锁，二者的关系如下：

写锁因为是共享写锁，所以多个线程可以同时执行读操作。
读锁和写锁之间互斥，即当有写锁时，无法加读锁，反之亦然。
写锁和写锁之间互斥，即当有写锁时，无法加写锁。
1
2
3

当对一个大表执行DDL时，需要格外注意，因为该操作会获取MDL写锁，应用程序对该表所有的增删改查都会因为无法获取MDL读锁而被阻塞，很容易引起线上事故。为了尽量的降低锁力度，InnoDB存储引擎提供了以行为单位的锁实现，我们可以简单称之为行锁，下面一起来看下。

4：行锁

当你发现某个sql执行被阻塞，且通过show processlist查看State=Updating时就说明是被行锁阻塞了，下面通过实例来验证下。

当执行如update t set c=x where id=1之类的语句时，就会获取id=1这一行的行写锁，此时行写锁(update 语句, select xxx for update)，行读锁(select xxx lock in share mode)注意select xx from t where id=1不获取任何锁都无法被获取，下面我们来一起看下。

• 启动会话A,B,C
• 在会话A启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
1
2

• 在会话A执行语句更新id=1的行

mysql> update t set c=c where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
1
2
3

• 在会话B启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
1
2

• 在会话B执行语句更新id=1的行

可以看到阻塞等待了。

• 在会话C执行show processlist;

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| 18 | root | localhost:57178 | test | Query   |   18 | Updating | update t set c=c where id=1 |
| 20 | root | localhost:58797 | test | Query   |    0 | NULL     | show processlist            |
| 21 | root | localhost:58801 | test | Sleep   |   55 |          | NULL                        |
| 22 | root | localhost:64840 | test | Sleep   | 1155 |          | NULL                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+
1
2
3
4
5
6
7
8
9

可以看到第一行线程ID为18的就是被阻塞而导致等待的线程，那么我们如何找到是谁占用了id=1的行锁呢？没有特别好的办法，但是我们知道行锁使用的是两阶段锁协议，即在执行语句时才会获取锁，但是语句执行完毕不立即释放锁，而是在事务提交时才释放锁，因此可以断定既然锁一直没有被释放，肯定是因为其所在的事务没有提交，所以我们可以通过过滤长事务的方式找到罪魁祸首,当然只是可能是哪个，但是必要时误杀也影响不大。可通过如下语句过滤：

mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G
*************************** 1. row ***************************
                    trx_id: AAF56
                 trx_state: RUNNING
               trx_started: 2022-07-15 14:18:56
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 320
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
1 row in set (0.03 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

这里我使用的事务已执行时长是60秒，可以根据情况来设置时长，trx_mysql_thread_id: 26其中的25就是线程ID，kill 26即可，如下图：

5：间隙锁

当show processlist,state为update时就说明是被间隙锁阻塞了。

准备数据：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,10,100),(5,50,500),
(100,1000,10000),(150,1500,15000),(200,2000,2000),(250,2500,25000);
1
2
3
4
5
6
7
8
9
10

在了解间隙锁之前，我们必须先明确一个概念，幻读，即相同的查询条件，后一次查询比前一次查询多出了行。对于RR的隔离级别，如果是RR使用的快照度，则是没有幻读这个问题的，如下面的例子：

• 启动2个会话A，B
• 在会话A启动事务，并执行查询

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

mysql> select * from t where id in (5,500);
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

• 在会话B启动事务，并插入ID=500数据

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

mysql> insert into t values(500,500,500);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8

• 在会话A,再次执行查询

mysql> select * from t where id in (5,500);
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10

可以看到并没有查询到ID=500的行，也就证明了在RR快照读的时候是没有幻读这个问题的，但是如果是当前读呢！当前读读取的是当前的最新数据，是不是RR此时就有幻读的问题了呢？看上去是的，接下来我们再使用当前读来试验下(注意：将数据恢复到初始状态！！！)：

• 启动2个会话A，B
• 在会话A启动事务，并使用当前读执行查询

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

mysql> select * from t where id in (5,500) for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

• 在会话B启动事务，并插入ID=500数据

从图中可以看出，此时插入操作阻塞等待了，其实就是被间隙锁阻塞了，所谓间隙锁，就是对数据的行与行之间的间隙增加的锁，不是很好理解，对于普通列，主键列，唯一索引，非唯一索引间隙锁的加锁行为也是不同的，因此我们分别来看下。

5.1：普通列

• 启动2个会话A，B
• 在会话A启动事务，并使用当前读执行查询

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

mysql> select * from t where d=500 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

• 在会话B插入ID=9的数据

可以看到阻塞等待，实际上加的间隙锁如下图：

其中区间的就是间隙锁，即影响的行的ID值落到区间内都会被间隙锁所阻塞，另外上面的数字的代表是行锁，即(-∞，0)间隙锁，0行锁，(0，5)间隙锁，5行锁，(5,100)间隙锁，100行锁，(100,150)间隙锁，150行锁，(150,200)间隙锁，200行锁，(200,250)间隙锁，250行锁，(250,+∞)间隙锁，其中间隙锁和行锁的组合我们叫做next-key lock,使用左开右闭的格式来表示，即(-∞，0]next-key lock，(0，5]next-key lock，(5,10]next-key lock，(10,15]next-key lock，(15,20]next-key lock，(20,25]next-key lock，(25,supremum]next-key lock,也就是只要是落到了这些区间的就都会被阻塞，为什么要在所有这些间隙都加锁呢，因为所有间隙都有可能产生满足where=500条件的新数据！！！其实此时就是任何值都会阻塞。
如下一些insert的操作（因为insert操作可能会破坏where d=500的条件，所以都会阻塞）：

mysql> insert into t values(9,9,9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=5 where id=0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (7,7,7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (23,23,23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (9999,9999,9999);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
1
2
3
4
5
6
7
8
9
10

如下是一些update的操作，分为两种情况，第一种是update有匹配的行，此时就会被阻塞，因为可能会破坏where d=500的条件，而当没有匹配的行时，因为不会破坏where d=500的条件，所以不会被阻塞，如下分别测试有匹配行和没有匹配行的情况(是否有匹配行已经给出了注释，另外set部分不重要，重要的是where部分)：

mysql> update t set c=c where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=9090 where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=8 where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set c=c where id=0; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set d=8 where id=2; /*无匹配行*/
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set d=8 where id=2222; /*无匹配行*/
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update t set d=8 where id=250; /*有匹配行*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

看到这里，不知道你有没有一个疑问，比如insert操作insert into t values (7,7,7)生成的新数据，并不满足where=500的条件，比如update操作update t set c=c where id=0; /*有匹配行*/也不会产生满足where=500的数据，但是为什么就阻塞了呢？MySQL就这么笨不会加上这个判断吗？我认为不这样做的原因是，代价太大！！！我们的例子肯定是很好判断的，但是实际的情况可就是千变万化了，比如insert into t values (7,7,select dVal from xxx where xxx in(selet ...)),判断的成本必定不可预估。

5.2：主键列

主键列分为两种情况，如果是目标ID对应的行是存在的，则直接给该行上行锁，如果是不存在，则会将其所在的间隙上间隙锁，这里我们先看这个要查询的ID值不存在对应行的情况：

• 启动2个会话A，B
• 在会话A启动事务，并使用当前读执行查询

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=13 for update;
Empty set (0.00 sec)
1
2
3
4
5

• 在会话B启动事务，并插入ID=14的行

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

mysql> insert into t values(14,140,1400);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
1
2
3
4
5

可以看到被阻塞了，阻塞的锁正是(5,100)间隙锁，也是唯一的间隙锁，那么为什么只给这一个间隙加间隙锁呢？是因为对于查询条件where id=13可能满足的ID范围一定在这个范围内，此时其他的行间隙都是可以操作的，如下验证：

mysql> insert into t values(3,30,300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(130,1300,13000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(180,1800,18000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(230,2300,23000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(251,2510,25100);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.3：唯一索引

注意：先将数据恢复到初始状态！！！

• 添加唯一索引

mysql> alter table t add unique index idx_d(`d`);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

在存在对应的值和不存在对应的值是行为是不同的，我们分开来看。

5.3.1：值存在

• 启动2个会话A，B，C
• 在会话A启动事务，并使用当前读执行查询

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

mysql> select * from t where d=500 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

• 在会话B对ID的间隙执行插入测试

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

mysql> insert into t values(3,30,300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(53,530,5300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(123,1230,12300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(230,2230,22300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(23000000,2230,2230000);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

可以看到所有的ID间隙都是可以正常插入数据的，说明此时并没有间隙锁，为什么不需要加呢？因为是唯一索引，所以不可能通过插入 d=500的新数据(唯一索引冲突)，或者是通过update其他数据，即将其他数据的d值改成500(唯一索引冲突)，唯一加的锁是d=50对应的行写锁，可以通过如下方式验证:

• 在会话B执行如下查询

mysql> update t set c=c where d=500;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
1
2

• 在会话C查看连接状态

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                         |
+----+------+-----------------+------+---------+------+----------+------------------------------+
| 15 | root | localhost:49423 | test | Sleep   |  776 |          | NULL                         |
| 16 | root | localhost:49434 | test | Query   |    4 | Updating | update t set c=c where d=500 |
| 17 | root | localhost:9513  | test | Query   |    0 | NULL     | show processlist             |
+----+------+-----------------+------+---------+------+----------+------------------------------+
1
2
3
4
5
6
7
8

注意到行| 16 | root | localhost:49434 | test | Query | 4 | Updating | update t set c=c where d=500 |的状态是Updating，说明是被行锁阻塞了。

5.3.2：值不存在

此时间隙锁是基于d对应的二级索引树来加的，简单来说就是加在d上的，不同于主键，普通列，加在主键上。

• 启动2个会话A，B，C
• 在会话A启动事务，并使用当前读执行查询

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

mysql> select * from t where d=89 for update;
Empty set (0.00 sec)
1
2
3
4
5

• 在会话B测试所有d间隙

mysql> insert into t values (9900,990,99);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values (111100,11110,1111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (300000,30000,3000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (1300000,130000,13000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (1800000,180000,18000);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

可以看到，只有当d的范围在(负无穷,100)时才会被阻塞，也就是说，此时只有(负无穷,100)的间隙锁。其实这个行为和主键列是一样的。

5.4：非唯一索引

注意：先将数据恢复到初始状态！！！

分为值存在和值不存在两种情况来看。

5.4.1：值不存在

• 启动会话A,B
• 在会话A启动事务，并查询c=11的数据

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

mysql> select * from t;
+-----+------+-------+
| id  | c    | d     |
+-----+------+-------+
|   0 |   10 |   100 |
|   5 |   50 |   500 |
| 100 | 1000 | 10000 |
| 150 | 1500 | 15000 |
| 200 | 2000 |  2000 |
| 250 | 2500 | 25000 |
+-----+------+-------+
6 rows in set (0.00 sec)

mysql> select * from t where c=11 for update;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 在会话B启动事务，并试验c的不同间隙加锁情况

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

mysql> insert into t values(RAND() * 900 + 100, 3, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 13, RAND() * 900 + 100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 913, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1113, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1713, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2412, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2501, RAND() * 900 + 100);
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

可以看到只有间隙c(10,50)加了间隙锁，其他的正常。

5.4.2：值存在

• 启动会话A,B
• 在会话A启动事务，并查询c=50的数据

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

mysql> select * from t where c=50 for update;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  5 |   50 |  500 |
+----+------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

• 在会话B执行如下测试

mysql> insert into t values(RAND() * 900 + 100, 6, RAND() * 900 + 100); /*间隙（负无穷,10）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 36, RAND() * 900 + 100); /*间隙（10,50）*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 999, RAND() * 900 + 100); /*间隙（50,1000）*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(RAND() * 900 + 100, 1333, RAND() * 900 + 100); /*间隙（1000,1500）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 1833, RAND() * 900 + 100); /*间隙（1500,2000）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 2433, RAND() * 900 + 100); /*间隙（2000,2500）*/
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(RAND() * 900 + 100, 99999, RAND() * 900 + 100); /*间隙（2500,正无穷）*/
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

可以看到，只有间隙（10,50），（50,1000）会加上间隙锁，其他的正常！

6：其他锁

6.1：意向锁

当我们执行语句lock tables t write语句获取表X锁时，只有满足如下的条件才能获取成功：

1：当前表t不存在其他事务持有表X锁,表S锁
2：当前表t的每一行不存在其他事务持有行X锁
1
2

其中的1很好判断，但是2则需要遍历索引树的每一个叶子节点，判断是否存在锁，这显然是一个全表扫描的过程，性能可想而知好不到那里去，那么为了解决这个问题，就引入了意向锁，具体如下：

select * from t where xxx lock in share mode：先获取满足条件行的S锁，然后获取意向共享锁，即IS锁。
select * from t where xxx for udpate：先获取满足条件行的X锁，然后获取意向排它锁，即IX锁。
1
2

这样，执行语句lock tables t write时的2，就只需要判断当前表是否存在IX锁即可(注意：意向锁是表锁！！！)。IS，IX之间互斥关系如下：

意向锁和行锁之间的互斥关系如下：

意向锁和表锁之间的互斥关系如下：

写在后面

一文搞懂Undo Log版本链与ReadView机制如何让事务读取到该读的数据

MySQL进阶系列：多版本并发控制mvcc的实现 。

详解 MySql InnoDB 中意向锁的作用 。

加锁原则(重要！！！)：