Mysql 各种锁

真是呕心沥血查资料, 做试验, 做分析, 可怜.jpg

一. 共享锁和排他锁(读锁和写锁)

共享锁又叫读锁

排他锁又叫写锁

锁和锁的关系如下表格:

	共享锁	排他锁
共享锁	√	×
排他锁	×	×

以下都叫读锁和写锁了, 可以少打几个字

二. 表锁

就是加在表上的锁, 上锁方式如下
手动上表锁
# 多个用逗号分隔
# read就是表级读锁
# write就是表级写锁
LOCK tables orders read LOCAL,order_detail write;
LOCK tables orders read,order_detail write;
# LOCAL 可以加也可以不加
# 加了LOCAL 其他不能修改, 但是可以在表尾插入新的数据, 也就是ID一定要在后面加新记录, 不能加在中间
当表没有主键或唯一索引, 或者表有索引但是 sql 索引没有生效的时候, 也会上表锁

当 A 连接加了读表锁之后, A 连接自己无法读取和修改其他表,并且无法对当前表做修改, 其他连接可以读取该表的数据, 但是不能修改, 如果修改, 会阻塞等待, 直到锁释放

当 A 连接加了写表锁之后, A 连接自己无法读取和修改别的表, 其他连接读和写都需要阻塞等待, 直到锁释放

三. 行锁

就是加在记录上的锁, 上锁方式如下
手动加行锁
#加写锁 只给id为1的记录加
select * from tables where id = 1 for update
#加写锁 给全部记录加
select * from tables for update
#加读锁 给id为1的记录加
select * from tables  where id = 1 lock in share mode
#加读锁 给全部记录加
select * from tables lock in share mode
增删改自动加行的写锁

修改和删除好理解, 增加记录还会有别的锁

行锁的互斥关系和一. 共享锁和排他锁(读锁和写锁)是一样的

如果一条记录加了读锁, 那么别的连接只能读, 不能写, 写的话就会阻塞

如果一条记录加了写锁, 那么别的连接不能读也不能写, 读写都会阻塞

四. 临键锁(next_key) 和间隙锁

临键锁(next_key) 和间隙锁是为了在 RR 隔离级别下解决幻读问题, 直接把区间锁住, 记录就插入不了, 就不会产生幻读

间隙锁: 顾名思义, 锁住一段间隙

临键锁: 就是间隙锁 + 行锁, 多锁了 1 个数据

innodb 默认使用临键锁(next_key)但是在主键索引和唯一索引, 遇到下面的情况会退化锁

精确匹配行锁

精确匹配值不存在 间隙锁

范围匹配 行锁 + 间隙锁

精确匹配	行锁
精确匹配值不存在	间隙锁
范围匹配	行锁 + 间隙锁

# 以下操作都在RR隔离级别上  也就是可重复读  间隙锁和临键锁 是为了解决幻读
CREATE TABLE `test_lock` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `3pp_menus`.`test_lock`(`id`, `age`) VALUES (5, 5);
INSERT INTO `3pp_menus`.`test_lock`(`id`, `age`) VALUES (10, 10);
INSERT INTO `3pp_menus`.`test_lock`(`id`, `age`) VALUES (15, 15);
INSERT INTO `3pp_menus`.`test_lock`(`id`, `age`) VALUES (20, 20);
INSERT INTO `3pp_menus`.`test_lock`(`id`, `age`) VALUES (25, 25);

id(主键或唯一索引)	age(普通索引)
5	5
10	10
15	15
20	20
25	25

对主键或唯一索引操作

等值查询查到记录

#事务A
select * from test_lock where id = 5 for update
#事务B
insert into test_lock values (4,4) #成功
insert into test_lock values (6,6) #成功
update test_lock set age = 123 where id = 5 #阻塞

加行锁, 只在 id 为 5 的记录上加

等值查询未查到记录

#事务A
select * from test_lock where id = 12 for update
#事务B
insert into test_lock values (4,4) #成功
insert into test_lock values (6,6) #成功
insert into test_lock values (11,11) #阻塞
insert into test_lock values (16,16) #成功
update test_lock set age = age + 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #成功

加间隙锁, (10,15) 左开又开

范围查询 1

#事务A
select * from test_lock where id > 10 for update
#事务B
insert into test_lock values (6,6) #成功
insert into test_lock values (11,11) #阻塞
insert into test_lock values (16,16) #阻塞
update test_lock set age = age + 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #阻塞
update test_lock set age = age + 1 where id = 20 #阻塞

