MySQL数据库锁：共享锁和独占锁

发表于 2024-06-05 更新于 2024-06-06 分类于 MySQL 阅读次数：本文字数： 5.6k 阅读时长 ≈ 5 分钟

你好，我是猿java。

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

申明：本文基于 MySQL 8.0.30 版本，InnoDB引擎

共享锁

什么是共享锁？

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

加锁方式

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

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

# 方式2
select ... for share;

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

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

举例说明

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

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

给行加共享锁

这里给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操作成功；

给表加共享锁

这里通过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存储引擎的行级别锁，但是一旦它作用到整张表时，其实是对表中所有的行加共享锁。

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引擎中其实不存在共享锁。

总结

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

共享锁是InnoDB存储引擎的行级锁，在MyISAM存储引擎中不存在；
共享锁是尽管是行级别锁，但是当锁加在整个表时（表中所有的行，一种特殊的行），排他锁也会在表级别生效；

排它锁

什么是排他锁？

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

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

加锁方式

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

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

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

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

举例说明

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

InnoDB存储引擎

给行加排他锁

这里通过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操作成功；

给表加排他锁

这里通过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存储引擎的行级别锁，但是一旦它作用到整张表时，其实是对表中所有的行加排他锁。

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操作能成功被阻塞；

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的共享锁busuanzi_count，也能获取id=4的排他锁；

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

总结

通过上述 3个示例及其运行结果可以看出：排他锁有表级别共享锁和行级别共享锁和自动锁机制 3种
表级别共享锁：锁定整个表，排他锁也会在表级别生效；
行级别共享锁：锁定特定行，排他锁也会在行级别生效；
自动锁机制：根据操作是表级别还行级别自动加对应的锁；

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

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

	无锁	共享锁	排他锁
无锁	允许	允许	允许
共享锁	允许	允许	阻塞
排他锁	允许	阻塞	阻塞

从上述矩阵可以看出：

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

总结

共享锁（S锁）和排他锁（X锁）是InnoDB存储引擎中的 2种行级别锁，MyISAM存储引擎不存在。
尽管共享锁（S 锁）和排他锁（X 锁）是行级锁，但是当他们加到表级别时，对表所有行都生效，这样看上去等同表级锁
共享锁（S 锁）允许多个事务同时读取数据，但不允许修改数据。多个事务可以同时持有共享锁
排他锁（X 锁）允许一个事务修改数据。只有一个事务可以持有排他锁，并且在它释放锁之前，其他事务不能获得任何类型的锁

参考

InnoDB Locking官方资料

学习交流

如果你觉得文章有帮助，请帮忙转发给更多的好友，或关注公众号：猿java，持续输出硬核文章。