mysql锁机制、MVCC 与 thinkphp的lock方法记录

最新推荐文章于 2024-04-08 16:27:28 发布

CoderAaron

最新推荐文章于 2024-04-08 16:27:28 发布

阅读量465

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

原文链接：https://blog.csdn.net/S_ZaiJiangHu/article/details/116720884

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

锁的引入

如果A有100元，同时对B、C转账，若处理是同时的，则此时同时读取A的余额为100元，在对两人转账后写回，A的余额不是0元而是50元。因此，为了防止这种现象的出现，要引入锁的概念，如只有在A对B的转账完成后，才可对C转账。

锁机制用于管理对共享资源的并发访问。

锁的基本类型

悲观锁和乐观锁

悲观锁（X Lock），正如其名，具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度。因此，在当前数据没有处理完之前，其他人或应用不能读取和操作数据！
乐观锁(S Lock)：乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。

意向锁

意向排他锁
事务想要获得一张表中某几行的排他锁
意向共享锁（IS Lock）
事务想要获得一张表中某几行的共享锁

意向锁是什么呢？我们好像从来没有听过，也从来没有使用过，其实他们是由数据库自己维护的。

也就是说，当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

反过来说：如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

那么这两个表级别的锁存在的意义是什么呢？

第一个，我们有了表级别的锁，在 InnoDB 里面就可以支持更多粒度的锁。它的第二个作用，我们想一下，如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么？是不是必须先要去判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低

但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以 InnoDB 里面的表锁，我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯，是用来提高加锁的效率的。

悲观锁又可分为——排他锁和共享锁

PS ：
操作的单位为线程
操作流程为先去尝试上锁，再操作数据。而非先操作数据，判断结果结果，再去上锁

悲观锁又可分为：

共享锁【shared locks】简称S锁。又称读锁，若事务T（线程A）对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改（即不能上排他锁）。
排他锁【exclusive locks】简称X锁。又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

需要注意的是，排他锁的真实含义不是排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，而是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

锁的算法（或者说粒度）

Mysql的锁语句

不同 SQL 语句对加锁的影响，不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况：

SELECT ... 语句正常情况下为快照读，不加锁；
SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；
常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚。

其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。

更多场景请看https://zhuanlan.zhihu.com/p/144347237

行级锁与表级锁

顾名思义，不详细解释了。需要注意的是：

表锁一般是 DDL 处理时使用。

DDL是数据定于语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改等操作的语言。它和DML语言的最大区别是DML只是对表内部数据操作，而不涉及表的定义，结构的修改，更不会涉及其他对象。DDL语句更多的由数据库管理员（DBA）使用，开发人员一般很少使用。如 CREATE DATABASE dbname;

表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。
默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁（隐式），而select语句默认不会加任何锁类型！！！加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过SELECT FROM查询数据，因为普通查询没有任何锁机制！！！！！！(这下可以理解之前介绍排他锁的时候说的话了把)
InnoDB默认是行级别的锁，当SELECT使用了FOR UPDATE语句且有明确指定的主键时候，是行级锁。否则是表级别。
注意，显式的加锁，即for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

例子: 假设表foods ，存在有id跟name、status三个字段，id是主键，status有索引。

例1: 明确指定主键(id)，并且有此记录(数据库中有对应的数据可以查询到)，加锁是行级锁
SELECT * FROM foods WHERE id=1 FOR UPDATE; 
SELECT * FROM foods WHERE id=1 and state=’待发送’ FOR UPDATE;

例2: 明确指定主键/索引，若查无此记录（没有id=-1的数据），无锁（又说会变成间隔锁？）
SELECT * FROM foods WHERE id=-1 FOR UPDATE;

例3: 无主键/索引，变为表级锁
SELECT * FROM foods WHERE state=’待发送’ FOR UPDATE;

例4: 主键/索引不明确（在查询之前不能明确的指定是哪一行），变为表级锁
SELECT * FROM foods WHERE id<>’3’ FOR UPDATE; 
SELECT * FROM foods WHERE id LIKE ‘3’ FOR UPDATE;

显性表锁

表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。