加间隙锁, (10,无穷大)

范围查询 2

#事务A
select * from test_lock where id > 17  for update
#事务B
insert into test_lock values (14,14) #成功
insert into test_lock values (16,16) #阻塞
insert into test_lock values (18,18) #阻塞
update test_lock set age = age + 1 where id = 15 #成功
update test_lock set age = age + 1 where id = 20 #阻塞

加间隙锁, (15,无限大)

范围查询 3

#事务A
select * from test_lock where id > 12 and id < 17 for update
#事务B
insert into test_lock values (8,8) #成功
insert into test_lock values (11,11) #阻塞
insert into test_lock values (14,14) #阻塞
insert into test_lock values (16,16) #阻塞
insert into test_lock values (18,18) #阻塞
insert into test_lock values (21,21) #成功
update test_lock set age = age + 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #阻塞
update test_lock set age = age + 1 where id = 20 #成功

间隙锁 + 行锁 + 间隙锁, (10,15) [15] (15,20)

范围查询 4

#事务A
select * from test_lock where id < 17 for update
#事务B
insert into test_lock values (21,21) #成功
insert into test_lock values (18,18) #阻塞
insert into test_lock values (4,4) #阻塞
update test_lock set age = age + 1 where id = 20 #成功
update test_lock set age = age + 1 where id = 15 #阻塞

间隙锁 (无限小,20)

对普通索引操作

先需要了解 1 下索引树是什么样子的, 更详细可以查看这篇博客 mysql 索引

下面是我们的测试数据图, 由于数据量太少, 所以只有一个节点

等值查询查到记录

#事务A
select * from test_lock where age = 15 for update
#事务B
insert into test_lock (id,age)values (9,10) #成功
insert into test_lock (id,age)values (11,10) #阻塞
insert into test_lock (id,age)values (50,14) #阻塞
insert into test_lock (id,age)values (51,16) #阻塞
insert into test_lock (id,age)values (52,19) #阻塞
insert into test_lock (id,age)values (53,21) #成功
update test_lock set age = age + 1 where id = 10 #阻塞
update test_lock set age = age - 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #阻塞
update test_lock set age = age + 1 where id = 20 #成功

临键锁 + 间隙锁 (10,15] + (15,20)

插入的情况如下

修改的情况如下

修改不太好画, 简单来说就是可以把值改出去, 但是不能改进来, 哪怕原本就在这个范围里

等值查询未查到记录

#事务A
select * from test_lock where age = 12 for update
#事务B
insert into test_lock (id,age)values (9,10) #成功
insert into test_lock (id,age)values (11,10) #阻塞
insert into test_lock (id,age)values (11,11) #阻塞
insert into test_lock (id,age)values (16,15) #成功
insert into test_lock (id,age)values (21,10) #阻塞
insert into test_lock (id,age)values (30,14) #阻塞
insert into test_lock (id,age)values (32,16) #成功
update test_lock set age = age + 1 where id = 10 #阻塞
update test_lock set age = age - 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #成功
update test_lock set age = age - 1 where id = 15 #阻塞
update test_lock set age = 10 where id = 20 #阻塞
update test_lock set age = 10 where id = 25 #阻塞
update test_lock set age = 15 where id = 20 #成功
update test_lock set age = 10 where id = 5 #成功

间隙锁 (10,15)

插入情况

修改情况

范围查询

#事务A
select * from test_lock where age > 12 and age < 17 for update
#事务B
insert into test_lock (id,age)values (9,10) #成功
insert into test_lock (id,age)values (11,10) #阻塞
insert into test_lock (id,age)values (50,14) #阻塞
insert into test_lock (id,age)values (51,16) #阻塞
insert into test_lock (id,age)values (52,19) #阻塞
insert into test_lock (id,age)values (53,21) #成功
update test_lock set age = age + 1 where id = 10 #阻塞
update test_lock set age = age - 1 where id = 10 #成功
update test_lock set age = age + 1 where id = 15 #阻塞
update test_lock set age = age + 1 where id = 20 #阻塞  只有这里和案例一不一样
update test_lock set age = 20 where id = 25 #成功
update test_lock set age = 19 where id = 25 #阻塞

临键锁 + 临键锁 (10,15] (15,20]

这里只有update test_lock set age = age + 1 where id = 20 和案例一不一样, 只是多锁了 1 条记录, 所以后面的间隙锁变成了临键锁

