【MySQL锁机制】3-MySQL中锁机制的深入学习

最新推荐文章于 2024-04-20 09:00:00 发布

风暴计划

最新推荐文章于 2024-04-20 09:00:00 发布

阅读量220

点赞数

分类专栏： MySQL+Redis基础知识文章标签： mysql 索引 MySQL中锁的分类 MySQL中的意向锁 MySQL中的临键锁

本文链接：https://blog.csdn.net/cmm0401/article/details/115860762

版权

MySQL+Redis基础知识专栏收录该内容

36 篇文章 6 订阅

订阅专栏

2.2.1 元数据锁（Metadata Lock，MDL锁）

4.1.2 意向共享锁（IS锁，是表级的锁）

4.1.3 意向排它锁（IX锁，是表级的锁）

4.2 MySQL 行锁的算法

4.2.1 记录锁：record lock

4.2.2 间隙锁：Gap lock

4.3.3 临键锁：next-key lock

99、参考

0、MySQL中加锁的目的与分类

0.1 MySQL中加锁的目的

数据库是一个【面向多用户共同使用】的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制，就有可能会读取和存储不正确的数据，破坏数据库的一致性。

数据库中的锁是用于管理对公共资源的并发控制。也就是说，在并发情况下，会出现共享资源的竞争问题，所以需要加锁来进行控制。加锁解决了【多用户环境下保证数据库的完整性和一致性】的问题。使用锁的对象是事务，事务使用锁来锁定数据库中的对象，这些对象可以是【表、页、行】，并且，一般锁定的对象仅在事务 commit 或 rollback 后进行释放（不同的事务隔离级别释放的时间可能存在差异）。

0.2 MySQL中锁的分类

1、加锁机制：乐观锁和悲观锁的思想

当程序中可能出现并发情况时，就需要保证在并发情况下数据的准确性，以此来确保当前用户和其他用户同时操作同一数据时，所得到的结果和他自己单独操作时的结果是一样的。这种手段就叫做并发控制（Concurrency Control）。并发控制的目的是：保证一个用户的工作不会对另一个用户的工作产生不合理的影响。没有做好并发控制，就有可能会导致脏读、幻读和不可重复度等问题。

常说的并发控制，一般都和数据库管理系统（DBMS）有关。在 DBMS 中的并发控制的任务：是确保在多个事务同时存取数据库中同一数据时，不破坏事务的隔离性、一致性和数据库的统一性。实现并发控制的主要手段，大致可以分为 乐观并发控制和悲观并发控制 两种。

首先要明确的是：无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实，不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像 tair数据存储结构等都有类似的概念。所以，不应该拿乐观锁、悲观锁和其他的数据库锁等进行对比。乐观锁比较适用于读多写少的情况（多读场景）。悲观锁比较适用于写多读少的情况（多写场景）。

MySQL中的加锁机制，请详读博客：https://blog.csdn.net/cmm0401/article/details/115816459

2、锁粒度

2.1 行锁

行锁，是MySQL中锁定粒度最小的一种锁，表示只针对当前操作的行记录进行加锁，它能够大大减少数据库操作的锁冲突。

行锁，它的加锁粒度是最小的，但是，加锁的开销却是最大的。

对于行锁，有可能会出现死锁的情况，出现死锁的解决办法就是：必须有一方事务回滚或者同时回滚。另外，行锁按照使用方式分为 共享锁（S锁）和排它锁（X锁）。

2.2 表锁

表锁，是MySQL中锁定粒度最大的一种锁，表示当前的操作对整张表进行加锁，加表锁的资源开销比行锁少，也不会出现死锁的情况，但是，发生锁冲突的概率却很大。

MySQL中的大部分存储引擎都支持表锁，MyISAM 和 InnoDB 都支持表锁，但是，InnoDB存储引擎默认使用的是行锁。

MySQL的表锁分为两类：元数据锁（Metadata Lock，MDL锁）、表锁。

2.2.1 元数据锁（Metadata Lock，MDL锁）