mysql> lock table user read(write); # 分为读锁和写锁
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 100; # 成功
mysql> select * from role where id = 100; # 失败，未提前获取该 role的读表锁
mysql> update user  set name = 'Tom' where id = 100; # 失败，未提前获得user的写表锁

mysql> unlock tables; # 显示释放表锁
Query OK, 0 rows affected (0.00 sec)

表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁。

除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

行锁(Record Lock 也叫记录锁)详解

不同存储引擎的行锁实现不同，后续没有特别说明，则行锁特指 InnoDB 实现的行锁。

行锁是锁定一个记录上的索引，而不是记录本身。

在了解 InnoDB 的加锁原理前，需要对其存储结构有一定的了解。InnoDB 是聚簇索引，也就是 B+树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值，所以通过二级索引查询数据时，还需要拿对应的主键去聚簇索引中再次进行查询。关于 InnoDB 和 MyISAM 的索引的详细知识可以阅读《Mysql探索(一):B+Tree索引》一文。

在这里插入图片描述
下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；
第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

注意：

因为覆盖索引优化，lock in share mode也就是S锁，只锁覆盖索引，不锁主键索引。
for update也就是排他锁，还会给主键索引上加锁，因为系统以为你要修改数据。

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景

update user set age = 10 where id > 49;#注意是update语句

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。
在这里插入图片描述

间隔锁（Gap Lock）

上面分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，如果把隔离级别设置为读提交的话，就没有间隙锁了。同时，要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为ROW。

间隔锁就是对(x,y)区间加锁。

间隔锁，为了解决幻读，比如可重复读级别下，你有记录1,4，无2,3，你select不到2，但是其他事务插入了2，你下次select for update当前读的时候就看到了2，这就是幻读，但是2无记录，你没法针对一行不存在的记录加锁，所以引入间隙锁，锁住间隙，让其他事务无法插入2

本章需要看后码锁才能理解，建议直接看后码锁那

Gap Lock是全开区间，Next-Key Lock是左开右闭区间

间隔锁一般发生在范围查询上

a	b
1	a1
3	a3
5	a5
7	a7
9	a9

如查询 a>1 and a<9 加锁的话，那么此时插入2 4 6 8 是插不进去的

SELECT a FROM t WHERE a BETWEEN 1 and 9 FOR UPDATE;

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，
其它事务就不能在 t.c 中插入 15。
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

对于“记录锁、间隙锁、临键锁，都属于排它锁“的总结，应该不对的，我补充一下（结论来自自测）。比如事务A中我用了lock in share mode锁了一行，这个时候在事务B中仍然可以用lock in share mode来获取读锁。所以record lock既可以是共享锁，也可以是排它锁。至于间隙锁gap lock，仅对insert起作用，官网原话是only purpose is to prevent other transactions from inserting to the gap，翻译一下的话就是唯一的目的是防止其他事务往这个间隙里插入值。其他的操作我测试了下，update/delete/select … for update/ select … lock in share mode/…都能run，不过因为gap本身对应着不存在的数据行，能run也没意义。最后说下临键锁next-key lock，是间隙锁+记录锁的组合，所以也不能定性为排它锁。finally, take care of your hair should be the most important thing, but not several useless locks.

好了本节后面的还是直接看后码锁那吧！

产生间隙锁的条件（RR事务隔离级别下；）：

使用唯一索引锁定多行记录。
使用多列唯一索引；
使用普通索引锁定；(Notice!此处不是范围也会，后面会具体解释)

以上情况，都会产生间隙锁，下面是小编看了官方文档理解的：

对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：

sql SELECT * FROM child WHERE id = 100 FOR UPDATE;

这条语句，就只会产生记录锁，不会产生间隙锁。

对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：
sql SELECT * FROM child WHERE id = 100 FOR UPDATE;
这条语句，就只会产生记录锁，不会产生间隙锁。

下面三节转自https://zhuanlan.zhihu.com/p/48269420

打开间隔锁

打开间隙锁设置
首先查看 innodb_locks_unsafe_for_binlog 是否禁用：

show variables like 'innodb_locks_unsafe_for_binlog';

查看结果：
innodb_locks_unsafe_for_binlog：默认值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini）重新启动才行。

