0401锁详解-MySQL-数据库

1 概述

• 介绍
  锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库显得尤其重要，也更加复杂。

• 分类：MySQL中的锁，按照锁的粒度分，分为以下三类

  • 全局锁：锁定数据库中的所有表；
  • 表锁：锁住整张表；
  • 行级锁：每次操作锁住对应的行数据。

2 全局锁

• 介绍

全局锁就是对整个数据库实例加锁，加锁后整个数据库实例处于只读状态，后续DML语句、DDL语句以及更新操作的事务提交语句都将被阻塞。

其典型应用场景就是做全库的逻辑备份，多所有表锁定，从而获得一致性视图，保证数据的完整性。

• 演示

-- 加全局锁
flush tables with read lock;
-- 备份
mysqldump -u用户名 -p密码 数据库名 > 备份地址;
-- 解锁
unlock tables;

备份示例，窗口1 ：

-- 链接数据库
mysql -h 127.0.0.1 -uroot -p
-- 加全局锁
flush tables with read lock;

窗口2 ，备份数据库

mysqldump -h 127.0.0.1 -uroot -proot1234 nacos> /Users/gaogzhen/Downloads/nacos1.sql

• mysqldump是mysql提供的备份工具，不是sql命令

窗口3 ，链接数据库执行插入操作

use nacos;
insert into nacos values('gaogzhen', '123456', 1);

• 未释放全局锁之前，该操作一直阻塞

如下图所示：

窗口1执行释放全局锁命令：

unlock tables;

释放锁之后，如下图所示：

• 特点：数据库中加全局锁，是一个比较重的操作，存在以下问题：
  • 如果在主库上备份，那么在备份期间不能执行更新，业务停摆；
  • 如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDb引擎中，我们可以在备份时加上参数--single-transaction来完成不加锁的一致性备份数据。

命令如下：

-- 不加锁备份
mysqldump -h host --single-transaction -u用户名 -p密码 数据库名 > 备份地址;

3 表锁

表级锁，每次操作锁住整张表。锁粒度大，发生锁冲突概率高，并发度低。应用在MyISAM、InnoDB、BDB等存储引擎中，我们主要讲解InnoDB中的表级锁。

表级锁分类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

3.1 表锁

表锁分类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

加锁、解锁语法：

-- 加锁
lock tables 表名 read/write;
-- 解锁
unlock tables / 客户端断开连接。

• 读锁特点：客户端A加read lock,客户端A只能执行读操作，不能执行写操作；其他连接能执行读操作，写操作会被阻塞，直至库客户端A释放锁。如下图所示

  

示例如下：

步骤1：开启2个客户端，目标表score。

步骤2：客户端A对表A加读锁

 -- 表score加读锁
 lock tables score read;

步骤3：客户端A执行读取操作

select * from score;
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |      90 |
|  3 | Jimi |   98 |      77 |      80 |

步骤4：客户端A执行写操作

update score set english=100 where id=1;
ERROR 1099 (HY000): Table 'score' was locked with a READ lock and can't be updated

步骤5：客户端B执行读取操作

select * from score;

步骤6：客户端B执行写操作

update score set 

• 阻塞

• 表write特点：客户端A加写锁，客户端可以执行读写操作；其他客户端读写操作会被阻塞，直至客户端释放锁.

  

示例：

-- 客户端加写锁
lock tables score write;
select * from score;
update score set math=60 where id=1;
-- 客户端B查询
select * from score;
-- 客户端A释放锁
unlock tables;

阻塞如下图所示：

释放锁如下图所示：

3.2 元数据锁

MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候自动加上。MDL锁的主要作用是维护表元数据的数据一致性。在表上有活动事务时，不能对元数据执行写操作。为了避免DML与DDL冲突，保证读写的正确性。

元数据是描述数据库结构和对象的信息，如表结构、索引、触发器等。

在MySQL5.5加入了MDL， 当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排它锁）。如下表所示：

SQL类型	锁类型	说明
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select\select … lock in share mode	SHARED_READ	与SAHRED_READ,SHARED_WRITE兼容，与EXCLUSIVE互斥
Insert、update、delete、select … for update	SHARED_WRITE	元数据是描述数据库结构和对象的信息，如表结构、索引、触发器等。
alter table …	EXCLUSIVE	与其他MDL互斥

示例：

-- 窗口1开启事务，执行查询
select * from score;

-- 窗口2 开启事务，执行修改表结构
alter table add column java int;

如下图所示：3.2-1所示：

修改表结构语句，加exclusive排它MDL锁，阻塞；当窗口1提交事务后，继续执行。

• 查看元数据锁

  select object_type,object_schema,object_name,lock_type,lock_duration from information_schema.metadata_locks;
  

示例：

-窗口1 开启事务
begin;
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
≈

object_type	object_schema	object_name	lock_type	lock_duration
TABLE	performance_schema	metadata_locks	SHARED_READ	TRANSACTION

-- 窗口1执行查询
select * from score;
-- 窗口2 开启事务
begin;
update score set math=28 where id=1;
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

如下表所示：

object_type	object_schema	object_name	lock_type	lock_duration
TABLE	performance_schema	metadata_locks	SHARED_READ	TRANSACTION
TABLE	performance_schema	metadata_locks	SHARED_READ	TRANSACTION
TABLE	gaogzhen	score	SHARED_READ	TRANSACTION
TABLE	gaogzhen	score	SHARED_WRITE	TRANSACTION

-- 现在窗口2 执行删除java列操作
alter table score drop column java;
-- 窗口1 查看元数据锁

• 窗口2 阻塞，如下图所示

窗口1，提交事务，窗口2正常执行。

3.3 意向锁

• 概述

InnoDB意向锁是为了支持多粒度锁共存而设计的，意向锁是一种特殊的行锁。在取得行锁之前需要先获取表的意向锁。
意向锁分为两类：意向共享锁和意向排他锁：

• 意向共享锁IS：表示事务想要对表中的行设置共享锁，有语句select ... in share mode添加；
• 意向排他锁IX：表示事务想要对表中行设置排他锁，由insert\update\delete ... 添加。

意向锁主要是辅助表级和行级锁冲突的判断，因为InnoDB支持行级锁，如果没有意向锁，那么判断表级锁和行级锁冲突就需要遍历所有行的行锁，有了意向锁就可以直接判断意向锁是否存在就可以判断是否有行锁了。

• 与表锁兼容性

  • 意向共享锁（IS）： 与表锁共享锁（read）兼容，与表锁排它锁（write）互斥；
  • 意向排它锁（IX）：与表锁共享锁（read）及表示排它锁（write）都互斥；
  • 意向锁之间不互斥。

• 查看意向锁与行锁加锁

  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
  

• 意向锁测试

第一步：窗口1 开启事务，添加意向共享锁IS

begin;
select * from score where id=1 lock in share mode;

第二步：窗口2查看锁记录

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IS	NULL
gaogzhen	score	PRIMARY	RECORD	S,REC_NOT_GAP	1

第三步：窗口2 加表共享锁（read）

mysql> lock tables score read;
Query OK, 0 rows affected (0.01 sec)

mysql> unlock tables
    -> ;
Query OK, 0 rows affected (0.01 sec)

第四步：窗口2 加表排它锁（write）,阻塞。

第五步：窗口1提交事务，窗口2阻塞解除。

第六步：窗口1开启事务，添加意向排它锁（IX）

begin;
update score set math=88 where id=1;

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IX	NULL
gaogzhen	score	PRIMARY	RECORD	X,REC_NOT_GAP	1

第七部：窗口2执行加表锁操作及及意向锁操作

lock tables score read;
-- 阻塞，窗口1重新开启事务添加意向排它锁IX
lock tables score write;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-- 阻塞，窗口1重新开启事务添加意向排它锁IX
update score set math=88 where id=2;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IX	NULL
gaogzhen	score	PRIMARY	RECORD	X,REC_NOT_GAP	2
gaogzhen	score	NULL	TABLE	IX	NULL
gaogzhen	score	PRIMARY	RECORD	X,REC_NOT_GAP	1

​

4 行锁

4.1 概述

行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB引擎中。

InnoDB引擎的数据是基于索引组织的，行锁通过对索引上加锁来实现的，而不是对记录加的锁。

• 行锁分类：
  • 行锁（Record Lock）：锁定单个行记录，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持；
  • 间隙锁（Gap Lock）： 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他记录在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  • 临建锁（Next-Key Lock）：行锁和间隙锁的结合，同时锁住行数据，并锁住当前行数据前面的间隙。在RR隔离级别下支持。

4.2 行锁

InnoDB实现了一些两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁；
• 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁。

请求锁类型	S（共享锁）	X（排它锁）
S（共享锁）	兼容	冲突
X（排它锁）	冲突	冲突

• 加锁

SQL	行锁类型	说明
insert \update\delete	X（排它锁）	自动加锁
select …	不加任何锁
select … lock in share mode	S(共享锁)	手动在select之后加lock in share mode
select … for update	X(排它锁)	手动在select之后加for update

• 说明

默认情况下，InnoDB在REPEATABLE READ 事务隔离级别下运行，InnoDB使用Next-Key Lock锁（临建锁）进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，自动优化为行锁；
2. InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB引擎将对表中的所有记录加锁，此时升级为表锁。

• 查看意向锁及行锁加锁

  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
  

• 演示

初始化：窗口1开启事务，执行普通的select ,窗口2查看加锁情况

select * from score where id=1;
-- 无锁
Empty set (0.01 sec)

第一步：窗口1加共享行锁，再次查看

select * from score where id=1 lock in share mode;

查询结果如下表4.2-1所示：

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IS	NULL
gaogzhen	score	PRIMARY	RECORD	S,REC_NOT_GAP	1

第二步：窗口2开启事务，也对相同行加共享行锁，查看锁情况

select * from score where id=1 lock in share mode;

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IS	NULL
gaogzhen	score	PRIMARY	RECORD	S,REC_NOT_GAP	1
gaogzhen	score	NULL	TABLE	IS	NULL
gaogzhen	score	PRIMARY	RECORD	S,REC_NOT_GAP	1

