InnoDB中锁的问题

路～～～

已于 2023-07-21 19:58:21 修改

阅读量521

点赞数

文章标签：数据库 mysql java

于 2021-03-21 14:22:02 首次发布

本文链接：https://blog.csdn.net/cxs_123/article/details/115014244

版权

首先，我们要明确的一个大前提是：MySQL中的锁是由具体的存储引擎实现的。所以像MyISAM存储引擎和InnoDB存储引擎的锁实现机制是有区别的。(MyISAM存储引擎只支持表级锁，InooDB存储引擎能够支持到行级锁)

但是今天先放下MyISAM存储引擎不谈，主要来说说InnoDB存储引擎的行级。

InnoDB存储引擎支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存。

行级锁(记录锁)

优缺点
优点：由于锁粒度小，争用率低，从而可以提高MySQL的并发新能
缺点：实现复杂，开销大，加锁慢，容易出现死锁。同时也因为其锁定粒度小，发生锁冲突的概率也会增加

实现方式
InnoDB存储引擎的行级锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据行中加锁来实现的。InnoDB存储引擎的这种行级锁实现的特点意味着：只有通过索引条件检索数据，InnoDB存储引擎才会使用行级锁，否则，InnoDB存储引擎将使用表级锁。换句话说，只有使用索引列作为条件列，InnoDB存储引擎才会使用行级锁；若条件列不是索引列，那么InnoDB存储引擎只能使用表级锁。其它注意事项：

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。另外，不论是使用主键索引、唯一键索引、或普通索引，InnoDB存储引擎都会使用行级锁来对数据加锁
即使条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的。如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB存储引擎将使用表锁，而不是行锁。因此，在分析冲突时，别忘记了检查SQL的执行计划，以确认是否真正使用了索引

tip:在实际应用中，要特别注意InnoDB存储引擎行级锁的实现原理，不然的话，可能导致大量的锁冲突，从而影响并发性能

行级锁类型

共享锁(S)：又称为读锁，顾名思义，共享锁就是多个事务对于同一数据可以加共享锁，都能访问到数据，但是只能读不能改。
排它锁(X)：又称为写锁，简称X锁，顾名思义，排它锁就是不能与其它锁并存，如一个事务获取了一个数据行的排它锁，其它事务就不能再获得该行的其它锁，包括共享锁和排它锁，但是获取此数据行排它锁的事务可以对数据进行读取和修改。

意向锁

在InooDB存储引擎的锁定机制中，为了让行级锁和表级锁共存，使用了**意向锁(表级锁定)**的概念，也就有了意向共享锁和意向排它锁两种：

意向共享锁(IS)：表示事务准备给表中的某些数据行加共享锁

意向共享锁：select ... from ... lock in share mode;
--表明事务先给表加IS锁后，才能给表中的某些行加S锁；若是条件列不是索引列，就会给表加表级的S锁

意向排它锁(IX)：表示事务有意向对表中的某些行加排它锁

意向排它锁(IX)：select ... from ... for update;
--表名事务先给表加IX锁后，才能给表中的某些行加X锁；若是条件列不是索引列，就会给表加表级的X锁

TIP：意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享锁/排它锁之前，InnoDB存储引擎会先获得该数据行所在数据表的对应意向锁。

意向锁要解决的问题

我们先来看对意向锁存在意义的描述：

如果另一个事务试图在表A上应用表级别的共享锁或排它锁，则受到由前一个事务在表A上表级别意向锁的阻塞。第二个事务在锁定表A前不必检查各个页或行锁，而只需要检查表上的意向锁。

案例演示

goods表包括id、status、name三个字段，id为主键
 CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `status` enum('1','2') NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

goods表中的数据行如下：
db01 [test]>select * from goods;
+----+--------+------------------+
| id | status | name             |
+----+--------+------------------+
|  1 | 1      | java编程思想     |
|  2 | 2      | 高性能MySQL      |
|  3 | 2      | java并发实战     |
+----+--------+------------------+
3 rows in set (0.00 sec)

事务A获取了goods表中某一行的排它锁，并未提交：
mysql>select * from goods where id=1 for update;  //此语句执行时，会先对goods表加上意向排它锁，又因为id是主键列，前面没有其它锁阻塞的话，就会给id=1这一行数据加上排它锁

事务B想要获取goods表的表锁：
mysql>lock tables goods read; //给goods表加表级别的共享锁。注意：此共享锁和事务A中的意向排它锁是一个级别的；和行级锁中的共享锁不是一个级别的。
因为共享锁和排它锁互斥，所以事务B在对试图对goods表加共享锁的时候，必须保证：
1、当前没有其它事务持有goods表的表级排它锁
2、当前没有其它事务持有goods表中任意一行的排它锁
为了检测是否满足第二条件，事务B必须在确保goods表不存在任何行级排它锁的前提下，去检测表中每一行是否存在排它锁。很明显这是一个效率很差的做法，但是有了意向锁后，情况就不一样了。

意向锁和行级锁的兼容矩阵
注：横向是已经持有的锁，纵向是正在请求的锁
在这里插入图片描述

即意向锁之间是互相兼容的，
虽然意向锁之间是互相兼容的，但是它会与行级排它/共享锁互斥、表级排它/共享锁互斥：

现在我们回到刚才goods表的例子：

事务A获取了某一行的排它锁，并未提交：
mysql>select * from goods where id=1 for update; 
此时goods表存在两把锁：goods表上的意向排它锁和id=1数据行上的行级排它锁。