在 my.cnf 里面的[mysqld]添加

[mysqld]
innodb_locks_unsafe_for_binlog = 1

唯一索引的间隙锁

测试环境：

环境：MySQL，InnoDB，默认的隔离级别（RR）

数据表：

CREATE TABLE `test` (
  `id` int(1) 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表中存在的隐藏间隙：

(-infinity, 1)
(1, 5)
5 #这个确定的5不是间隙 只是这里为了方便显示出来
(5, 7)
7
(7, 11)
11
(11, +infinity)

我们现在进行以下几个事务的测试：

/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行
/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;

上诉的案例，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。

我们继续在 id 唯一索引列上做以下的测试：

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

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

从上面我们可以看到，(5, 7)、(7, 11) 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以我们可以得出结论：当我们给 (5, 7)这个区间加锁的时候，会锁住 (5, 7)、(7, 11)这两个区间。

我们再来测试如果我们锁住不存在的数据时，会怎样：

/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
结论

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

普通索引的间隙锁

数据准备：创建 test1 表：

# 注意：number 不是唯一值
CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在这张表上，我们有 id number 这两个字段，id 是我们的主键，我们在 number 上，建立了一个普通索引，为了方便我们后面的测试。现在我们要先加一些数据：

INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

在进行测试之前，我们先来看看test1表中 number 索引存在的隐藏间隙：

(-infinity, 1)
1
(1, 3)
3
(3, 8)
8
(8, 12)
12
(12, +infinity]

案例说明:我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */
BEGIN;
/* 查询 number = 3 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行
/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞 注意！
/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞
/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞
/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行
/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行
/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行
/* 提交事务1 */
COMMIT;

我们会发现有些语句可以正常执行，有些语句被阻塞了。
这里可以看到，number (1 ，- 8) 的间隙中，插入语句都被阻塞了，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。我们再进行以下的测试，方便我们更好的理解间隙锁的区域（我们要将数据还原成原来的那样）：

/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

/* 事务1插入一条 id = 2， number = 1 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); # 阻塞

/* 事务2插入一条 id = 3， number = 2 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); # 阻塞

/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); # 阻塞

/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); # 正常执行

/* 事务5插入一条 id = 9， number = 9 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); # 正常执行

/* 事务6插入一条 id = 10， number = 12 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); # 正常执行

/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞

/* 提交事务1 */
COMMIT;

这里有一个奇怪的现象：
事务3添加 id = 6，number = 8 的数据，给阻塞了；
事务4添加 id = 8，number = 8 的数据，正常执行了。
事务7将 id = 11，number = 12 的数据修改为 id = 11， number = 5的操作，给阻塞了；

这是为什么呢？我们来看看下边的图，大家就明白了。
在这里插入图片描述
从图中可以看出，当 number 相同时，会根据主键 id 来排序，所以：

事务3添加的 id = 6，number = 8，这条数据是在（3, 8）的区间里边，所以会被阻塞；
事务4添加的 id = 8，number = 8，这条数据则是在（8, 12）区间里边，所以不会被阻塞；
事务7的修改语句相当于在（3, 8）的区间里边插入一条数据，所以也被阻塞了。

结论：

在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

后码锁（临键锁Next-Key Lock）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

Next-Key Lock它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。
在这里插入图片描述

转自 https://blog.csdn.net/qq_40008535/article/details/110941911

next-key lock加锁规则：

原则一：加锁的基本单位都为next-key lock且范围为加锁区间最近两个端点的前开后闭（]，即使是行锁，也是先加后码锁，再变成行锁。
原则二：查找过程中只有访问到的对象才会加锁，例如走全表扫描的情况，这种在扫描前就会给全表加上next-key lock。
优化一：索引上的等值查询，在给唯一索引加锁时，next-key lock会退化为行锁，因为主键是唯一的。
优化二：索引上的等值查询（没有说是唯一索引），继续向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁(都是开区间)。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例

以下案例以下面数据为例：

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);

案例一：等值查询间隙锁

在这里插入图片描述

