MySQL数据库中的行锁

前言

最近在阅读《MySQL技术内幕 InnoDB存储引擎》一文，发现文中对InnoDB中锁的描述和网上大多数博客相差甚远，因此查阅了大量资料，结合实际测试后，写下了这篇文章。

如有不同看法，欢迎在评论区留言讨论。

一、共享锁与排他锁

在介绍MySQL中的行锁之前，首先要了解什么是共享锁与排他锁。

排他锁(X Lock)

排他锁，也称写锁，允许获取锁的事务更新或删除数据。
当前事务获取到排他锁后，禁止其他事务获取该排他锁，禁止其他事务获取该数据的共享锁。
获取排他锁时，需要等待其他事务释放共享锁或排他锁。

共享锁(S Lock)

共享锁，也称读锁，允许获取锁的事务读取数据。
当前事务获得共享锁时，其他事务也可以获取该共享锁，但禁止其他事务获取该数据的排他锁。
获取共享锁时，需要等待其他事务释放排他锁。

二、获取行锁的方式

InnoDB中，行锁支持共享锁和排他锁。
如果需要手动加锁或查询时加锁，使用如下代码:

-- 获取行级别的共享锁
-- 在结尾加上 lock in share mode 表示给该行加共享锁
select ... lock in share mode;

-- 获取行级别的排他锁
-- 在结尾加上 for update 表示给该行加排他锁
select ... for update;

此外，对数据做新增、删除或修改时，自动会加上排他锁。

三、行锁的算法

InnoDB中行锁有3种算法，值得注意的是，无论使用哪种锁算法，都支持共享锁和排他锁，获取锁的方式也都采用2.1中介绍的方式。
记录锁(Record Lock)：单个行记录上的锁。
间隙锁(Gap Lock)：锁定一个范围，但不包含记录本身。
临键锁（Next-Key Lock）：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

在不同的隔离级别下，MySQL使用的锁算法不一定相同，下面主要说明可重复读隔离级别下使用的锁算法。

3.1 隔离级别为可重复读（REPEATABLE READ）

MySQL的默认数据隔离级别为可重复读（REPEATABLE READ），在该隔离级别下，锁算法默认使用临键锁算法（Next-Key Lock）。

3.1.1 对唯一索引加锁

where条件中存在唯一索引时，临键锁会退化成记录锁，只对满足条件的记录加锁，包括满足条件但不存在的记录。

3.1.2 对非唯一索引加锁

where条件中不存在唯一索引时，对满足条件的记录加锁，也对直到下一个不满足条件的记录的范围区间加锁，不包含下一个不满足条件的记录。

3.2 隔离级别为读可提交（READ COMMITTED）

在隔离级别设置为读可提交（READ COMMITTED）时，所算法使用记录锁算法（Record Lock）。

四、实际案例

在 可重复读（REPEATABLE READ） 隔离条件下，创建一个表z，其中有唯一索引a，和非唯一索引b。

create table z(a int,b int,primary key(a),key(b));
insert into z SELECT 1,1;
insert into z SELECT 3,1;
insert into z SELECT 5,3;
insert into z SELECT 7,6;
insert into z SELECT 10,8;

4.1 测试唯一索引加锁

事务1，当对唯一索引a=3加锁时，只会对a=3的索引加锁。

begin;
select * from z where a=3 for update;

查看锁信息，可以发现，对主键为3的数据加上了记录锁(Record Lock)。

SELECT * FROM performance_schema.data_locks;

事务2，再次对a=3加锁，发现SQL处于等待状态，等待事务1释放锁。

select * from z where a=3 for update;

事务2，插入一条a=4的数据，发现可以正常插入，说明未产生间隙锁。

insert into z select 4,2;

提交事务1，删除a=4的数据，使数据库恢复原状

commit;
delete from z where a=4;

4.2 测试非唯一索引加锁

接下来测试使用非唯一索引b加锁，产生临键锁。
事务1，对b=3的数据加锁。

begin;
select * from z where b=3 for update;

查看锁信息，可以发现，加锁的区间是(b=3,a=5 ~ b=6,a=7)之间，此外主键a=5被加上了记录锁，所以完整的临键锁区间为[b=3,a=5 ~ b=6,a=7)。
SELECT * FROM performance_schema.data_locks;

