详解MySQL锁

文章目录

• • 前言
  • 全局锁
  • 表级锁
  • 行级锁
  • 死锁

前言

MySQL中的锁大致分为三类：全局锁、表级锁、行锁。本文主要针对这三种锁展开叙述。

关于MySQL的系列文章，请跳转至 MySQL专栏

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：DML语句（数据的增删改）、DDL语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

如果让整个库都处于只读状态，会发生什么：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

看来加全局锁不太好，那能不能不加？

如果不加全局锁做数据的全量备份，会导致在备份过程中库中执行DML和DDL语句等，导致系统备份得到的数据不是一个逻辑时间点的数据，这个视图是逻辑不一致的。

那我们能够拿到一致性视图就解决这个问题了，MySQL的 RR(可重复读) 隔离级别就可以获取一致性视图，保证在一个事务内多次读取相同的数据，查询的结果是一致的。又由于 MVCC 的支持，这个过程中数据也是可以正常更新的。

有了这个功能，为什么还需要FTWRL呢？

一致性读是好，但前提是引擎要支持这个隔离级别。MyISAM 引擎不支持 RR 隔离级别，那么备份就只能通过 FTWRL 方法。

---

表级锁

MySQL 里面表级别的锁有：表锁，元数据锁（meta data lock，MDL），意图锁。

---

表锁

语法是 lock tables tableName read/write，可以用 unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

如果线程 A 中执行 lock tables t1 read, t2 write，则其他线程的 写 t1，读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables前，也只能执行 t1 读，t2 读写的操作。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL锁

MySQL5.5引入了MDL锁，用于保证表中元数据的信息。MDL 不需显示使用，在访问一个表时会被自动加上。作用是，保证读写的正确性。

MDL锁的锁机制是：对一个表做DML操作时，加MDL读锁；对一个表做DDL操作时，加MDL写锁。

• 读锁之间不互斥，可以同时有多个线程对一张表进行增删改查。
• 读写锁、写锁之间是互斥的，保证变更表结构操作的安全性。

事务中的 MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

意向锁

InnoDB 支持多粒度锁定，允许行锁和表锁共存。有两种意向锁的类型，分别：意向共享锁和意向排他锁。

• 意向共享锁（IS）是指在给一个数据行加共享锁前必须先取得该表的 IS 锁
• 意向排他锁（IX）是指在给一个数据行加排他锁前必须先取得该表的 IX 锁

其实，意向锁的作用和 MDL 类似，都是防止在事务进行过程中，执行 DDL 语句的操作导致数据的不一致。表级锁类型兼容性总结在下图：

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

---

行级锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

InnoDB实现标准的行级锁定，其中有两种类型的锁：共享锁和排他锁。

共享锁（S）

共享锁，也称读锁，一个事务获取了一个数据行的读锁，其他事务能获得该行对应的读锁，但不能获得写锁，即一个事务在读取一个数据行时，其他事务可以读，但不能对该数据行进行增删改的操作。

排他锁（X）

排他锁，也成写锁，一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁，写锁优先级最高。

记录锁（Record Lock）

顾名思义，记录锁就是为某行记录加锁（简称行锁），实际上是对索引记录加锁。InnoDB 支持行锁，那么更新同一行数据时会出现锁等待现象。

• 当两个会话同时对一个索引字段，不同行数据进行 update 操作时，更新成功，不会出现锁等待现象；
• 当两个会话同时对一个索引字段的同一行数据进行 update 操作时，其中一个会话会出现锁等待现象；
• 当两个会话同时对一个普通字段（没有索引），不同行数据进行update操作时，会发生什么？

对于上述的第三种情况，当更新的字段没有索引时，即使是不同行的记录，也会出现锁等待现象。所以，InnoDB的记录锁时加载索引上的，如果对非索引字段加记录锁，

间隙锁（GAP Lock）

