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)
  • 会话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)
  • 会话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;

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

  • 会话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)

正常执行。

  • 会话B执行更新

在这里插入图片描述

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

3:表级锁

MySQL目前支持的表级锁有两种,一种是表锁,一种是MDL(meta data lock),其中第一种是针对表数据的锁,第二种是针对表元数据的锁。

3.1:表锁

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

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

下面一起来看个例子。

  • 启动会话A,会话B
  • 会话A对表t,t1分别加读锁,写锁
mysql> lock tables t read, t1 write;
Query OK, 0 rows affected (0.00 sec)
  • 会话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)

因为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)

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

  • 会话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

从错误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

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

3.2:MDL

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

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

当对一个大表执行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)
  • 在会话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
  • 在会话B启动事务
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
  • 在会话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                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+

可以看到第一行线程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)

这里我使用的时长是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);

在了解间隙锁之前,我们必须先明确一个概念,幻读,即相同的查询条件,后一次查询比前一次查询多出了行。对于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)
  • 在会话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)
  • 在会话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)

可以看到并没有查询到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)
  • 在会话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)
  • 在会话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

如下是一些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>

看到这里,不知道你有没有一个疑问,比如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)
  • 在会话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

可以看到被阻塞了,阻塞的锁正是(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)

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

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

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)
  • 在会话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)

可以看到所有的ID间隙都是可以正常插入数据的,说明此时并没有间隙锁,为什么不需要加呢?因为是唯一索引,所以不可能通过insert 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
  • 在会话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             |
+----+------+-----------------+------+---------+------+----------+------------------------------+

注意到行| 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)
  • 在会话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)

可以看到,只有当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)
  • 在会话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)

可以看到只有间隙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)
  • 在会话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)

可以看到,只有间隙(10,50),(50,1000)会加上间隙锁,其他的正常!

6:其他锁

6.1:意向锁

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

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

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

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

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

写在后面

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

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

详解 MySql InnoDB 中意向锁的作用

加锁原则参考图(重要!!!)
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页
评论

打赏作者

wang0907

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值