MySQL中锁的几种类型

最新推荐文章于 2024-06-13 21:12:59 发布

菠萝蜜blm

最新推荐文章于 2024-06-13 21:12:59 发布

阅读量1.1k

点赞数

分类专栏：一起成为数据库高手文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_46984703/article/details/139013185

版权

一起成为数据库高手专栏收录该内容

20 篇文章 0 订阅

订阅专栏

MySQL根据加锁的范围，可以分为全局锁、表级锁、行级锁三类。

2.5.1. 锁定读

2.5.1.1. 共享锁和独占锁

事务的读-读情况并不会引起什么问题，对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以MySQL给锁分了个类：

共享锁，Shared Locks，简称 S 锁。在事务要读取一条记录时，需要先获取该记录的 S 锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称 X 锁。在事务要改动一条记录时，需要先获取该记录的X锁。

事务 T1 首先获取了一条记录的 X 锁之后，那么不管事务 T2 接着想获取该记录的 S 锁还是 X 锁都会被阻塞，直到事务 T1 提交。

2.5.1.2. 锁定读语句

有时候想在读取记录时就获取记录的 X 锁，来禁止别的事务读写该记录，为此MySQL存在两种比较特殊的SELECT语句格式：

对读取的记录加 S 锁：

SELECT ... LOCK IN SHARE MODE;

在普通的SELECT语句后边加LOCK IN SHARE MODE，当前事务执行了该语句，会为读取到的记录加S锁，允许别的事务继续获取这些记录的S锁，但是不能获取这些记录的X锁。别的事务想获取这些记录的 X 锁，就会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。

对读取的记录加X锁：

SELECT ... FOR UPDATE;

在普通的SELECT语句后边加FOR UPDATE，当前事务执行了该语句，会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁，也不允许获取这些记录的X锁。如果别的事务想要获取这些记录的S锁或者X锁，就会阻塞，直到当前事务提交之后将这些记录上的X锁释放掉。

2.5.2. 全局锁

全局锁使用方法：

-- 加全局锁
flush tables with read lock
-- 释放全局锁
unlock tables

加了全局锁后，整个数据库处于只读状态，其他线程执行以下状态都会被阻塞：

对数据库的增删改操作，如 insert、delete、update等语句；
对表结构的更改操作，比如 alter table、drop table 等语句。

会话断开后，全局锁自动释放

全局锁的应用场景：

主要用于做全库逻辑备份，备份数据库期间不会因为数据或表结构的更新，造成数据不一致。

例如：

全库逻辑备份期间，用户购买了一件商品，商品业务逻辑涉及多张数据库表的更新；

先备份了用户表的数据
用户购买了商品
备份商品表的数据

备份用户表和商品表之间用户购买了商品，备份的结果就是用户余额没有减少，库存减少了。

全局锁带来的缺点：

整个数据库都是只读状态，庞大的数据量进行备份会花费很长时间，而且不能更新数据，会造成业务停滞。

避免全局锁会影响业务：

数据库引擎的事务支持可重复读隔离级别，备份前先开启事务，会先创建Read View，整个事务期间都在用这个Read View，由于MVCC的支持，备份期间业务依然可以对数据进行更新，这就是隔离性。

备份数据库的工具是 mysqldump，使用 mysqldump 时加上 –single-transaction 参数，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎可以采用这种方式来备份数据库，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

2.5.3. 表级锁

MySQL 里面表级别的锁有这几种：

表锁；
元数据锁（MDL）;
意向锁；
AUTO-INC 锁；

2.5.3.1. 表锁

加表锁可使用以下命令：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;
-- 释放当前会话所有表锁 会话退出也会释放所有表锁
unlock tables

本线程对学生表加了「共享表锁」，任何写操作（包括当前线程和其他线程的写操作）都会被阻塞，直到锁被释放。

本线程对学生表加了「独占表锁」，该锁持有期间，当前线程可以对表进行任何操作（读写），其他事务无法对该表进行任何类型的锁定（包括共享锁和其他独占锁），保证了当前事务对该表的独占访问权限，通常在事务结束时（提交或回滚）释放。

尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能。

2.5.3.2. 元数据锁（MDL）

对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

防止对用户表进行CRUD时，其他线程对这个表结构做了变更。

当有线程在执行 select 语句（加 MDL 读锁）的期间，其他线程要更改该表的结构（申请 MDL 写锁），将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

当有线程对表结构进行变更（加 MDL 写锁）的期间，其他线程执行了 CRUD 操作（申请 MDL 读锁），就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

MDL不需要显示调用，它是在什么时候释放的？

事务执行期间MDL会一直持有，事务提交后会释放。

一个长事务（开启了未提交），对表结构进行变更操作可能会出现的问题：

线程A开启事务，执行select语句，表上会加MDL读锁；
线程B执行查询语句，此时不会阻塞，读读不冲突；
线程C修改表字段，MDL读锁还在占用，线程C无法申请到MDL写锁，会被阻塞
后续对该表的查询语句都会被阻塞，如果有大量的查询语句，线程很快就会爆满

因为申请MDL锁的操作会形成队列，写锁获取优先级高于读锁，出现写锁后续的CRUD都会被阻塞。

2.5.3.3. 意向锁

意向共享锁，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IIS锁和IX锁是兼容的，IX锁和IX锁是兼容的

兼容性	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先在表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

2.5.3.4. AUTO-INC锁

MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

插入数据的时候，为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

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 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

具体案例查看参考文档。

2.5.4. 行级锁（记录锁）

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

普通的 select 语句属于快照读不会对记录加锁。要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

2.5.4.1. Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁（共享锁）和 X 锁（独占锁）之分的：

一个事务对一条记录加了 S 锁后，其他事务也可以继续对该记录加 S锁（S 锁与 S 锁兼容），但是不可以对该记录加 X锁（S 锁与 X 锁不兼容）;
一个事务对一条记录加了 X锁后，其他事务既不可以对该记录加 S 锁（S 锁与 X 锁不兼容），也不可以对该记录加 X 锁（X 锁与 X 锁不兼容）。

2.5.4.2. Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录，有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻读记录而提出的。

给一条记录加了gap锁只是不允许其他事务往这条记录前面的间隙插入新记录，那对于最后一条记录之后的间隙，该咋办呢？两条伪记录：

Infimum记录，表示该页面中最小的记录。
Supremum记录，表示该页面中最大的记录。

为了实现阻止其他事务在该记录后插入新记录，可以给索引中的最后一条记录加上一个gap锁

详细案例查看参考文档。

2.5.4.3. Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

2.5.4.4. MySQL行级锁的加锁规则

唯一索引等值查询：

当查询的记录「存在」，在索引树上定位到这一条记录后，该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录「不存在」，在索引树找到第一条大于该查询记录的记录后，该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

当查询的记录「存在」时，可能存在索引值相同的记录，所以非唯一索引等值查询的过程是一个扫描的过程，扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询加锁规则不同之处在于：