腾讯一面:MySQL的共享锁和独占锁

数据库 MySQL
理解共享锁(S锁)和排他锁(X锁)的工作机制及其交互关系对于掌握MySQL的并发控制和锁机制非常重要,因此,今天就来一起聊聊MySQL的共享锁和排他锁。

在InnoDB存储引擎中,行级别锁有两种类型:共享锁(S锁)和排他锁(X锁),理解这 2种锁的工作机制及其交互关系对于掌握MySQL的并发控制和锁机制非常重要,因此,今天就来一起聊聊MySQL的共享锁和排他锁。

申明:本文基于 MySQL 8.0.30 版本,InnoDB引擎。

一、共享锁

1.什么是共享锁?

共享锁(shared lock,S锁),也叫读锁。它是指当对象被锁定时,允许多个事务同时读取该资源,也允许其它事务从该对象上再次获取共享锁,但不能对该对象进行写操作。

2.加锁方式

共享锁一般通过下面 2种方式进行加锁:

# 方式1
select ... lock in share mode;

# 方式2
select ... for share;

如果事务T1 持有某对象的共享(S)锁,则事务T2 需要再次获取该对象的锁时,会出现下面两种情况:

  • 如果T2 获取该对象的共享(S)锁,则可以立即获取锁;
  • 如果T2 获取该对象的排他(X)锁,则无法获取锁;

二、举例说明

为了更好地理解上述两种情况,这里分别以下面的执行顺序流对InnoDB存储引擎和MyISAM存储引擎进行验证:

1.InnoDB存储引擎

创建一张用户user表,表结构如下:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(1) 给行加共享锁

这里给user表中id=3行加共享锁为例,执行顺序流如下表:

加锁线程  sessionA

线程B  sessionB

线程C  sessionC

#开启事务

begin;



#给id=3的行加共享锁

select * from user

where id = 3 lock in share mode;




#获取id=3行的共享锁成功

#select操作执行成功

select * from user where id=3;

#获取id=3行的共享锁成功

#select操作执行成功

select * from user where id=3;


#获取id=3行的排它锁失败

#delete操作被堵塞

delete from user where id = 3;

#获取id=4行的排它锁成功

#delete操作执行成功

delete from user where id = 4;

#提交事务

#id=3的行上共享锁被释放

commit;




#获取id=3行的排它锁成功

#被堵塞的delete操作执行成功

delete from user where id = 3;


示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)对user中id=3这行添加共享锁后,事务B(sessionB)和事务C(sessionC)都可以获取user表的共享锁,也就是select操作能成功执行,但是事务B(SessionB)获取user表id=3的写锁失败,即delete where id=3操作被阻塞,而事务C(sessionC)获取user表id=4的写锁成功,即delete where id=4操作成功;

(2) 给表加共享锁

这里通过lock in share mode方式给user整张表添加共享锁,执行顺序流如下表:

加锁线程   sessionA

线程B    sessionB

#开启事务

begin;


#对user整张表加共享锁

select * from user lock in share mode;



#成功获取user表的共享锁,select操作成功执行

select * from user;


#获取user表的排他锁失败,操作被堵塞

delete from user where id = 1;

#提交事务

#user表的共享锁被释放

commit;



#获取user表上排他锁成功,delete操作执行成功

delete from user where id = 1;

示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)对user整张表添加共享锁后,事务B(sessionB)可以获取user表的共享锁,也就是select操作能成功执行,但是事务B(SessionB)获取user表的写锁失败,即delete操作被阻塞。

所以,尽管共享锁(S锁)是InnoDB存储引擎的行级别锁,但是一旦它作用到整张表时,其实是对表中所有的行加共享锁。

2.MyISAM引擎

创建一张用户person表,表结构如下:

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_

给行加共享锁

这里给person表的id=3行加共享锁为例,执行顺序流如下表:

加锁线程  sessionA

线程B  sessionB

#开启事务

begin;


#给id=3的行加共享锁

select * from person where id = 3 lock in share mode;



#获取id=3行的共享锁成功

#select操作成功

select * from person where id=3;


#获取id=3行的排它锁成功

#update操作成功

update person set name='name3xx' user where id = 3;


select * from person where id=3;

#提交事务

#id=3行上共享锁被释放

commit;


示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)对person中id=3这行添加共享锁后,事务B(sessionB)既能获取person表的共享锁,也能获取person表id=3的写锁,即select和update where id=3都操作成功;

因此,在MyISAM引擎中其实不存在共享锁。

3.总结

通过上述示例及其运行结果可以看出:

共享锁是InnoDB存储引擎的行级锁,在MyISAM存储引擎中不存在;

共享锁是尽管是行级别锁,但是当锁加在整个表时(表中所有的行,一种特殊的行),排他锁也会在表级别生效;

三、排它锁

1.什么是排他锁?

排它锁(exclusive lock,X锁),也叫写锁或者独占锁,主要是防止其它事务和当前加锁事务锁定同一对象,同一对象主要有两层含义:

  • 当排他锁加在表上,则其它事务无法对该表进行insert,update,delete,alter,drop等更新操作;
  • 当排他锁加在行上,则其它事务无法对该行进行insert,update,delete,alter,drop等更新操作;

2.加锁方式

排他锁加锁的方式一般有 2种:显式加锁和隐式加锁,如下:

-- 显式加锁
select ... for update;

-- 隐式加锁,是 MySQL内部自动加锁

为了更好的说明排他锁,这里以下面的执行顺序流来进行验证,用户user表的结构还是和上面的一样:

四、举例说明

为了更好地理解上述两种情况,这里分别以下面的执行顺序流对InnoDB存储引擎和MyISAM存储引擎进行验证:

1.InnoDB存储引擎

(1) 给行加排他锁

这里通过for update显式给user表中id=6行加排他锁为例,执行顺序流如下表:

加锁线程  sessionA

线程B  sessionB

线程C  sessionC

#开启事务

begin;



#给id=6的行加排他锁

select * from user

where id = 6 for update;




#获取id=6的共享锁成功

select * from user where id=6;

#获取id=6的共享锁成功

select * from user where id=6;


#获取id=6的排它锁失败

delete from user where id = 6;

#获取id=7的排它锁成功

delete from user where id = 7;

#提交事务

#user表id=6的行上排他锁被释放

commit;




#获取id=6的排它锁成功

#被堵塞的delete操作执行成功

delete from user where id = 6;


示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)对user中id=6这行添加共享锁后,事务B(sessionB)和事务C(sessionC)都可以获取user表的共享锁,也就是select操作能成功执行,但是事务B(SessionB)获取user表id=6的写锁失败,即delete where id=6操作被阻塞,而事务C(sessionC)获取user表id=7的写锁成功,即delete where id=7操作成功;

(2) 给表加排他锁

这里通过for update显式方式给user整张表添加排他锁,执行顺序流如下表:

加锁线程   sessionA

线程B    sessionB

#开启事务 begin;


#对user整张表加排他锁

select * from user for update;



#获取user表上的共享锁成功,select执行成功

select * from user;


#获取user表上的排他锁失败,操作被堵塞

delete from user where id=3;

#提交事务

#user表上的排他被释放

commit;



#获取user表上的排他锁成功,操作执行成功

delete from user where id = 3;

示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)对user整张表加排他锁后,事务B(sessionB)可以获取user表的共享锁,也就是select操作能成功执行,但是事务B(SessionB)获取user表的排他锁失败,即delete操作被阻塞;

所以,尽管排他锁(X锁)是InnoDB存储引擎的行级别锁,但是一旦它作用到整张表时,其实是对表中所有的行加排他锁。

2.MySQL 隐式加排他锁

这里通过MySQL隐式给user的id=6行添加排他锁,执行顺序流如下表

加锁线程   sessionA

线程B    sessionB

#开启事务 begin;


#MySQL隐式给id=6行加排他锁

