MySql 加意向锁_MySQL 行锁 意向锁 间隙锁

MySQL 行锁 意向锁 间隙锁

一、锁的分类

共享锁：反正我就理解成读锁一个意思，事务A对某些数据加了共享锁，允许其他事务同时获取这些数据共享锁，但是不可以在这些数据上加排它锁。

排它锁：理解成写锁吧，事务A对某些数据加了排它锁，那么其他事务不再允许加共享锁或排它锁。

理解：这样讲吧~事务A读取了一条记录，事务B也可以读取这条记录，但是他不能做修改(也就是不能做写操作)。事务A修改了一条记录，事务B不能读不能写这条记录。

读锁:事务A对某些记录加了读锁，那么其他事务也可以加读锁读取这些数据，但是不能对其进行写操作(可以理解为不能修改了)

写锁：事务A对某些记录加了写锁(一般做一些修改之类的)，那么其他事务不可以再对这些数据进行写操作和读操作了

行锁：也就是以行为单位的锁啦。也分行读锁、行写锁。

表锁：表级锁呀~一锁就锁一整张表

意向锁：表级锁。分为意向共享锁和意向排他锁

**为啥要有意向锁呢？**我基于innodb引擎哈~它的作用，咱们可以通过下面一个例子来分析：

innodb下有行锁，也有表级锁。

如果事务A要给一张表加表写锁，肯定是不允许这张表有其他事务B已经在某行数据上加了行写锁的。那么mysql怎么去检索一张表里面的数据有没有行写锁啊，一条一条去看，是不是效率也太低了~所以这里衍生了意向锁。如果事务B对某行数据加了行写锁(属于排他锁哦)，那么B也会同样给这张表加一个意向排他锁，等到事务A来加表锁的时候，事务A 会看到这张表上有了意向排它锁，事务A就不再进行加锁操作了。

下面我们重点讲行锁

二、行锁

行锁演示

表结构：id为主键，idx_name_age_sex(name,age,sex`)组合索引

DROP TABLE IF EXISTS `tuser`;

CREATE TABLE `tuser` (

`id` int(11) NOT NULL,

`name` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`age` int(11) NULL DEFAULT NULL,

`sex` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE,

INDEX `idx_name_age_sex`(`name`, `age`, `sex`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

数据

INSERT INTO `tuser` VALUES (1, '张三', 24, '1', '否');

INSERT INTO `tuser` VALUES (2, '李四', 33, '1', '2');

INSERT INTO `tuser` VALUES (3, '王五', 26, '1', '33');

INSERT INTO `tuser` VALUES (4, '苏六', 28, '2', '33');

INSERT INTO `tuser` VALUES (5, '赵期', 22, '1', '11');

INSERT INTO `tuser` VALUES (6, '刘芳', 26, '2', '无');

SET FOREIGN_KEY_CHECKS = 1;

session1：

BEGIN;

select * from tuser where name = '张三' lock in share mode;

session2:

BEGIN;

UPDATE tuser set age =38 where name='张三';

讲解：session1 开启事务，并执行select lock in share mode语句，此时会对name='张三’的记录加行读锁，不提交；此时，session2 开启事务，对name=‘张三’ 的数据执行update操作，结果为堵塞，如下图：

注意：事务提交后，锁就可以理解为释放了哈

三、间隙锁

间隙锁：很容易理解，锁住一个间隙。阻止其他事务向间隙中插入数据，或将数据更新为间隙内的值

这里介绍一个Next-Key，RR隔离级别当前读依靠next-key来解决幻读问题，快照读使用mvcc解决幻读

Next-key：记录锁+间隙锁

下面是我总结的各种情况的RR隔离级别下的加锁情况，先简单参考一下，不用背，理解就可以了，不理解的自己做实验试一下。

演示

表结构和数据看图吧

1、辅助索引

1)等值查询

a)命中记录

session1：

BEGIN;

select * from user where num=10 lock in share mode;

COMMIT;

session2:

BEGIN;

INSERT INTO `user` VALUES (9,11,22);

COMMIT;

执行结果：阻塞

分析

session1 走的是辅助索引的等值查询，且命中记录，所以会产生记录锁+间隙锁。间隙锁锁的位置就是如上图中的位置，而记录锁 锁的是 辅助索引树上num=10的这行记录 和 主键索引树上 num=10的记录(虽然也就是同一条数据，但是主键索引树和辅助索引树上都要锁住)。

b)无命中

session1

BEGIN;

select * from user where num=13 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (9,14,22);

INSERT INTO `user` VALUES (9,16,22);

效果截图：(9,14,22)阻塞，(9,16,22)成功

分析

首先我们的sql使用的是辅助索引，进行等值查询，但是没有命中记录，所以只会产生间隙锁，而不会产生记录锁。间隙锁的范围如上图所示。

2)范围查询

a)命中记录

session1

BEGIN;

select * from user where num>6 and num <11 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (9,6,22);

效果截图：

分析：

session1 走的是辅助索引的范围查询，且命中num为10的记录，所以会产生记录锁+间隙锁。间隙锁锁的位置就是如上图中的位置，而记录锁 锁的是 辅助索引树上num=10的这行记录 和 主键索引树上 num=10的记录(虽然也就是同一条数据，但是主键索引树和辅助索引树上都要锁住)。

b)无命中记录

session1

BEGIN;

select * from user where num>7 and num <9 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (9,6,22);

效果截图：

分析：

首先我们的sql使用的是辅助索引，进行范围查询，但是没有命中记录，所以只会产生间隙锁，而不会产生记录锁。间隙锁的范围如上图所示。

2、主键索引

1)等值查询

a)命中记录

session1

BEGIN;

select * from user where id=8 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (9,6,22);

效果截图：

分析

这条sql我们使用的是主键索引的等值查询，命中id=8的记录，这里只会产生记录锁，也就是锁住 主键索引树上的id=8的那条记录。不会产生间隙锁。

b)无命中记录

session1

BEGIN;

select * from user where id=17 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (18,18,22);

效果截图

分析

这条sql我们使用的是主键索引的等值查询，无命中记录，这里只会产生间隙锁。间隙锁范围如上图所示。

2)范围查询

a)命中记录

session1

BEGIN;

select * from user where id>14 and id <17 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (18,6,22);

效果截图

分析

这条sql我们使用的是主键索引的范围查询，命中id=15的记录，这里会产生间隙锁和记录锁。间隙锁范围如上图所示。而记录锁 锁的是 主键索引树上num=15的这行记录 。

b)无命中记录

session1

BEGIN;

select * from user where id>16 and id <18 lock in share mode;

session2

BEGIN;

INSERT INTO `user` VALUES (19,6,22);

效果截图

分析

这条sql我们使用的是主键索引的范围查询，无命中记录，这里只会产生间隙锁。间隙锁范围如上图所示。

标签：BEGIN,记录,间隙,行锁,索引,VALUES,user,意向锁,MySQL

来源： https://blog.csdn.net/weixin_44969687/article/details/107028262