参考
https://xiaolincoding.com/mysql/lock/mysql_lock.html
全局锁(备份数据库)
加全局锁
flush tables with read lock
释放全局锁
unlock tables
执行后,整个数据库就处于只读状态了,这时其他线程执行以下操作,都会被阻塞:
- 对数据的增删改操作,比如 insert、delete、update等语句;
- 对表结构的更改操作,比如 alter table、drop table 等语句。
全局锁主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。
加全局锁又会带来什么缺点呢?
加上全局锁,意味着整个数据库都是只读状态。
那么如果数据库里有很多数据,备份就会花费很多的时间,关键是备份期间,业务只能读数据,而不能更新数据,这样会造成业务停滞。
但是这样会影响业务,那有什么其他方式可以避免?
如果数据库的引擎支持的事务支持 可重复读的隔离级别,那么在 备份数据库之前先开启事务,会先创建 Read View,然后整个事务执行期间都在用这个 Read View(可重复读隔离级别的特性),而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下,即使其他事务更新了表的数据,也不会影响备份数据库时的 Read View,这就是事务四大特性中的隔离性,这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump,在使用 mysqldump 时加上 –single-transaction
参数的时候,就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎默认的事务隔离级别正是可重复读,因此可以采用这种方式来备份数据库。
但是,对于 MyISAM 这种不支持事务的引擎,在备份数据库时就要使用全局锁的方法。
表级锁
1、表锁
加表锁
//表级别的共享锁,也就是读锁; lock tables t_student read; //表级别的独占锁,也就是写锁; lock tables t_stuent write;
释放表锁
unlock tables
表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。
不过尽量避免在使用 InnoDB 引擎的表使用表锁,因为表锁的颗粒度太大,会影响并发性能,InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。
2、MDL锁(元数据锁--表结构变更)
我们不需要显示的使用 MDL,因为当我们对数据库表进行操作时,会自动给这个表加上 MDL:
-
- 对一张表进行 CRUD 操作时,加的是 MDL 读锁(可以加其它 MDL 读锁,也就是可以进行其它 CRUD。但是不可以加 MDL 写锁,即在这期间不可变更表结构);
- 对一张表做 结构变更 操作的时候,加的是 MDL 写锁(不可以加 MDL 读锁,也就是在这期间不可以被 CRUD。当然也不可以加 MDL 写锁);
MDL 是在事务提交后才会释放,这意味着事务执行期间,MDL 是一直持有的。
MDL 是为了保证当用户对表执行 CRUD 操作时,防止其他线程对这个表结构做了变更。
-
- 当有线程在执行 select、insert、delete、update 语句( 加 MDL 读锁)的期间,如果有其他线程要更改该表的结构( 申请 MDL 写锁),那么将会被阻塞,直到执行完 select、insert、delete、update 语句( 释放 MDL 读锁)。
- 反之,当有线程对表结构进行变更( 加 MDL 写锁)的期间,如果有其他线程执行了 CRUD 操作( 申请 MDL 读锁),那么就会被阻塞,直到表结构变更完成( 释放 MDL 写锁)。
申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待,会阻塞后续该表的所有 CRUD 操作。这就会导致一个现象
如果一个进行 CRUD 的长事务A一直没有提交,这时候它长时间持有MDL读锁不释放。此时一个新线程 B 想要变更表结构,申请了 MDL 写锁,就会在队列进行等待。而它后面想要进行 CRUD 操作的所有线程就会一直阻塞住,这时数据库的线程很快就会爆满了。因为它们要申请 MDL 读锁,而在队列里前面有优先级更高的线程 B申请的 MDL 写锁在等待。
2、意向锁(快速判断表里是否有记录被加行级X独占锁)
- 在使用 InnoDB 引擎的表里对某些记录加上 行级「共享锁」之前,需要先在表级别加上一个 表级「意向共享锁」IS;
- 在使用 InnoDB 引擎的表里对某些纪录加上 行级「独占锁」之前,需要先在表级别加上一个 表级「意向独占锁」IX;
而普通的 select 是不会加行级锁的,普通的 select 语句是利用 MVCC 实现一致性读,是无锁的。不过,select 也是可以对记录加共享锁和独占锁的,需要特殊指定。
也就是,当执行插入、更新、删除操作,需要先加上 表级「意向独占锁」,然后对该记录加 行级独占锁。
意向共享锁和意向独占锁是表级锁
- 不会和行级的共享锁和独占锁发生冲突
- 意向锁之间也不会发生冲突
- 只会和 共享表锁(lock tables ... read)和 独占表锁(lock tables ... write)发生冲突。
它的作用是什么呢?
假如Innodb需要对一个table上一个表锁(例如数据备份、alter table、drop table、 create index 之类的操作,系统会对整个表锁定,这说的表锁不是意向锁,而是独占表锁,请大家不要混淆),就需要先判断是否有某个行被上了行锁,如果有的话,说明某个或某些行正在执行读写操作,系统需要等待这个写操作完成了才能做备份之类的工作,才能加表级X独占锁。
3、AUTO-INC 锁(自增主键时 INSERT前加上)
表里的主键设置成自增是通过对主键字段声明 AUTO_INCREMENT
属性实现的。
之后可以在插入数据时,可以不指定主键的值,数据库会自动给主键赋值递增的值,这主要是通过 AUTO-INC 锁实现的。
-
- 在插入数据时,会加一个表级别的 AUTO-INC 锁,然后为被
AUTO_INCREMENT
修饰的字段生成新的自增值 - 等插入完成后,把 AUTO-INC 锁立即释放掉(不是在一个事务提交后才释放)
- 在插入数据时,会加一个表级别的 AUTO-INC 锁,然后为被
也就是说对同一个表的插入操作 生成自增值 和 插入 都是串行的。那么,一个事务在持有 AUTO-INC 锁的过程中(生成自增值和插入操作),要向该表的插入语句都会被阻塞,这样会影响插入性能。
因此, 在 MySQL 5.1.22 版本开始,InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候,会为被 AUTO_INCREMENT
修饰的字段加上轻量级锁,只是生成自增值之后就把这个轻量级锁释放了,而不需要等待整个插入语句执行完后才释放锁。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量,是用来控制选择用 AUTO-INC 锁,还是轻量级的锁。
-
- 当 innodb_autoinc_lock_mode = 0,就采用 AUTO-INC 锁,语句执行结束后才释放锁;
- 当 innodb_autoinc_lock_mode = 2,就采用轻量级锁,申请自增主键后就释放锁,并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1:
- 普通 insert 语句,自增锁在申请之后就马上释放;
- 类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
auto-inc锁和轻量级锁带来的结果是,在「主从复制的场景」中会发生 从库和主库 数据不一致的问题(待研究)。
行级锁
InnoDB 引擎是支持行级锁的,而 MyISAM 引擎并不支持行级锁。
不同的 SQL 语句当然会加不同的锁,总结起来主要分为五种情况:
- SELECT … 语句正常情况下为快照读,不加锁;
- SELECT … LOCK IN SHARE MODE 语句为当前读,加 S 锁;
- SELECT … FOR UPDATE 语句为当前读,加 X 锁;
- 常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读,加 X 锁;
- 常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁,且这些语句为隐式提交,不能回滚。
其中,当前读的 SQL 语句的 where 从句的不同也会影响加锁,包括是否使用索引,索引是否是唯一索引等等。
在读已提交隔离级别下,行级锁的种类只有记录锁,也就是仅仅把一条记录锁上。
在可重复读隔离级别下,行级锁的种类除了有记录锁,还有间隙锁(目的是为了避免幻读),所以行级锁的种类主要有三类:
行级锁的类型主要有三类:
-
- Record Lock,记录锁,也就是仅仅把一条记录锁上;
- Gap Lock,间隙锁,锁定【两条记录中间的】一个范围,但是不包含记录本身;
- Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定后面闭区间那条记录本身。
1、Record Lock
Record Lock 称为记录锁,锁住的是一条记录。而且记录锁是有 S 共享锁和 X 排他锁之分的:
2、Gap Lock(范围锁,前开后开区间)
Gap Lock 称为间隙锁(顾名思义,为了锁住 两条记录中间不存在的间隙的那段范围),只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
假设,表中有一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了,这样就有效的防止幻读现象的发生。
间隙锁之间是兼容的,间隙锁的意义只在于阻止区间被插入或删除,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁的目的是防止插入幻影记录而提出的。
3、Next-Key Lock(前开后闭区间,后闭的那条记录即记录锁)
Next-Key Lock 称为临键锁,是 Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
假设,表中有一个范围 id 为(3,5] 的 next-key lock,那么其他事务即不能插入 id = 4 记录,也不能修改和删除 id = 5 这条记录。
虽然相同范围的间隙锁是多个事务相互兼容的,但对于记录锁,我们是要考虑 X 型与 S 型关系,X 型的记录锁与 X 型的记录锁是冲突的。
比如,一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,就会被阻塞。
4、插入意向锁
一个事务在插入一条记录的时候,需要判断插入位置是否已被其他事务加了间隙锁(next-key lock 也包含间隙锁)。
如果有的话,插入操作就会发生阻塞,直到拥有间隙锁的那个事务提交为止(释放间隙锁的时刻),在此期间会生成一个插入意向锁,表明有事务想在某个区间插入新记录,但是现在处于等待状态。
插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。(????)
MySQL 是怎么加行级锁的?
行级锁加锁规则比较复杂,不同的场景,加锁的形式是不同的。
加锁的对象是索引,加锁的基本单位是 next-key lock,它是由记录锁和间隙锁组合而成的,next-key lock 是前开后闭区间,而间隙锁是前开后开区间。
但是,next-key lock 在一些场景下会退化成记录锁或间隙锁。
那到底是什么场景呢?总结一句,在能使用记录锁或者间隙锁就能避免幻读、不可重复读现象的场景下, next-key lock 就会退化成记录锁或间隙锁。
有什么命令可以分析加了什么锁?
我们可以通过 select * from performance_schema.data_locks\G;
这条语句,查看事务执行 SQL 过程中加了什么锁。
从上图可以看到,共加了两个锁,分别是:
-
- 表锁:X 类型的意向锁;
- 行锁:X 类型的记录锁;
这里我们重点关注行级锁,图中 LOCK_TYPE 中的 RECORD 表示行级锁,而不是记录锁的意思。
通过 LOCK_MODE 可以确认是 next-key 锁,还是间隙锁,还是记录锁:
- 如果 LOCK_MODE 为
X
,说明是 next-key 锁; - 如果 LOCK_MODE 为
X, REC_NOT_GAP
,说明是记录锁; - 如果 LOCK_MODE 为
X, GAP
,说明是间隙锁;
如果是二级索引项加行级锁,而且还会对查询到的记录的主键索引项上加「记录锁」。
(如果是二级唯一索引,在二级索引上加锁规则 和 主键索引的相同)
1、唯一索引等值查询
- 当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。
(1)记录存在
这个很好理解,因为查的只是这一条数据,所以 记录锁就够了
怎么用 next-key lock 退化的语言描述?
唯一索引 等值查询,当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
(2)记录不存在
查询不存在于表里的 id = 2 的记录
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id = 2 for update; Empty set (0.03 sec)
此时事务 A 在 id = 5 记录(第一条大于该查询记录的记录)的主键索引上加的是间隙锁,锁住的范围是 (1, 5)。
接下来,如果有其他事务插入 id 值为 2、3、4 这一些记录的话,这些插入语句都会发生阻塞。
注意,如果其他事务插入的 id = 1 或者 id = 5 的记录话,并不会发生阻塞,而是报主键冲突的错误,因为表中已经存在 id = 1 和 id = 5 的记录了。
唯一索引 等值查询,当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。
2、唯一索引范围查询
(1)针对「大于或者大于等于」的范围查询
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id > 15 for update; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 20 | 香克斯 | 39 | +----+-----------+-----+ 1 row in set (0.01 sec)
查找大于 15 的记录
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id >= 15 for update; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 20 | 香克斯 | 39 | +----+-----------+-----+ 1 row in set (0.01 sec)
查找大于等于 15 的记录
(10, 15] 的 next-key lock 退化为了 15 上的记录锁
唯一索引 范围查询,在满足一些条件的时候,索引的 next-key lock 退化为间隙锁(这里×)或者记录锁(这里√ (10, 15] 的 next-key lock 退化为了 15 上的记录锁)。
(2)针对「小于或者小于等于」的范围查询
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id < 6 for update; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | 路飞 | 19 | | 5 | 索隆 | 21 | +----+--------+-----+ 3 rows in set (0.00 sec)
查找 id < 6 的记录(id = 6 这条记录不存在)
(5,10] 的 next key lock 退化为了 (5,10) 的间隙锁
唯一索引范围查询,在满足一些条件的时候,索引的 next-key lock 退化为间隙锁(这里√ (5,10] 的 next key lock 退化为了 (5,10) 的间隙锁)或者记录锁(这里×)。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id <= 5 for update; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | 路飞 | 19 | | 5 | 索隆 | 21 | +----+--------+-----+ 2 rows in set (0.00 sec)
查找 id <= 5 的记录(id = 5 这条记录存在)
这里没有发生退化
唯一索引 范围查询,在满足一些条件的时候,索引的 next-key lock 退化为间隙锁(这里×)或者记录锁(这里×)。
3、非唯一索引等值查询
当我们用非唯一索引进行等值查询的时候,因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有 满足查询条件的记录 才会对它们的 主键索引加锁。
(1)记录不存在
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where age = 25 for update; Empty set (0.00 sec)
查找 age = 25 的记录(age = 25 的记录不存在)
和之前唯一索引类似,给 age = 25 所在的间隙加上间隙锁
非唯一索引 等值查询,当查询的记录「不存在」时,扫描到第一条大于条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。
此时,如果有其他事务插入了 age 值为 23、24、25、26、....、38 这些新记录,那么这些插入语句都会发生阻塞。
不过对于 插入 age = 22 或 age = 39 记录 的语句,在一些情况是可以成功插入的,而一些情况则无法成功插入,这是为什么呢?
二级索引树是按照二级索引值(age列)按顺序存放的,在相同的二级索引值情况下, 再按主键 id 的顺序存放。
间隙锁两端的记录是 (id=10, age=22) 和 (id=20,age=39),要插到这两条记录中间的都不能成功。也就是说:
-
- 对于那些 age=22 的,如果 id<10 可以成功,如果 id>10 不可以成功
- 对于那些 age=39 的,如果 id>20 可以成功,如果 id<20 不可以成功
分析结果中,LOCK_DATA 有两个数:
-
- 第一个数 39 代表在二级非唯一索引 age 上的间隙锁 (22, 39)
- 第二个数 20 代表在二级非唯一索引 age 上的 (10,20) 的 id 也不可以插入
(2)记录存在
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where age = 22 for update; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 10 | 山治 | 22 | +----+--------+-----+ 1 row in set (0.00 sec)
查找 age = 22 的记录(age = 22 的记录存在)
非唯一索引 等值查询,当查询的记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录。从扫描到的第一个等于条件的开始,到扫描到第一个不等于条件的停止扫描,然后在扫描的过程中,对扫描到的等于条件的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
如上图,第一条不满足条件(age = 22 )的记录 (age = 39, id = 20)上的 next-key lock 退化为了间隙锁
由于是非唯一索引,两个间隙锁的 LOCK_DATA 第二条是怎样的?
-
- next-key 锁 (21,22] 的 LOCK_DATA 是 22,10
- age = 21, id<5 的可以插入, age>5 的不可以插入
- age = 22 的都不可以插入
- 间隙锁 (22,39) 的 LOCK_DATA 是 39,20
- age = 39, id>20 的可以插入,age<20 的不可以插入
- next-key 锁 (21,22] 的 LOCK_DATA 是 22,10
4、非唯一索引范围查询
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where age >= 22 for update; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 10 | 山治 | 22 | | 20 | 香克斯 | 39 | +----+-----------+-----+ 2 rows in set (0.01 sec)
查找 age >= 22 的记录(age = 22 的记录存在)
非唯一索引 范围查询,索引的 next-key lock 不会退化为间隙锁和记录锁。(因为要防止在范围的边缘插入多条和边缘一样的记录)
5、没有加索引的查询和更新
如果 锁定读时 查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况,update 和 delete 语句如果查询条件不加索引,那么由于扫描的方式是全表扫描,于是就会对每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表。
因此,在 线上在执行 update、delete、select ... for update 等具有加锁性质的语句(单纯的 select 现在都不是锁定读了,而是无锁化的 MVCC 快照读),一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。
总结
唯一索引等值查询:
- 当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。
非唯一索引等值查询(条件里的一个等值可能对应多条记录):
- 当查询的记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录。从扫描到的第一个等于条件的开始,到扫描到第一个不等于条件的停止扫描,然后在扫描的过程中,对扫描到的等于条件的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
- 当查询的记录「不存在」时,扫描到第一条大于该查询记录的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于:
- 唯一索引在满足一些条件的时候,索引的 next-key lock 会退化为间隙锁或者记录锁。
- 非唯一索引范围查询,索引的 next-key lock 不会退化为间隙锁和记录锁。
- 如下图,查 age>=22
- 如果 age 是 唯一索引,age = 22 上的 next-key lock 会退化为记录所
- 如果 age 是非唯一索引,age = 22 上的 next-key lock 就不会退化
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库