事务B想要获取goods表的表级共享锁：
mysql>lock tables goods read;
此时事务B检测事务A持有goods表的意向排它锁，就可以得知事务A必然持有该表中某些数据行的行级排它锁，那么事务B对goods表的加锁
请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排它锁。

意向锁的并发性

这就牵扯到我之前多次强调的一件事：意向锁不会与行级锁的共享/排斥锁互斥！！！
正因为如此，意向锁并不会影响到多个事务对不同数据行加排它锁时的并发性（不然我们就直接用普通的表锁就行了）。

最后我们扩展一下上面goods表的例子来概括一下意向锁的作用（一条数据从被锁定到被释放的过程中，可能存在多种不同的锁，但这里我们只着重表现意向锁）。

使用的还是goods表
事务A先获取了某一行的排它锁，并未提交：
mysql>select * from goods where id=1 for update; 
   1、事务A获取了goods表上的意向排它锁
   2、事务A获取了id=1这行数据的排它锁

之后事务B想要获取goods表的表级共享锁：
mysql>lock table goods read;
   1、事务B检测到事务A持有goods表的意向排它锁
   2、事务B对goods表的加锁请求被阻塞

最后事务C也想获取goods表中某一行的排它锁：
mysql>select * from goods where id=2 for update;
    1、事务申请goods表的意向排它锁
    2、事务C检测到事务A吃用goods表的意向排它锁
    3、因为意向锁之间并不互斥，所以事务C获取到了goods表的意向排它锁
    4、因为id=2的数据行上不存在任何排它锁，最终事务C成功获取到了该数据行上的排它锁

InnoDB如何加表锁
在用"lock table"对InnoDB表加锁时要注意，要将"autocommit"设为0，否则MySQL不会给表加锁；事务结束前，不要用"unlock table"释放锁，用为"unlock table"会隐式的提交事务；commit或rollback并不能释放用"lock table"加的表级锁，必须用"unlock table"释放表锁。

mysql>SET AUTOCOMMIT=0;
mysql>LOCK TABLES t1 WRITE, t2 READ, ...;
     .
     .
     .
mysql>COMMIT;
mysql>UNLOCK TABLES;

tip：既然都使用表锁了，你还用啥InooDB存储引擎啊？？？

隐式加锁：

InnoDB存储引擎会自动加意向锁。
对于update、delete和insert语句，InnoDB存储引擎会自动给涉及数据加行级排它锁
对于普通select语句,InooDB存储引擎不会加任何锁

显示加锁：

意向共享锁(IS)：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
意向排它锁(IX)：SELECT * FROM table_name WHERE … FOR UPDATE

间隙锁

前提：间隙锁只有在事务隔离级别为RR中才会产生；若表存在唯一键在RC模式下也存在gap锁。
间隙锁定义
当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB存储引擎会给符合条件的已有数据行的索引项加锁；对于键值在条件范围内但表中并不存在的行，叫做"间隙(GAP)"，InnoDB也会对这个"间隙"加锁，这所锁机制就是所谓的间隙锁。
举例：

假如emp表中只有101条记录，其id字段的值分别时1、2、3...100、101，下面的SQL:
mysql>select * from emp where id>100 for update;

这是一个范围条件的检索，InnoDB存储引擎不仅会对符合条件的id=101的行数据进行加排它锁，也会对id>101的"间隙"加锁，即使这些行数据并不存在。

间隙锁的缺点

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被锁定，从而就会造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对MySQL性能造成很大的伤害
当查询无法利用索引的时候(间隙锁会将范围内数据行的索引项加锁)，InnoDB存储引擎会放弃使用行级锁而改用表级锁的锁定，造成并发性能降低
当查询使用的索引不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该查询的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体键值

产生间隙锁的条件(RR、RC事务隔离级别下)

使用普通索引锁定
使用多列唯一索引
使用唯一索引锁定多行记录
以上情况都会产生间隙锁。

间隙锁的设置是通过"innodb_locks_unsafe_for_binlog"参数来设置的，可通过设置此参数来启用或禁用间隙锁。

唯一索引的间隙锁

测试环境：
环境：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表中存在的隐藏间隙：
1、(-infinity, 1]
2、(1, 5]
3、(5, 7]
4、(7, 11]
5、(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 在 7 - 11 范围的数据并加记录锁 */
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 索引存在的隐藏间隙：
1、(-infinity, 1]
2、(1, 3]
3、(3, 8]
4、(8, 12]
5、(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）的区间里边插入一条数据，所以也被阻塞了。

结论：

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

临键锁（Next-Key Locks）

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

参考文章：https://zhuanlan.zhihu.com/p/48269420
https://blog.csdn.net/zcl_love_wx/article/details/81983267
https://blog.csdn.net/xmtblog/article/details/105548629

路～～～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
InnoDB中锁的问题

首先，我们要明确的一个大前提是：MySQL中的锁是由具体的存储引擎实现的。所以像MyISAM存储引擎和InnoDB存储引擎的锁实现机制是有区别的。(MyISAM存储引擎只支持表级锁，InooDB存储引擎能够支持到行级锁)但是今天先放下MyISAM存储引擎不谈，主要来说说InnoDB存储引擎的行级。InnoDB存储引擎支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存。行级锁(记录锁)优缺点优点：由于锁粒度小，争用率低，从而可以提高MySQL的并发新能
复制链接

扫一扫