第三步：窗口2 提交事务，重新开启事务对id=3的数据行执行修改操作

update score name='Java' where id=3;

• 不阻塞，正常更新，不同数据集之间行锁不会冲突。

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IX	NULL
gaogzhen	score	PRIMARY	RECORD	X,REC_NOT_GAP	3
gaogzhen	score	NULL	TABLE	IS	NULL
gaogzhen	score	PRIMARY	RECORD	S,REC_NOT_GAP	1

第四步：窗口2对id=1的数据行执行更新操作

update score set name='Java' where id=1;
-- 窗口2事务行锁
 gaogzhen      | score       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1 

• 窗口2阻塞

其他情况不在测试，有兴趣自行测试

第五步：测试无索引更新与添加索引后的更新

-- 重新开启事务
begin;
-- 窗口1 通过name字段更新分数
update score set math=88 where name='Tom';

查看加锁情况，对所有记录加锁

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	score	NULL	TABLE	IX	NULL
gaogzhen	score	PRIMARY	RECORD	X	supremum pseudo-record
gaogzhen	score	PRIMARY	RECORD	X	1
gaogzhen	score	PRIMARY	RECORD	X	2
gaogzhen	score	PRIMARY	RECORD	X	3

窗口2执行更新操作，阻塞

给name字段添加索引

create index idx_score_name on score(`name`);

再次执行上述操作，窗口1根据name字段跟新数据

4.3 间隙锁&临建锁

默认情况下，InnoDB在REPEATABLE READ 事务隔离级别下运行，InnoDB使用Next-Key Lock锁（临建锁）进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询（唯一索引）

   • 记录存在时，对应索引添加行锁
   • 给不存在记录加锁时，优化为间隙锁，即向右边遍历第一个不满足条件索引加间隙锁；

2. 索引上的等值查询（普通索引）

   • 记录存在时，对应的主键索引添加行锁；普通索引对应的记录前后加临建锁；
   • 记录不存在时向右遍历至第一个不满足查询需求时next-key lock 退化为间隙锁；

3. 索引上的范围查询

   • 唯一索引：范围内添加临建锁

   • 普通索引：对应普通索引范围内添加临建锁；对应存在主键索引添加行锁。

说明：对于表中最大值之后的临建锁怎么加的呢

• 示例：| gaogzhen | stu | idx_stu_age | RECORD | S | supremum pseudo-record |

• supremum pseudo-record：上界伪记录即无穷大到记录对应查询最大值之间的间隙

测试表stu如下所示：

id	age	name
1	2	tom
3	4	cat
8	9	rose
11	12	jetty
19	20	lily
25	26	luci

• 测试更新id=5的行数据

  update stu set age=20 where id=5;
  

sss

加锁情况如下表所示：

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	stu	NULL	TABLE	IX	NULL
gaogzhen	stu	PRIMARY	RECORD	X,GAP	8

• 索引：主键索引
• 锁类型：行锁
• 锁模式：排它锁，间隙锁。
• 锁的间隙：id 3~8之间的间隙，不包含id为3和8的间隙

验证一下，插入一条id=7的数据

insert into stu values(7, 20, 'Lucy');

• 窗口2阻塞

• 测试普通索引等值查询

在age字段创建普通索引，窗口1开启事务，执行查询

create index idx_stu_age on stu(`age`);
begin;
select * from stu where age=4 lock in share mode;

查看加锁情况：

object_schema	object_name	index_name	lock_type	lock_mode	lock_data
gaogzhen	stu	NULL	TABLE	IS	NULL
gaogzhen	stu	idex_stu_age	RECORD	S	4, 3
gaogzhen	stu	PRIMARY	RECORD	S,REC_NOT_GAP	3
gaogzhen	stu	idx_stu_age	RECORD	S,GAP	9, 8

• 第二条记录，索引类型idx_stu_age,锁模式S临建锁，lock_date age=4对应主键3
  • 锁住age=4及之前的间隙即age=2记录到age=4之间间隙及age=4记录对应的索引
• 第三条记录，锁住id=3对应的主键索引
• 第四条，锁住age=4之后的间隙，即age 4~9之间的间隙对应的

其他情况自行测试。lock_mode标志如下表所示

	行锁	间隙锁	临建锁
共享锁	S,REC_NOT_GAP	S,GAP	S
排它锁	X,REC_NOT_GAP	X,GAP	X

5 小结

5.1 概述

• 在并发访问数据库时，解决数据的一致性、有效性问题；
• 分类：全局锁、表级锁、行级锁。

5.2 全局锁

• 对整个数据库加锁，加锁后整个数据库实例处于只读状态；
• 性能较差，数据逻辑备份时使用。

5.3 表锁

• 操作时锁住整张表，锁粒度大，发生锁冲突概率高；
• 分类：表锁、元数据锁、意向锁。

5.4 行锁

• 操作锁住对应的行数据，锁粒度最小，发生锁冲突的概率最低；
• 分类：行锁、间隙锁、临建锁。

结语

如果小伙伴什么问题或者指教，欢迎交流。

❓QQ:806797785

参考链接:

[1]MySQL数据库视频[CP/OL].2020-04-16.p122-132.