概述

本章，您将学习到 MySQL 8 中有关锁机制的知识，内容包括：

• 锁机制的分类
• 演示 InnoDB 存储引擎的行锁
• 演示 InnoDB 存储引擎的表锁
• 锁的查看
• 有关乐观锁、全局锁和死锁的内容
• MVCC

由于内容较多，本文档来说明并演示 InnoDB 存储引擎的表锁。

InnoDB 的表锁

主要包括以下锁：

• 意向排他锁（IS）
• 意向共享锁（IX）
• 自增锁
• MDL

意向共享锁（IS）和意向排他锁（IX）

前文提到：

意向锁：表锁的一种，其本身并不直接锁定任何数据行，而是作为一种 "标记" 存在，用于表明事务后续准备对表中的某些行施加共享锁（S 锁）或排他锁（X 锁）。意向锁由存储引擎自行维护，使用者无法手动操作。意向锁又划分为意向共享锁（IS 锁） 和意向排他锁（IX 锁）。

例如，当某一个事务想对表添加表锁，它需要先确认表中没有任何行被其他事务加了行锁，对于一张存储了十几万行数据的表而言，逐行检查的效率非常地低，于是便引入了意向锁。有了意向锁之后，事务在给某行数据加锁前，会先自动获取该表的意向锁（IS 锁或 IX 锁），这样其他事务想获取表锁时，只需检查意向锁状态即可快速判断，避免了逐行检查。

从这里可以看到，意向锁与意向锁之间是相互兼容的；排他锁与其他任何锁都是冲突的。注意！这里的共享锁或排他锁都是表级别的。

作者还是使用前文的 locks 库来演示，示例如下：

use locks;

create table if not exists tlock1(
  id int primary key auto_increment,
  name varchar(10)
);

insert into tlock1 values(1,'kone'),(7,'john'),(15,'lee');

• Session 1 中，id 列应用了主键约束，自动生成同名主键索引，for update 表示主键索引持有一个行锁（记录锁，排他锁），其他事务对这条记录无法更新、删除，但是可以读取。这里 Innodb 存储引擎其实已经自动加了 IX 锁
• Session 2 中，select * from tlock1 where id=1 for update; 会阻塞，因为行级 X 锁与行级 X 锁之间是相互冲突的
• Session 2 中，lock table tlock1 read; 表示为 tlock1 表添加表级读锁（表级 S 锁），这条 SQL 语句会阻塞，因为 Session 1 的 IX 锁与表级 S 锁是冲突的
• Session 2 中，select * from tlock1 where id=7 lock in share mode; 会正常执行，Session 1 只是锁住了 id=1 这条记录，id=7 这条记录不受影响

表锁的语法与演示

添加表锁的语法

基本语法为 —— lock table 表名 read | write;，比如：

# 为某张表添加表级读锁（表级 S 锁）
lock table 表名 read;

# 为某张表添加表级写锁（表级 X 锁）
lock table 表名 write;

# 为多张表添加表锁
lock table 表名1 read, 表名2 write;

表级 S 锁的特点：

• 一旦表持有表级读锁，当前会话以及其他会话能读取表中的数据（select），但不能进行修改操作（delete、update、insert）

表级 X 锁的特点：

• 一旦表持有表级写锁，当前会话可以允许读取表中数据以及进行修改操作，但其他会话不能读取表中的数据（select），也不能进行修改操作（delete、update、insert）

删除表锁的语法

语法为 —— unlock tables;

表级读锁的演示

• Session 3 中，lock table tlock1 read; 表示为 tlock1 表添加表级读锁
• Session 3 中，insert into tlock1 values(21,'Qa'); 会输出 "ERROR 1099 (HY000): Table 'tlock1' was locked with a READ lock and can't be updated" 错误
• Session 3 中，delete from tlock1 where id=7; 会输出 "ERROR 1099 (HY000): Table 'tlock1' was locked with a READ lock and can't be updated" 错误
• Session 4 中，select * from tlock1; 正常运行
• Session 4 中，delete from tlock1 where id=1;、update tlock1 set name='Liu' where id=15; 和 insert into tlock1 values(20,'M'); 会阻塞
• Session 4 中，select * from tlock1 where id=15 lock in share mode; 正常运行，因为 Session 3 是表级读锁，当前会话以及其他会话可以读取表中的数据，Session 4 中，这是一个行级共享锁，两种锁并不冲突（都允许读）

表级写锁的演示

• Session 5 中， select * from tb1; 正常执行
• Session 6 中，select * from tlock1;、delete from tlock1 where id=7;、update tlock1 set name='liu' where id=15;、insert into tlock1 values(20,'m'); 都会阻塞

自增锁

自增锁：特殊的表锁，顾名思义，针对自增长列。当 A 事务中插入包含了自增长列（auto_increment）的新增数据时，会持有自增锁，若 B 事务执行 insert 操作包含了自增长列的数据，需要等待 A 事务的释放。自增锁有几种工作模式，由系统变量 innodb_autoinc_lock_mode 的值控制，MySQL 8 中，其值默认为 2，即交叉模式。

binlog 的三种格式：

• ROW，默认格式，不记录具体的 SQL 语句，仅保存 SQL 语句执行成功后的每一行数据修改。该格式的特点是记录非常详细，占用的存储空间大，但是它能解决主从同步数据不一致的问题
• STATEMENT，以 SQL 语句的形式保存修改数据，SQL语句可以是 DDL、DML 等
• MIXED，前两者的混合

MDL（元数据锁）

MDL 的作用是为了保证读写的正确性，其加锁过程由 MySQL 自动控制，通常在访问一张表时会自动加上该锁。

• 当对一张表的数据执行增删改查（insert、update、delete、select）操作时，加 MDL 读锁
• 当对表的结构进行变更时（比如加字段、删字段、修改字段名、修改字段的数据类型、修改约束、修改表名等操作），加 MDL 写锁

MDL 读锁与 MDL 读锁之间不冲突，但 MDL 读锁与 MDL 写锁是冲突的，见下面的操作：

• Session 7 中，select * from tlock1; 自动加上 MDL 读锁
• Session 8 中，alter table tlock1 add comments varchar(10); 会阻塞，因为 MDL 读锁与 MDL 写锁之间是冲突的
• Session 9 中，select * from tlock1; 正常运行
• Session 9 中，insert into tlock1 values(20,'ki'); 正常运行，因为 MDL 写锁之间不冲突

• Session 9 中，show processlist\G; 的输出如下：

  show processlist\G;
  
  *************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
  Command: Daemon
   Time: 3090
  State: Waiting on empty queue
   Info: NULL
  *************************** 2. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: locks
  Command: Sleep
   Time: 106
  State:
   Info: NULL
  *************************** 3. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: locks
  Command: Query
   Time: 4
  State: Waiting for table metadata lock
   Info: alter table tlock1 add comments varchar(10)  ←← 这是 Session 8 中的阻塞
  *************************** 4. row ***************************
     Id: 20
   User: root
   Host: localhost
     db: locks
  Command: Query
   Time: 0
  State: init
   Info: show processlist
  4 rows in set, 1 warning (0.00 sec)

赞赏