MySQL事务-锁分类
# 常用 SQL
-- 查询 InnoDB 存储引擎的状态信息，包括当前的锁记录
SHOW ENGINE INNODB STATUS;
-- MySQL 8 查询锁记录
SELECT * from PERFORMANCE_SCHEMA.DATA_LOCKS;
SELECT ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,EVENT_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from PERFORMANCE_SCHEMA.DATA_LOCKS;
-- MySQL 8 查询 metadata locks
SELECT * from PERFORMANCE_SCHEMA.METADATA_LOCKS;
-- MySQL 5.7
-- 查询当前正在被阻塞的锁
SELECT wait_started, locked_table, locked_index, locked_type, waiting_trx_id, waiting_lock_mode, waiting_trx_started, waiting_trx_rows_locked, waiting_trx_rows_modified, waiting_pid, waiting_query, waiting_lock_id, blocking_trx_id, blocking_pid, blocking_lock_mode FROM sys.innodb_lock_waits;
select * from INFORMATION_SCHEMA.INNODB_TRX\G
select * from INFORMATION_SCHEMA.INNODB_LOCKS\G
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
-- 事务要获取某行记录的共享锁时，会自动添加表的共享意向锁（IS）
SELECT ... LOCK IN SHARE MODE;
-- 事务要获取某行记录的排他锁时，会自动添加表的排他意向锁（IX）
SELECT ... LOCK FOR UPDATE;
# 数据准备
CREATE TABLE `t2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) unsigned DEFAULT 0,
`nation` varchar(30) NOT NULL DEFAULT '',
`city` varchar(40),
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
index idx_age(age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t2` (`id`, `name`, `age`, `nation`, `city`)
values
(1, 'xiaoming', 10, 'China', 'Beijing'),
(3, 'huahua', 13, 'Japan', 'Shanghai'),
(8, 'jack', 32, 'United States', 'New York'),
(11, 'liubei', 13, 'China', 'Wuhan'),
(13, 'wusong', 25, 'Japan', 'Beijing'),
(15, 'sunwukong', 11, 'China', 'Nanjing');
# 表级锁
# 间隙锁（Gap Lock）
# 为什么要用间隙锁？
在 InnoDB 存储引擎中主要用于解决幻读问题，例如我们要基于某个条件查询一段区间的数据，要求在查询期间不希望此段数据的数据集发生变化。这种情况下加行锁会存在几个问题：1. 行锁比较重，每条记录都要加锁。2. insert 操作是插入一条新数据，没法提前加行锁，锁不了 insert 操作。所以要保证区间数据的一致性，间隙锁应运而生。
# 间隙锁特点
- 间隙锁其实是一种特殊的共享锁（Shared Lock），允许多个事务同时获取并保持这些锁。允许多个事务读取数据，防止其他事务获取排他锁（Exclusive Lock）来修改数据。
- 算是行锁
# 场景复现
# 临键锁（Next Key Lock）
加锁的基本单位是临键锁 (Next Key Lock)，临键锁的规则是前开后闭的区间
执行过程中扫描到的记录都会加锁
-- 事务 A begin; select * from t2 where nation = 'Japan' lock in share mode; -- nation 并不是索引，此查询需要扫描全表 +----+--------+------+--------+----------+---------------------+ | id | name | age | nation | city | updated_at | +----+--------+------+--------+----------+---------------------+ | 3 | huahua | 13 | Japan | Shanghai | 2023-08-27 15:01:20 | | 12 | wusong | 25 | Japan | Beijing | 2023-08-27 15:01:20 | +----+--------+------+--------+----------+---------------------+ -- 事务 B begin; update t2 set name = 'Name1' where id = 1; -- 此时会被阻塞 -- 查询当前锁状态，所有被扫描到的记录都被加了共享锁 SELECT ENGINE_TRANSACTION_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from PERFORMANCE_SCHEMA.DATA_LOCKS; +-----------------------+------------+-----------+---------------+-------------+------------------------+ | ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+------------+-----------+---------------+-------------+------------------------+ | 2348 | NULL | TABLE | IX | GRANTED | NULL | | 2348 | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 15 | | 421791203597528 | NULL | TABLE | IS | GRANTED | NULL | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 3 | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 8 | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 11 | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 12 | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 15 | | 421791203597528 | PRIMARY | RECORD | S | GRANTED | 1 | +-----------------------+------------+-----------+---------------+-------------+------------------------+对于索引上的等值查询，如果查询条件是唯一索引（包括主键索引），那么临键锁会退化成记录锁 (Record Lock)，如果查询的记录不存在则会退化成间隙锁
-- 事务 A begin; -- id 是主键索引，且 id=8 的数据存在 update t2 set name = 'Name8' where id = 8; -- 新开一个 session 来查询当前锁 SELECT ENGINE_TRANSACTION_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from PERFORMANCE_SCHEMA.DATA_LOCKS; +-----------------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+------------+-----------+---------------+-------------+-----------+ | 4373 | NULL | TABLE | IX | GRANTED | NULL | | 4373 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 8 | +-----------------------+------------+-----------+---------------+-------------+-----------+ -- 此时在 id=8 这条数据上加了行锁（Record），并且不是间隙锁，所以这只是加了这一条记录的排他锁 -- 事务 B begin; -- 事务 B 同样来更新，此时事务被阻塞 update t2 set name = 'Name9' where id = 8;对于索引上的等值查询，会继续扫描到第一个不符合条件的记录值，转换为间隙锁 (Gap Lock)
事务 A 按 age 做等值查询（age有索引，不唯一），查询
age = 13这条数据，表中有两条数据符合条件的数据begin; update t2 set name = 'test age' where age = 13;此时来观察一下锁的情况
观察
INDEX_NAME和LOCK_DATA这两列，例如第二行的13, 3，索引用的是ids_age，所以13代表的是age = 13，3代表的是id = 3，可以理解为这是二级索引数据标识，以此定位到一条数据。基于以上，可以看到
id = 3 和 id = 11这两行都加了排他锁，同时由于age是非唯一索引，所以会继续查询到下一条不符合记录的数据为止都加上间隙锁，这里需要注意，由于是通过age索引来查询的，索引树是以age来排序的，所以这个间隙锁锁定的区间是按age来锁定的，并不是通过主键id来锁定的！本例中可以观察到最后一行有个
GAP间隙锁，锁定的边界是age = 25，范围是age (13, 25)
开启事务 B，观察间隙锁情况
-- 不在 age (13,25) 锁定区间，插入时不会被阻塞 insert into `t2` (`id`, `name`, `age`, `nation`, `city`) values (12, 'Name12', 11, 'China', 'Beijing'); -- 如果 age 改成 14，此时插入会被阻塞 insert into `t2` (`id`, `name`, `age`, `nation`, `city`) values (12, 'Name12', 14, 'China', 'Beijing');
唯一索引上的范围查询，会访问到不满足条件的第一个值为止
# 意向锁（Intention Lock）
意向锁属于表级锁，表示事务要准备给表中的行加哪种类型的锁（共享或者独占）。主要用于解决行锁和表锁之间的冲突问题，提高并发，减少行锁扫描次数。
- 意向锁之间互相兼容
- 意向锁只会跟表级锁发生冲突，不会跟行级锁发生冲突
- 更新数据时，如果查询条件不是索引，将会走全表扫描，此时会去获取排他意向锁（IX）
alter table add index 加索引时，会获取 metadata lock
此时执行 update 数据失败，执行 select 查询正常
alter table drop index 删除索引时，会获取 metadata lock
但是此时 更新 查询数据都没问题
上次更新: 2024/11/05, 03:15:29