一文搞懂数据库中的“锁”（图文详解）

[toc]

1.锁

1.1.锁的概述

锁是什么

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。
• 在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

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

1.2.全局锁

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

应用场景：

• 做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
• 如果不加全局锁，先后执行数据备份和业务的数据更新操作，会导致数据不一致

使用全局锁进行数据库逻辑备份的过程：

• 加全局锁

flush tables with read lock;

• mysqldump是数据库用于数据备份的工具，执行数据备份
• 注意：mysqldump是MySQL提供的一个工具，不是sql语句，需要在windows命令行中执行

mysqldump -uroot -p123456 user>user.sql

• 在加锁后，DML和DDL被阻塞，其他客户端不能写入数据，但是DQL可以执行，其他客户端可以查找数据
• 备份结束，得到备份后的文件，释放锁

unlock tables;

案例演示（模拟三个客户端）：

• 客户端A：对数据库加上全局锁

flush tables with read lock;

• 客户端B：执行select语句--->成功

select * from student;

执行update语句--->失败

update student set name = 'A' where id = 2;

• 客户端C：执行数据备份

mysqldump -h192.168.200.202 -uroot -p1234 db01 > D:/db01.sql

注意：mysqldump是MySQL提供的一个工具，不是sql语句，需要在windows命令行中执行

在D盘中可以看到数据已经备份完成

数据备份成功后，在客户端A中释放锁

unlock tables;

此时在客户端B中update就可以正常执行。

全局锁的好处：

• 保证数据的完整性。

全局锁的弊端：

• 粒度很大，如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果业务数据库不是单机版而是主从结构，且做了读写分离，那么在从库上备份不会影响主库的读写操作，但是在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

其他实现一致性数据备份的方式：

在InnoDB引擎中可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。其底层是通过快照读实现。

mysqldump --single-transaction -uroot –p123456 database > database.sql

1.3.表级锁

1.3.1.概念

表级锁，顾名思义，在每次操作时锁住整张表。应用在MyISAM、InnoDB、BDB等存储引擎中

1.3.2.表级锁分类

• 表锁
• 元数据锁
• 意向锁

1.3.3.表锁

表锁分类：

• 表共享读锁（简称：读锁）

• 表独占写锁（简称：写锁）

加锁的语法：

lock tables tb1 , tb2... read / write

释放锁的语法：

unlock tables 或者关闭客户端连接

写锁案例演示：

• 假设有一张表score，有两个客户端A和B，模拟读锁，以下操作依次执行：
• 在客户端A中给表score加读锁

lock tables score read ;

• 在客户端A中执行查询语句--->查询成功

select * from score;

• 在客户端A中执行更新语句--->更新失败，且报错

update score set math = 100 where id = 2;

在客户端B中执行查询语句--->查询成功

select * from score;

在客户端B中执行更新语句--->更新处于阻塞状态

update score set math = 100 where id = 2;

• 在客户端A中给表score释放读锁，此时B中阻塞状态解除，update更新成功

读锁案例演示：

• 假设有一张表score，有两个客户端A和B，模拟写锁，以下操作依次执行：
• 在客户端A中给表score加写锁

lock tables score write ;

• 在客户端A中执行查询语句--->查询成功

select * from score;

• 在客户端A中执行更新语句--->更新成功

update score set chinese = 100 where id = 2;

• 在客户端B中执行查询语句--->读取处于阻塞状态

select * from score;

• 在客户端B中执行更新语句--->更新处于阻塞状态

update score set chinese = 100 where id = 2;

1.3.4.元数据锁

• 元数据锁（meta data lock，MDL），该锁是系统自动控制的，在访问一张表的时候自动上锁。
• 元数据可以简单理解为表结构，元数据锁的作用是维护表结构的数据一致性，避免DML和DDL之间发生冲突，保证读写正确性
• 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL共享读锁和共享写锁（shared_read / shared_write）；当对表结构进行变更操作的时候，加MDL排他锁（exclusive）。
• 共享锁之间相互兼容，表示可以边读边写；共享锁与排他锁互斥，表示在进行增删改查时，不能同时执行表结构的变更。

元数据锁的案例演示：

• 以下操作按顺序依次演示。
• 客户端A开启事务，并对表score执行select查询操作--->成功执行

begin

select * from score;

• 客户端B也开启事务，并对表score执行select查询、update更新操作--->成功执行

begin

select * from score;

update score set math = 88 where id = 1;

