登录 注册

Mysql 各种锁

本贴最后更新于 1195 天前，其中的信息可能已经东海扬尘

Mysql 各种锁

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

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

共享锁又叫读锁

排他锁又叫写锁

锁和锁的关系如下表格:

共享锁 排他锁
共享锁 √ ×
排他锁 × ×
以下都叫读锁和写锁了, 可以少打几个字

二. 表锁

就是加在表上的锁, 上锁方式如下

  1. 手动上表锁

    # 多个用逗号分隔
# read就是表级读锁
# write就是表级写锁
LOCK tables orders read LOCAL,order_detail write;
LOCK tables orders read,order_detail write;
# LOCAL 可以加也可以不加
# 加了LOCAL 其他不能修改, 但是可以在表尾插入新的数据, 也就是ID一定要在后面加新记录, 不能加在中间

  2. 当表没有主键或唯一索引, 或者表有索引 但是 sql 索引没有生效的时候, 也会上表锁

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

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

三. 行锁

就是加在记录上的锁, 上锁方式如下

  1. 手动加行锁

    #加写锁 只给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

  2. 增删改自动加行的写锁

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

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

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

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

四. 临键锁(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

  1. 对主键或唯一索引操作

    1. 等值查询查到记录

      #事务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 的记录上加

    2. 等值查询未查到记录

      #事务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) 左开又开

    3. 范围查询 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,无穷大)

    4. 范围查询 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,无限大)

    5. 范围查询 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)

    6. 范围查询 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)

  2. 对普通索引操作

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

    image-20210814180033560

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

    image-20210814173554737

    1. 等值查询查到记录

      #事务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)

      插入的情况如下

      image-20210814174715521

      修改的情况如下

      image-20210814175530055

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

    2. 等值查询未查到记录

      #事务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)

      插入情况

      image-20210814181137388

      修改情况

      image-20210814183046408

    3. 范围查询

      #事务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 种

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

六.自增锁

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

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

  1. 插入空值的时候

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

  2. 插入已经有值的自增

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

七. 乐观锁

乐观锁需要自己实现

  1. 可以多加 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的记录

  2. 也可以直接 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 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    690 引用 • 535 回帖
  • 面试

    面试造航母，上班拧螺丝。多面试，少加班。

    325 引用 • 1395 回帖
上海 位置
2 回帖
512 37 34 103 2 36 7 293

相关帖子

2 回帖
Mysql 各种锁

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
  • xiaokedamowang •
    捐赠者 作者

    花了 2 天时间整理的, 呕心沥血, 求个赞

  • dahao •

    老哥好，我在别的文章了解到锁是加在具体的索引上的，主键索引那的测试感觉问题不大，普通索引那的测试，我感觉 where 条件是不是应该改为 age = xxx 这种，希望一起探讨探讨。

xiaokedamowang
懒 上海
回帖
5
 帖子
11
 积分
7714