写在前面
本文如无特殊说明,使用的存储引擎都是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;
这里为什么不阻塞等待呢?是因为
DTRWLFTWRL的排它读锁只有在会话结束时才会被释放,而这里又阻塞等待,则会话永远不会结束,就互相等待了,很明显不能出现这种情况。
- 会话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间隙都是可以正常插入数据的,说明此时并没有间隙锁,为什么不需要加呢?因为是唯一索引,所以不可能通过插入 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锁,表S锁
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机制如何让事务读取到该读的数据
加锁原则(重要!!!)
: