MySQL原理与实践（五）：数据库的锁机制

最新推荐文章于 2024-09-23 09:12:23 发布

温柔狠角色

最新推荐文章于 2024-09-23 09:12:23 发布

阅读量5.7k

点赞数 11

分类专栏： MySQL原理与实践文章标签： MySQL原理与实践全局锁表级锁行锁与间隙锁幻读

本文链接：https://blog.csdn.net/qq_25827845/article/details/91345678

版权

MySQL原理与实践专栏收录该内容

5 篇文章 5 订阅

订阅专栏

（尊重劳动成果，转载请注明出处：https://yangwenqiang.blog.csdn.net/article/details/91345678冷血之心的博客）

关注微信公众号（文强的技术小屋），学习更多技术知识，一起遨游知识海洋~

快速导航：

MySQL原理与实践（一）：一条select语句引出Server层和存储引擎层

MySQL原理与实践（二）：一条update语句引出MySQL日志系统

MySQL原理与实践（三）：由三种数据结构引入MySQL索引及其特性

MySQL原理与实践（四）：由数据库事务引出数据库隔离级别

MySQL原理与实践（五）：数据库的锁机制

MySQL原理与实践（六）：自增主键的使用

前言：

在上一篇MySQL原理与实践：MySQL原理与实践（四）：由数据库事务引出数据库隔离级别的结尾我们提到了为了解决幻读的问题，必须先行介绍MySQL的锁机制，那么在这一篇博文中，我们就来聊一聊MySQL的锁机制吧。本文整理总结于极客时间 -《MySQL实战45讲》，欢迎大家订阅学习，干货满满。

正文：

在并发访问的情况下，数据库中的数据是一种共享的资源。为了处理并发访问过程中出现的问题，并且合理的控制资源的访问规则，就设计出了MySQL的锁机制。根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。而行锁又分为了排他锁，共享锁以及更新锁；从程序的角度上，又可以分为悲观锁和乐观锁；为了解决幻读的问题，又引入了间隙锁的概念。对于这些锁机制中的基本概念和理解，我们将在本篇文章中逐个介绍。

全局锁：

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

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。所以在备份过程中整个库完全处于只读状态。

但是让整库都只读，有如下的缺点：

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

为了避免使用全局锁来进行全库备份，我们可以在支持事务的存储引擎上以可重复读隔离级别下开启一个事务，从而获取到一个一致性的视图。官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。（具体原因可以参考系列文章第四篇：MySQL原理与实践（四）：由数据库事务引出数据库隔离级别）

当然不支持事务的存储引擎址好通过FTWRL来进行全库备份了，这也就是为什么InnoDB会选用率会高于MyISAM存储引擎的重要原因。

表级锁：

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁：

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

eg. 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

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

元数据锁（MDL）：

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁：

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

行级锁包括共享锁、排他锁和更新锁。

共享锁：

共享锁，简称S锁（Shared Lock）。共享锁锁定的资源可以被其它用户读取，但其它用户不能修改它。在SELECT 命令执行时，通常会对对象进行共享锁锁定。通常加共享锁的数据页被读取完毕后，共享锁就会立即被释放。

排他锁：

排他锁，也叫独占锁，简称X锁（Exclusive Lock）。独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL 会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

更新锁：

更新锁是为了防止死锁而设立的。当MySQL准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到MySQL确定要进行更新数据操作时，它会自动将更新锁换为独占锁。但当对象上有其它锁存在时，无法对其作更新锁锁定。

注意：

MySQL InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

两阶段锁协议：

在这里我们举一个例子来阐述行锁（限定存储引擎是InnoDB），有如下的更新语句：

update t set k=k+1 where id=1

这里需要分情况讨论：

如果id这一列上没有索引，那么此时将会使用表级锁，锁定全表，一行一行查找数据。
如果id列上存在索引，那么该事务将会拥有id=1这一行的行锁，只锁定了一行数据。

两阶段锁协议的定义：

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

既然有了两阶段锁协议的存在，那么如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，这样可以在一定程度上减少死锁出现的概率。

死锁：

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。产生死锁的4个必要条件：

互斥条件：一个资源每次只能被一个线程使用
请求与保持条件：一个线程因请求资源而阻塞时，对已获得的资源保持不放
不剥夺条件：进程已经获得的资源，在未使用完之前，不能强行剥夺
循环等待条件：若干线程之间形成一种头尾相接的循环等待资源关系

消除死锁的一个很好的解决办法就是指定获取锁的顺序，举例如下：

比如某个线程只有获得A锁和B锁才能对某资源进行操作，在多线程条件下，如何避免死锁？
获得锁的顺序是一定的，比如规定，只有获得A锁的线程才有资格获取B锁，按顺序获取锁就可以避免死锁

死锁检测：

对于死锁，我们可以通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。

如何解决热点行更新导致的性能问题？

如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

行级锁什么时候会锁住整个表？

InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

为什么查询一行数据也这么慢？

在前面介绍了MySQL中的全局锁，表级锁以及行锁之后，我们来看看为什么仅仅查询一行数据有时候也很慢？如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，不属于我们今天的讨论范围。

假设现在有如下的查询语句：

select * from t where id=1;

执行很慢的原因总结如下：

等 MDL 锁
等表级锁
等行锁
没有使用到索引，走了全表扫描

间隙锁和幻读：

幻读：

MySQL锁机制中还有一种常见的锁叫间隙锁，主要用来解决幻读的问题。所以，我们先来了解下何为幻读。在上一篇文章中，我们给幻读下了如下的定义：

事务A读的时候读出了15条记录，事务B在事务A执行的过程中增加了1条，事务A再读的时候就变成了 16 条，这种情况就叫做幻影读。不可重复读说明了做数据库读操作的时候可能会出现的问题。

我们来举一个例子来详细阐述何为幻读，有如下的建表和插入数据的SQL语句：

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,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这个表除了主键 id 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。我们假设当前的InnoDB 的事务隔离级别是可重复读。那么来分析如下的语句是怎么加锁的：

begin;
select * from t where d=5 for update;
commit;

这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

如果只在 id=5 这一行加锁，而其他行的不加锁的话，会怎么样？来看下边的场景：

可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果：

Q1 只返回 id=5 这一行
在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行
在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读导致的问题：

幻读首先在语义上进行了破坏，然后引起了数据的不一致的问题，即使我们将所有的记录都加上行锁都无法阻止新纪录的插入，也就是幻读的出现。

间隙锁：

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。比如在上边的例子中的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙，如下所示：

当执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

跟行锁有冲突关系的是“另外一个行锁”。但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁的优点：

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题。

间隙锁的缺点：

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

幻读的解决：

前面我们的介绍都假设当前的隔离级别是可重复读。所以，在可重复读的隔离级别下，我们可以通过间隙锁来解决幻读，保证数据的一致性。如果当前的隔离级别是读已提交，那么间隙锁是不存在的，这个时候可以设置binlog_format=row 来解决幻读导致的数据不一致的问题。

总结：

这篇文章中，我们通过全局锁，表级锁，行级锁以及间隙锁来较为详细的阐述了MySQL的锁机制，并且给出了死锁的检测方法和解决办法。在文章的最后，通过分析幻读带来的问题，介绍了如何在不同的隔离级别下解决幻读带来的问题，保证数据的一致性。

接下来，我会继续更新MySQL原理与实践的相关总结与笔记，欢迎大家关注交流，希望对大家的学习有所帮助。

如果对你有帮助，记得点赞哦~欢迎大家关注我的博客，可以进群366533258一起交流学习哦~

本群给大家提供一个学习交流的平台，内设菜鸟Java管理员一枚、精通算法的金牌讲师一枚、Android管理员一枚、蓝牙BlueTooth管理员一枚、Web前端管理一枚以及C#管理一枚。欢迎大家进来交流技术。