元数据锁（MDL锁）不需要显式使用，在访问一个表的时候会被MySQL引擎自动加上。

这个特性需要MySQL 5.5 版本以上才会支持：当对一个表做增删改查的时候，该表会被加上 MDL读锁；当对表做结构变更的时候，该表会被加上 MDL写锁。

2.2.2 MDL锁的规则

（1）MDL读锁与MDL读锁之间不互斥。

（2）MDL写锁与MDL读锁、MDL写锁与MDL写锁之间是互斥的。为了保证表结构变更的安全性，所以，如果要多线程对同一个表加字段等表结构操作，就会变成串行化，需要进行锁等待。

（3）MDL写锁的优先级比 MDL读锁的优先级要高。

（4）MDL锁释放必须要等到事务结束后才会释放。

2.2.3 MDL锁的举例

如果没有 MDL锁的保护，则事务2 可以直接执行DDL操作，并且导致事务1出错。MySQL 5.1 版本即是如此。在MySQL 5.5 版本中加入了MDL锁，目的就在于避免这种情况的发生。由于事务1开启了查询（增删改查都会触发MDL锁的加上），那么获得了MDL锁，锁的模式为 SHARED_READ（读锁），事务2要执行DDL，则需获得EXCLUSIVE锁（写锁），两者互斥，所以事务2 需要等待事务1 的执行结束后才有可能执行。

2.3 页锁

页锁，是MySQL中锁定粒度介于行锁和表锁中间的一种锁。

表锁的加锁速度快，但发生的锁冲突较多；行锁发生的锁冲突少，但是行锁的加锁速度较慢。所以，取了折衷的方案页锁，页锁的含义是：一次锁定相邻的一组记录。

3、兼容性

3.1 共享锁（读锁、S锁）

共享锁，也叫做读锁、S锁：其他事务可以读数据，但是不能写数据。共享锁允许一个事务去读一行或者多行数据，并且阻止其他事务获得相同数据集的排它锁。

共享锁举例：若事务T 对数据A 加上S锁，则事务T 可以读取A 但是不能修改A，其他事务只能再对A 加 S锁而不能加 X锁，直到事务T 释放A 上的S锁为止。这保证了其他事务可以读取A，但是在事务T 释放A 上的S锁之前不能对数据A 做任何修改。

显示加上共享锁的SQL语句：

select ... lock in share mode;

3.2 排它锁（写锁、X锁）

排它锁，也叫做写锁、X锁：其他事务不能读数据，也不能写数据。允许获得排它锁的事务去读取数据并更新数据，并且阻止其他事务取得相同数据集的共享读锁和排它写锁。

排它锁举例：若事务T 对数据A 加上X锁，则事务T 可以读A 也可以修改A，其他事务不能再对A 加任何锁，直到事务T 释放A 上的锁为止。这保证了其他事务在事务T 释放A 上的锁之前不能再读取和修改数据A，从而保证了数据的一致性。

显示加上排它锁的SQL语句：

select ... for update;

4、锁模式

4.1 MySQL 意向锁的分类

在 MySQL 的 InnoDB 存储引擎中，意向锁，intention locks，可以分为意向共享锁（IS）和意向排它锁（IX），依次表示接下来的一个事务将会获得共享锁还是排它锁。

意向锁，不需要用户显示获取，它是用户在获取共享锁或者排它锁的时候 MySQL本身自动获取的。这也就是说，如果要获取共享锁或者排它锁，则一定是先获取到了意向共享锁或者意向排它锁。

意向锁不会锁住任何东西，除非有进行全表请求的操作，否则，它不会锁住任何数据。意向锁存在的意义：只是用来表示有事务正在锁某一行数据，或者将要锁某一行数据。

原文：Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

4.1.1 意向锁的作用：可参考此博客

前面介绍了MySQL中的意向锁的概念和分类，那么，在MySQL中，为什么需要意向锁呢？

（1）首先，一个事务成功的给一张表加上表锁的前提是：没有任何其他事务已经锁定了该表的任意一行数据。