对sessionA加锁，因为id=7的行不存在，根据原则一，Session加锁的范围首先为id主键索引上的(5,10]，然后根据优化二，继续向右访问，id=10不满足条件，范围为(5,10]的next-key lock退化为间隙锁(5,10)。
所以sessionB失败，sessionC尝试加锁，先(5,10]【注意，不是(10,15]，因为10本身包含了更近！】,然后退化为行锁10，加锁成功，执行成功。

案例二：非唯一索引等值共享锁

在这里插入图片描述

注意点：回忆我们之前学到的，因为覆盖索引优化，lock in share mode只锁覆盖索引，不锁主键索引。

答案：

所需要的信息我们在普通索引C上就可以找到，利用覆盖索引优化，不会进行回表，根据原则一，Session A加锁范围为普通索引C上的(0,5]。
根据原则二，Session B的update sql可以执行，因为主键索引表上没有加锁（因为这里是共享锁）
对sessionC，首先是(5,10]根据优化二，继续向右寻找，id = 10不满足条件，范围为(5,10]的next-key lock退化为间隙锁(5,10)。加锁失败。

案例三：主键索引范围锁

Session A 先找到id=10这一行，根据优化一，原本(5,10]的next-key lock退化为id=10的行锁。然后对于10<id<11，唯一索引上的范围查找需要向后查找，找到id=15这一行，加锁next-key lock(10,15]。二者结合，最终锁的范围是[10,15]

这样后面的语句就明了了，这里有个注意点：找id=10这一行的时候是等值查询，id=15这一行的时候是范围查询判断。

案例四：非唯一索引范围锁

在这里插入图片描述
找到c=10这一行，加上(5,10]的next-key lock，由于不是非唯一索引，不退化为行锁。继续向前寻找，找到c=15这一行，加(10,15]的next-key lock。最终是（5，15]的锁

案例五：唯一索引范围锁 bug

在这里插入图片描述

首先找到id=15这一行，由于是等值查询，所以加上(10,15]的next-key lock。
但是这里毕竟是个范围，不是标准意义的等值，由于InnoDB 会往前扫描到第一个不满足条件的行为止，也就是id=20这一行，也是范围查找，加上(15,20]的next-key lock。

案例六：非唯一索引上存在"等值"的例子

案例建立在插入如下sql：

insert into t values(30,10,30);

索引C的示意图如下：
在这里插入图片描述
模拟场景：

delete语句的加锁逻辑与select…for update是类似的。

案例七：limit 语句加锁

在这里插入图片描述
情况与案例七类似，不过加上limit 2，查询到两条数据之后就不会再查询，也就是不会查找到c=15这一行，示图如下：

所以在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

在这里插入图片描述
Session A在执行select语句时加(5,10]的next-key lock和(10,15)的间隙锁（？这里用了之前的数据？）
Session B在执行update语句时，先加(5,10)的间隙锁，再申请写锁的时候阻塞。
Session A在插入语句时发生死锁现象。
分析情况时用next-key lock，申请锁时还是分成间隙锁和行锁两段执行的。

总结：

加锁的基本单位为next-key lock = 间隙锁+行锁，范围为前开后闭，但加锁过程为先申请间隙锁在申请行锁。
只有访问到的对象才可以加锁。
等值查询：①主键索引：next-key lock会退化为行锁；②非主键索引：保持原样
等值查询：InnoDB会向右继续遍历，找到第一个不符合的数据行，加next-key lock并退化为间隙锁。
范围查询：先找到复核条件的一行数据，再向右查找，向右查找时加的锁不会退化为间隙锁。
无论什么情况下，InnoDB 会往前扫描到第一个不满足条件的行为止。

死锁

锁的释放与阻塞

回顾：锁什么时候释放？事务结束（commit，rollback）；客户端连接断开。

如果一个事务一直未释放锁，其他事务会被阻塞多久？会不会永远等待下去？如果是，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL 有一个参数来控制获取锁的等待时间，默认是 50 秒。

show VARIABLES like 'innodb_lock_wait_timeout';

对于死锁，是无论等多久都不能获取到锁的，这种情况，也需要等待 50 秒钟吗？那不是白白浪费了 50 秒钟的时间吗？我们先来看一下什么时候会发生死锁。

死锁的发生和检测

在这里插入图片描述
在第一个事务中，检测到了死锁，马上退出了，第二个事务获得了锁，不需要等待 50 秒：

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

为什么可以直接检测到呢？是因为死锁的发生需要满足一定的条件，所以在发生死锁时，InnoDB 一般都能通过算法（wait-for graph）自动检测到。

那么死锁需要满足什么条件？死锁的产生条件：

因为锁本身是互斥的，
（1）同一时刻只能有一个事务持有这把锁，
（2）其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺，
（3）当多个事务形成等待环路的时候，即发生死锁。

如果锁一直没有释放，就有可能造成大量阻塞或者发生死锁，造成系统吞吐量下降，这时候就要查看是哪些事务持有了锁。

查看锁信息（日志）

SHOW STATUS 命令中，包括了一些行锁的信息：

show status like ‘innodb_row_lock_%’;

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time ：从系统启动到现在锁定的总时间长度，单位 ms；

Innodb_row_lock_time_avg ：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；

Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

SHOW 命令是一个概要信息。InnoDB 还提供了三张表来分析事务与锁的情况：

select * from information_schema.INNODB_TRX; -- 当前运行的所有事务 ，还有具体的语句
select * from information_schema.INNODB_LOCKS; -- 当前出现的锁
select * from information_schema.INNODB_LOCK_WAITS; -- 锁等待的对应关系

死锁的避免

1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）；

2、批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）；

3、申请足够级别的锁，如果要操作数据，就申请排它锁；

4、尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表；

5、如果可以，大事务化成小事务；

6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

注意：锁必须要在事务中使用

锁和事务是天然相关的，因为一般来说，我们锁住表的目的是为了更改其数据！因而，它们要在事务中才能使用。
注意，在这里可能会产生混淆，认为事务具有ACID性质中的隔离性，从而认为会自动加锁。事实上，事务的隔离性可以分为4种类型的隔离级别（具体看另一篇博客）。对不同的隔离级别，加锁分为手动与自动。
比如，当数据库的隔离级别为2 Read Committed时（大多数数据库的默认级别），为了实现可重复读，仍然需要我们进行手动加锁！

自增锁探究https://zhuanlan.zhihu.com/p/48207652

ThinkPhp的锁机制

在数据库链式操作中，使用lock(true)来加锁。

list=$model->lock(true)->where(['id'=>1])->find();
//id为1在更新时，select id=1 会等待。把ID改为2时，不等待

在thinkphp中，似乎不需要1、在事务中才能用lock。2、在插入、更新操作中也可使用lock。
这可能是框架自动帮我们处理了。

在这里插入图片描述

注1：MYSQL会每条sql语句自动提交所以测试前要单独设置下
注2：Mysql锁住的是索引！

为什么表里面没有索引的时候，实验一锁住一行数据会导致锁表

问题一：为什么表里面没有索引的时候，实验一锁住一行数据会导致锁表？或者说，如果锁住的是索引，一张表没有索引怎么办？所以，一张表有没有可能没有索引？

如果我们定义了主键(PRIMARYKEY)，那么 InnoDB 会选择主键作为聚集索引
如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增
所以，实验一为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

在辅助索引里面，索引存储的是二级索引和主键的值。比如name=4，存储的是name的索引和主键id的值4。而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。
————————————————
原文链接：https://blog.csdn.net/S_ZaiJiangHu/article/details/116720884

CoderAaron

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql锁机制、MVCC 与 thinkphp的lock方法记录

加锁的基本单位为next-key lock = 间隙锁+行锁，范围为前开后闭，但加锁过程为先申请间隙锁在申请行锁。只有访问到的对象才可以加锁。等值查询：①主键索引：next-key lock会退化为行锁；②非主键索引：保持原样等值查询：InnoDB会向右继续遍历，找到第一个不符合的数据行，加next-key lock并退化为间隙锁。范围查询：先找到复核条件的一行数据，再向右查找，向右查找时加的锁不会退化为间隙锁。无论什么情况下，InnoDB 会往前扫描到第一个不满足条件的行为止。
复制链接

扫一扫