间隙锁和间隙锁不是互斥的, 假如 2 个连接都拿到了同样的间隙锁, 然后去修改或新增, 那么会产生死锁问题

update, delete, select for update 可以获得间隙锁

五. 意向锁

意向锁是锁在表上的, 有意向排他锁, 和意向共享锁

主要的作用就是加速锁的判断

比如: A 连接对某条记录加行锁**(读锁, 写锁都行)**, B 连接这时候如果想加表级写锁, 那么他得一条条记录遍历, 才知道有没有人加过锁

那么 A 给某条记录加锁之前, 给表加意向锁, 代表有人正在执行读或写的操作, 那么 B 连接不用遍历也知道, 有人在操作数据, 他的写锁得等前面人操作完成后才能加, 直接阻塞等待就行

情况就 2 种

A 要写, 给表加意向写锁, B 要加表锁, 不管是读写都得阻塞
A 要读, 给表加意向读锁, B 要加表级写锁, 阻塞等待

六.自增锁

自增锁是 MySQL 一种特殊的锁，如果表中存在自增字段，MySQL 便会自动维护一个自增锁。

就是 ID 自增的时候, 要确保 ID 不重复的锁

插入空值的时候

1、申请AUTO_INC锁
2、得到当前的AUTO_INCREMENT值n，并加1
3、执行插入操作，并将n写入新增的对应字段中。
4、释放AUTO_INC锁。

插入已经有值的自增

1、插入第一条数据
2、如果失败流程结束
3、如果成功，申请AUTO_INC锁
4、调用set_max函数，修改AUTO_INCREMENT
5、语句结束，释放AUTO_INC锁

七. 乐观锁

乐观锁需要自己实现

可以多加 1 个 version 字段, 每次修改的时候 where 条件带上 version

select * from user where id = 10
# 查出记录, 比如: id = 10, name = 张三, age = 20, version = 3
# 然后修改张三的年龄为21
update user set age = 21, version = version + 1 where id = 10 and version = 3
# 如果有别的线程改过,并且也遵循乐观锁的规则, 每次修改数据都version + 1, 那么上面这条sql就会执行失败, 因为表中的version 已经被别人 + 1了,变成了4, 找不到id = 10, version = 3的记录

也可以直接 where 条件带上旧值, 不过这适合修改一个字段

select * from user where id = 10
# 查出记录, 比如: id = 10, name = 张三, age = 20
# 然后修改张三的年龄为21
update user set age = 21 where id = 10 and age = 20

Mysql 各种锁

Mysql 各种锁

真是呕心沥血查资料, 做试验, 做分析, 可怜.jpg

一. 共享锁和排他锁(读锁和写锁)

以下都叫读锁和写锁了, 可以少打几个字

二. 表锁

三. 行锁

四. 临键锁(next_key) 和间隙锁

间隙锁和间隙锁不是互斥的, 假如 2 个连接都拿到了同样的间隙锁, 然后去修改或新增, 那么会产生死锁问题

五. 意向锁

六.自增锁

七. 乐观锁

相关帖子

手写 call,apply,bind

哈哈哈

数据库篇

Diss 一下前端“八股文”

虚拟机篇

技术招聘漫谈 | 正在招 Golang 工程师的你，赶快收藏这份识人秘籍！

面经 -1

欢迎来到这里！

Mysql 各种锁

Mysql 各种锁

真是呕心沥血查资料, 做试验, 做分析, 可怜.jpg

一. 共享锁和排他锁(读锁和写锁)

以下都叫读锁和写锁了, 可以少打几个字

二. 表锁

三. 行锁

四. 临键锁(next_key) 和 间隙锁

间隙锁 和 间隙锁不是互斥的, 假如 2 个连接都拿到了同样的间隙锁, 然后去修改或新增, 那么会产生死锁问题

五. 意向锁

六.自增锁

七. 乐观锁

相关帖子

手写 call,apply,bind

哈哈哈

数据库篇

Diss 一下前端“八股文”

虚拟机篇

技术招聘漫谈 | 正在招 Golang 工程师的你，赶快收藏这份识人秘籍！

面经 -1

欢迎来到这里！

四. 临键锁(next_key) 和间隙锁

间隙锁和间隙锁不是互斥的, 假如 2 个连接都拿到了同样的间隙锁, 然后去修改或新增, 那么会产生死锁问题