• 可以发现上述操作都能成功执行，原因是共享锁之间相互兼容，可并发进行读写操作。

• 依次提交客户端A、B的事务

commit;

• 客户端A再次开启事务，并执行select查询操作，此时MDL自动给表结构加上共享读锁（shared_read）

begin

select * from score;

• 客户端B此时对表结构进行alter修改操作，结果处于阻塞状态，原因是在执行alter语句时，MDL会自动给表结构加上排他锁exclusive，该锁与共享锁互斥。

alter table score add column java int;

• 此时客户端A提交事务，客户端B的阻塞状态解除，原因是客户端A的共享读锁释放

• 我们可以通过以下语句查看元数据锁：

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

• 在客户端未执行任何增删改查语句前，底层只有一把元数据锁，锁的类型是一把共享锁（shared_read），属于元数据表metadata_locks

• 在客户端A执行select语句，然后在客户端B中查看元数据锁：可以发现MDL自动加上了一把共享读锁share_read

1.3.5.意向锁

• 为了避免DML在执行时，客户端A加的行锁与客户端B加的表锁的冲突，在InnoDB中引入了意向锁
• 意向锁使得客户端B在尝试加表锁时不用检查每行数据是否加了锁，直接根据是否有意向锁以及意向锁的类型来决定表锁是否可以添加成功，减少了表锁的检查。

意向锁的分类：

• 意向共享锁（IS）：与表锁共享锁（shared_read）兼容，与表锁排他锁（write）互斥。 由语句select ... lock in share mode添加 。
• 意向排他锁（IX）：与表锁共享锁（shared_read）及排他锁(write)都互斥，意向锁之间不会互斥。 由insert、update、delete、select...for update添加 。

我们可以通过以下语句查看意向锁是否添加成功：

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

案例演示：

• 客户端A开启事务，然后执行select语句，会自动为id=1这行加上行锁，随后手动通过lock in share mode加上意向共享锁IS：

begin;

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

• 客户端B加上表锁共享读锁（shared_read），此时表锁添加成功：

lock tables score read;

• 客户端B加上表锁写锁（write），此时发现被阻塞：

lock tables score write;

• 客户端A开启事务，然后执行update语句，会自动加上行锁和意向锁IX

update score set math = 66 where id = 1;

• 在客户端B中查看意向锁的情况，可以发现意向锁IX已经加上：

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

• 在客户端B中加上表锁（read），结果显示被阻塞，原因是意向锁IX和表锁读锁（read）互斥

lock tables score read;

• 在客户端A中提交事务，意向锁IX释放，随后客户端B中的读锁阻塞状态解除

1.4.行级锁

• 行级锁：每次加锁锁住对应的数据行和行间的间隙，锁的粒度最小，并发度最高。
• InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

1.4.1.行级锁的分类

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在Read Commit、Read Repeatable隔离级别下都支持。

• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在Read Repeatable隔离级别下都支持。

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在Read Repeatable隔离级别下支持。

1.4.2.行锁

• 行锁有两种，分为共享锁和排它锁
• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得同一数据集的排它锁（X）。即共享锁与共享锁之间兼容，共享锁和排它锁之间互斥。
• 排它锁（X）：允许获取了排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

锁之间的兼容和排斥情况：

SQL增删改查语句对应加的行锁：

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

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
• InnoDB的行锁是针对于索引加的锁，如果某字段没有创建索引，即不通过索引条件检索该字段的数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

我们可以通过以下语句查看行锁是否添加成功：

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

1.4.3.间隙锁和临键锁

• 默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。
• 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

案例演示：

• 根据索引进行等值查询，且索引是唯一索引（例如主键索引)，给一个不存在的记录加行锁时, 行锁会优化为间隙锁 。
  • 间隙锁的作用：防止其他并发事务在间隙中插入数据

如果根据索引进行等值查询，且该索引是普通索引（字段上的记录值有可能重复），那么在叶子节点中向右遍历的最后一个值不满足查询需求时，临键锁退化为间隙锁。

• 行锁的作用：防止单行记录被并发修改
• 临键锁/间隙锁的作用：防止单行前后间隙插入记录，引起幻读

如果根据索引进行范围查询，且该索引是唯一索引（如主键索引），那么会加上临键锁，会访问到不满足条件的第一个值为止。

• 行锁的作用：防止该行被并发修改
• 两个临键锁的作用：防止范围内的数据记录被并发修改