因为被锁的区间为[b=3,a=5 ~ b=6,a=7),所以对该范围内的数据加锁都会失败。
事务2，
对b=3的数据加共享锁，发现SQL阻塞，等待锁释放。
插入b=3,a=6的数据，发现SQL阻塞，等待锁释放。
插入b=6,a=6的数据，发现SQL阻塞，等待锁释放。
插入b=4,a=11的数据，发现SQL阻塞，等待锁释放。

select * from z where b=3 lock in share mode;
insert into z select 6,3;
insert into z select 6,6;
insert into z select 11,4;

事务2，对锁区间[b=3,a=5 ~ b=6,a=7)范围外的数据加锁，可以正常执行。
插入b=0,a=2的数据，成功执行。
插入b=6,a=8的数据，成功执行。
插入b=7,a=6的数据，成功执行。

insert into z select 2,0;
insert into z select 8,6;
insert into z select 6,7;

此外，InnoDB中执行插入语句时，会加上插入意向间隙锁，该锁会锁住插入数据到下一个存在记录的区间，包括下一个存在的记录，如果该区间被锁定，则阻塞插入操作。
在该案例中，如果往(b=1,a=3~b=3,a=5]区间内插入数据，就需要获取(b=1,a=3~b=3,a=5]区间的插入意向间隙锁。
因此在(b=1,a=3~b=3,a=5]的区间内，也会阻塞插入操作。
事务2，
插入b=1,a=4的数据，发现SQL阻塞，等待锁释放。
插入b=2,a=2的数据，发现SQL阻塞，等待锁释放。
插入b=3,a=4的数据，发现SQL阻塞，等待锁释放。

insert into z select 4,1;
insert into z select 2,2;
insert into z select 4,3;

但在(-∞~b=1,a=3]之间插入数据就可以正常执行。
插入b=1,a=2的数据，成功执行。
插入b=0,a=6的数据，成功执行。

insert into z select 2,1;
insert into z select 6,0;

该案例中，整个禁止插入的间隙为(b=1,a=3~b=6,a=7)。

2.3 如何避免间隙锁

可以看到，如果产生间隙锁，将会大面积阻塞插入操作，因此应该尽量避免间隙锁。那么有哪些避免间隙锁的方法呢？

1. 避免手动加锁，如需加锁，则对唯一索引加锁，使临键锁降级为记录锁。
2. 修改隔离级别为读可提交（READ COMMITTED），此时不会产生临键锁，但会出现幻读现象。

五、查看锁信息

5.1 查看事务信息

SELECT * from information_schema.INNODB_TRX;

字段名	说明
trx_id	InnoDB中唯一的事务ID
trx_state	当前事务的状态，LOCK WAIT表示在等待锁
trx_started	事务的开始时间
trx_requested_lock_id	等待事务的锁ID
trx_wait_started	事务等待开始时间
trx_weight	事务的权重，反映一个事务修改和锁住的行数，当数据库死锁发生回滚时，InnoDB选择该值最小的回滚
trx_mysql_thread_id	MySQL中的线程ID，SHOW PROCESSLIST中显示的结果
trx_query	事务运行的SQL语句

对于MySQL8.0以上，查看锁信息。

5.2 查看数据锁信息

SELECT * FROM performance_schema.data_locks;

字段名	说明
ENGINE	存储引擎
ENGINE_LOCK_ID	存储引擎持有或请求的锁的ID
ENGINE_TRANSACTION_ID	请求锁的事务的存储引擎内部ID
THREAD_ID	线程ID
EVENT_ID	事件ID
OBJECT_SCHEMA	包含锁定表的模式
OBJECT_NAME	锁定的表的名称
PARTITION_NAME	锁定的分区名称
SUBPARTITION_NAME	锁定的子分区名称
INDEX_NAME	锁定的索引的名称
OBJECT_INSTANCE_BEGIN	锁在内存中的地址
LOCK_TYPE	事务运行的SQL语句
LOCK_MODE	锁的模式
LOCK_STATUS	锁请求的状态
LOCK_DATA	与锁相关联的数据。在主键索引上放置锁时，显示被锁记录的主键值。在次级索引上放置锁时，显示被锁记录的次级索引值，并附加上主键值

5.3 查看数据锁等待信息

SELECT * FROM performance_schema.data_lock_waits;

参考资料

《MySQL技术内幕 InnoDB存储引擎》