在 RR 隔离级别中，为了避免幻读现象，引入了 Gap Lock，是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止其他事务插入 15 的值到 t.c1 列中，无论列中是否已经存在任何此类值，因为该范围内所有现有值之间的间隙被锁定。

间隙可能跨越单个索引值、多个索引值，甚至是空的。

例如，事务A 执行 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;SQL，事务B往 t 表中插入 c1 = 15 的数据，出现了锁等待现象。是 Gap Lock 在发挥作用，防止在 事务 A 执行期间出现幻读现象。而 Gap Lock 只应用在 RR 隔离级别中。

Next-Key Lock

Next-Key Lock 是记录锁和间隙锁的组合，当 InnoDB 扫描索引记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的间隙上加上间隙锁。

假设一个索引包含值 10、11、13 和 20。该索引可能的 next-key 锁涵盖以下区间，其中左开右闭。

(negative infinity, 10]	# 负无穷
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)	# 正无穷

此时，按照下面的表格开启两个session，执行到第6步之前，都是可以成功的，第 6 步开始阻塞，第 8 步可以正常执行。这样 sessionA 实际上锁住了一个范围，锁住了 13 所在的范围区间 (11, 13] 和 (13, 20] ， 所以 sessionB 插入 11 和 14 时会被阻塞。

order	sessionA	sessionB
1	begin;
2	select * from test where id = 13 for update;
3		begin;
4		insert into test select 1;
5		insert into test select 8;
6		insert into test select 11;
7		insert into test select 14;
8		insert into test select 21;

上面情况 id 是辅助索引且不是唯一索引的情况，如果是唯一索引呢？

如果将 id 列修改为主键，上面这个表格中，sessionB 中 第 6,7 步都可以执行成功，(11, 13] 和 (13, 20] 区间内的值，除了主键冲突的情况，都可以成功插入表中。这种现象的原因是，因为索引唯一，InnoDB 会把锁降级为 record lock（行锁），只会锁住一个记录而已，这样能很好的提高并发性。

死锁

死锁是一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁，才能占用该资源。如果一个事务一直不释放资源，就需要继续等待下去，直到超过了锁等待时间，会报等待超时的错误。MySQL 中通过 innodb_lock_wait_timeout 参数控制，单位是秒。

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，就是所谓的锁资源请求产生了回路现象，即死循环。

1、在session A 中执行 update tt set name = 'aaa' where score = 60;
2、在session B 中执行 update tt set name='a' where score = 70;
3、在session A 中执行 update tt set name='bb' where score = 70;
4、在session B 中执行 update tt set name = 'aa' where score = 60;

两个session按以上顺序执行，就出现了相互等待资源的现象，也就是死锁现象。

InnoDB 可以自动检测死锁，并自动回滚事务。可以通过参数 innodb_deadlock_detect
查看是否开启死锁检测机制，InnoDB 是默认开启的。

如何降低死锁发生的概率

• 使用 SHOW ENGINE INNODB STATUS命令查看最近死锁的原因，可以帮助调整应用程序以避免死锁
• 如果频繁的死锁警告，启用 innodb_print_all_deadlocks参数收集更广泛的调试信息
• 如果由于死锁而失败，请随时准备重新发出事务
• 保持事务小且持续时间短，以减少它们发生冲突的可能性
• 在进行一组相关更改后立即提交事务，以减少它们发生冲突的可能性，不要让交互式 mysql会话长时间处于打开状态并带有未提交的事务
• 如果您使用锁定读取，请尝试使用较低的隔离级别，如 RC
• 当修改一个事务中的多个表或同一个表中的不同行集时，每次都以一致的顺序执行这些操作
• 添加索引时慎重考虑，以便查询扫描更少的索引记录并设置更少的锁
• 使用较少的锁定
• 使用表级锁序列化您的事务，表级锁可防止对表的并发更新，从而避免死锁，但代价是繁忙系统的响应速度变慢