update user set name = 'name6' where id =6;



#获取id=6的共享锁失败,select执行被阻塞

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

#提交事务

#id=6的排他被释放

commit;



#获取id=6表上的共享锁成功,select执行成功;

示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)执行update where id=6时,MySQL会隐式加排他锁,事务B(sessionB)在lock in share mode模式下获取user表id=6的共享锁失败,也就是select操作能成功被阻塞;

3.MyISAM引擎

MySQL 隐式加排他锁

这里通过MySQL隐式给person的id=4行添加排他锁,执行顺序流如下表:

加锁线程   sessionA

线程B    sessionB

#开启事务 begin;


#MySQL不会隐式给id=4行加排他锁

update person set name = 'name4' where id =4;



#获取id=4的共享锁成功,select执行成功

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


#获取id=4表上的共享锁成功,select执行成功;

#提交事务 id=4的排他被释放

commit;


示例执行结果图如下:

通过上述的示例执行结果可以看出:当事务A(sessionA)执行update where id=6时,MySQL不会隐式加排他锁,事务B(sessionB)既能获取id=4的共享锁,也能获取id=4的排他锁;

因此,在MyISAM引擎中其实不存在排他锁。

4.总结

通过上述 3个示例及其运行结果可以看出:排他锁有表级别共享锁和行级别共享锁和自动锁机制 3种 表级别共享锁:

  • 锁定整个表,排他锁也会在表级别生效;
  • 行级别共享锁:锁定特定行,排他锁也会在行级别生效;
  • 自动锁机制:根据操作是表级别还行级别自动加对应的锁;

五、共享锁和排他锁的兼容性矩阵

为了更好地理解共享锁和排他锁的互斥关系,可以参考以下兼容性矩阵:


无锁

共享锁

排他锁

无锁

允许

允许

允许

共享锁

允许

允许

阻塞

排他锁

允许

阻塞

阻塞

从上述矩阵可以看出:

  • 无锁状态下可以获取任何类型的锁
  • 共享锁状态下可以继续获取共享锁,但不能获取排他锁
  • 排他锁状态下不能获取任何其他锁

六、总结

  • 共享锁(S锁)和排他锁(X锁)是InnoDB存储引擎中的 2种行级别锁,MyISAM存储引擎不存在。
  • 尽管共享锁(S 锁)和排他锁(X 锁)是行级锁,但是当他们加到表级别时,对表所有行都生效,这样看上去等同表级锁
  • 共享锁(S 锁)允许多个事务同时读取数据,但不允许修改数据。多个事务可以同时持有共享锁
  • 排他锁(X 锁)允许一个事务修改数据。只有一个事务可以持有排他锁,并且在它释放锁之前,其他事务不能获得任何类型的锁
责任编辑:赵宁宁 来源: 猿java
相关推荐

2022-02-21 15:01:45

MySQL共享锁独占锁

2024-01-29 07:43:42

Java独占锁共享锁

2018-07-31 10:10:06

MySQLInnoDB死锁

2019-01-04 11:18:35

独享锁共享锁非公平锁

2024-05-15 09:23:45

MySQL排他锁共享锁

2020-10-20 13:50:47

MySQL数据库

2024-06-11 00:01:00

并发validate场景

2022-05-11 22:15:51

云计算云平台

2022-07-26 07:51:40

ThreadRunnableFuture

2020-09-03 11:10:34

MySQL数据库

2009-07-30 14:38:36

云计算

2020-09-19 17:46:20

React Hooks开发函数

2024-05-15 16:41:57

进程IO文件

2011-12-23 09:43:15

开源开放

2011-12-22 20:53:40

Android

2023-12-06 07:33:20

MySQL锁事间隙锁

2024-10-30 16:12:14

2022-11-30 17:13:05

MySQLDynamic存储

2024-01-29 01:08:01

悲观锁递归锁读写锁

2024-03-18 12:21:28

Java轻量级锁重量级锁
点赞
收藏

51CTO技术栈公众号