（2）那么，怎么知道 “ 没有任何其他事务已经锁定了该表的任意一行数据 ” 呢？这就需要对全表扫描去做检测，非常费时。

（3）所以，使用意向锁的目的就是提高加表锁的效率，也可以把意向锁当做是一个表中存在不存在一些记录被其他事务锁上的标识。这也就是说，直接看这张表有无意向锁，如果没有，则当前事务可以加表锁；如果有，则说明该表中的一些记录已经被其他事务加上了锁（不管是行锁还是表锁），当前事务要想加表锁只能够等待。

=============== 换一种说法来解释意向锁 ===============

在MySQL中，意向锁的主要作用是处理行锁和表锁之间的矛盾，意向锁能够显示 “某个事务正在表中的某一些行上持有了锁，或者准备去持有某一些行上的锁”。当我们需要加一个排它锁时，需要根据意向锁去判断表中有没有数据行被锁定。

问题引入：事务A 想要在一个表上加S锁，如果表中的一行已经被事务B 加了X锁，那么该锁的申请也应该被阻塞。如果表中的数据非常多，那么，逐行检查锁标志的开销将会非常大，系统的性能将会受到影响。

问题解决：为了解决这个问题，可以在表级别上引入新的锁类型来表示表中行记录的加锁情况，这就引出了 “意向锁” 的概念。

意向锁举例：举个例子，如果表中有记录1千万条，事务A 把其中的几条记录已经上了行锁，这时，事务B 想要给这个表加表锁，如果没有意向锁的话，那么就需要去表中查找这1千万条记录中是否存在一些记录被上锁了，由此可看出，逐行检查锁标志的开销将会非常大，系统性能将会受到严重影响。但是，如果存在意向锁的话，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B 先检查该表上是否存在意向锁、存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则事务B 需要等待直到事务Ａ释放相关的锁，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁住了，它只要知道反正有一行记录被其他事务锁住了就行。这就是为什么在MySQL中需要意向锁的原因。

4.1.2 意向共享锁（IS锁，是表级的锁）

意向共享锁（IS锁）的作用是表明事务想要获得一张表中某几行记录的共享锁。

事务打算给表中的一些记录加上共享锁，那么，就必须得先取得该表的意向共享锁。

4.1.3 意向排它锁（IX锁，是表级的锁）

意向排它锁（IX锁）的作用是表明事务想要获得一张表中某几行记录的排它锁。

事务打算给表中的一些记录加上排它锁，那么，就必须得先取得该表的意向排它锁。

4.2 MySQL 行锁的算法

4.2.1 记录锁：record lock

记录锁，锁的是索引记录，而非记录行本身。如果InnoDB表中没有索引，那么，会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。当一条SQL语句没有走任何索引时，那么，将会在每一条聚集索引后面加 X锁，这个类似于表锁，但原理上和表锁是完全不同的。

记录锁的条件：命中单行记录并且命中的条件字段是唯一索引或者主键索引。

update user_info set name=’张三’ where id=1;
// 这里的id是唯一索引，使用到了 Record Lock

记录锁，总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB存储引擎会使用隐式的主键rowId 来进行锁定。

4.2.2 间隙锁：Gap lock

间隙锁，也称之为 Gap锁，是锁住索引记录中的间隙（左开右闭原则）、或者锁住第一条索引记录之前的范围、又或者锁住最后一条索引记录之后的范围。

产生间隙的条件：（1）使用普通索引锁定。（2）使用多列唯一索引锁定。（3）使用唯一索引锁定多行记录。

我们先理解什么是间隙，如下所示：

CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

在test表中，有如上4条记录【1、5、7、11】，则上面数据的间隙就是：(-infinity, 1]、(1, 5]、(5, 7]、(7, 11]、(11, +infinity] 。在test表中使用间隙锁的话，锁的就是上面的范围。

基于test表，举例如下：

【测试1】使用范围查询，由此SQL产生的间隙锁范围是：会锁住 (1, 5]、 (5, 7]、(7, 11] 这三个区间。

BEGIN;
/* 查询 id 在 5 - 7 范围的数据并加排它锁 */
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

【测试2】使用等值查询，由于 id=3 的记录并不存在，所以也没有单行记录可言，但是也会产生间隙锁，锁定范围是(1,3]、(3,5]。

BEGIN;
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
SELECT SLEEP(30);

【测试3】使用范围查询，会产生间隙锁，锁定范围是 (5,+infinity]。

BEGIN;
SELECT * FROM `test` WHERE `id` > 5 FOR UPDATE;
SELECT SLEEP(30);

理解了间隙，我们看看下面的例子：

mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
|  1 | 伊利   |    68 |   1 |
|  2 | 蒙牛   |    88 |   1 |
|  6 | tom    |  2788 |   3 |
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+
其中：id为主键，num为普通索引。

窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+
1 row in set

窗口B：
mysql> insert into product_copy values(5,'kris',1888, 2);
这里会等待，直到窗口A commit后才会显示下面结果。
Query OK, 1 row affected

但是，下面是不需要等待的。
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0

mysql> insert into product_copy values(5,'kris',1888, 5);
Query OK, 1 row affected

通过上面的例子可以看出：Gap锁的作用是在的范围是（1,3] U [3,4)。

但是要记住：使用【主键索引或唯一索引】条件的单行记录不会使用间隙锁，而是会使用记录锁；但是，如果查询出来的是多行记录，使用的就是间隙锁。

下面的例子，id 是主键索引，使用的就是记录锁。

窗口A：使用【主键索引或唯一索引】条件的单行记录不会使用间隙锁，而是会使用记录锁。
mysql> select * from product_copy where id=6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+

窗口B：并不会发生等待
mysql> insert into product_copy values(5,'kris',1888,3);
Query OK, 1 row affected

窗口A：使用【主键索引或唯一索引】条件的多行记录，使用的就是间隙锁。
mysql> select * from product_copy where id>6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
| 10 | 优衣库 |   488 |   4 |
+----+------+-------+-----+

窗口B：会发生等待
mysql> insert into product_copy values(9,'kris',1888,3);
Query OK, 1 row affected

从上面的例子可以看到：第一条SQL是使用了主键索引的单行记录，使用了记录锁。第二个SQL即使使用了主键索引，但是查询出来的数据多于一条，所以使用的是间隙锁，间隙锁的锁定范围是 (6,+infinity]。

4.3.3 临键锁：next-key lock

临键锁，是记录锁与间隙锁的组合，它的封锁范围是：既锁住记录本身，还锁住索引记录之间的间隙。

注：临键锁的主要目的，也是为了避免幻读（Phantom Read）。临键锁适用的事务隔离级别是RR及以上；如果把事务的隔离级别降级为RC，则临键锁也会失效。

99、参考

（1）MySQL：MySQL锁机制1：https://blog.csdn.net/cmm0401/article/details/107299458

（2）https://blog.csdn.net/cmm0401/article/details/115857079

（3）https://blog.csdn.net/cmm0401/article/details/115655095

（4）https://www.jianshu.com/p/615f3c7fbe6f

（5）https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc

（6）https://www.jianshu.com/p/f7142e39f455

（7）好文分享：

https://blog.csdn.net/qq_38238296/article/details/88362999

https://www.cnblogs.com/zhoulujun/p/11710318.html

https://zhuanlan.zhihu.com/p/48269420

https://segmentfault.com/a/1190000014133576

（8）

风暴计划

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【MySQL锁机制】3-MySQL中锁机制的深入学习

0、MySQL中加锁的目的与分类0.1 MySQL中加锁的目的数据库是一个【面向多用户共同使用】的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制，就有可能会读取和存储不正确的数据，破坏数据库的一致性。数据库中的锁是用于管理对公共资源的并发控制。也就是说，在并发情况下，会出现共享资源的竞争问题，所以需要加锁来进行控制。加锁解决了【多用户环境下保证数据库的完整性和一致性】的问题。使用锁的对象是事务，事务使用锁来锁定数据库中的对...
复制链接

扫一扫