[数据库] 排查MySQL锁表情况及解决思路-指挥学院-公会大作战活动集结号

[数据库] 排查MySQL锁表情况及解决思路 In 指挥学院 @2025-10-26 15:54:35

0 MYSQL数据库的基础：事务、锁

事务

事务的开启、提交、回滚

在默认情况下，用户执行的每一条SQL语句都会被当成单独的事务【自动提交】。

【开启事务】：如果要将一组SQL语句作为一个事务，则需要先执行以下语句显式地开启一个事务。

START TRANSACTION;

-- 或 BEGIN;

【提交事务】：上述语句执行后，每一条SQL语句不再自动提交，用户需要使用以下语句手动提交，只有事务提交后，其中的操作才会生效。

COMMIT;

【回滚事务】：如果不想提交当前事务，可以使用如下语句取消事务（即回滚）。

ROLLBACK;

事务的四大特性:ACID

原子性: Atom

原子性(Atomicity)是指一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。

事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态。

一致性: Consist

一致性(Consistency)是指在事务处理时，无论执行成功还是失败，都要保证数据库系统处于一致的状态，保证数据库系统不会返回到一个未处理的事务中。

MySQL中的一致性主要由日志机制实现，通过日志记录数据库的所有变化，为事务恢复提供了跟踪记录。

隔离性: Isolation

隔离性（Isolation)是指当一个事务在执行时，不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离，直到事务完成为止，才能看到事务的执行结果。

隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性: Durable

持久性(Durability)是指事务一旦提交，其对数据库的修改就是永久性的。

需要注意的是，事务的持久性不能做到百分百的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所有提交的数据可能都会丢失。

并发事务引起的问题

脏读：

一个事务读取到另个事务还没有提交的数据

不可重复读：

一个事务分两次读取某个数据，前后两次读取数据不一致

幻读：

一个事务读取某个数据时，并没有该数据，但插入时发现已经存在

事务隔离级别

读未提交（脏读）

READ UNCOMMITTED 是事务中最低的级别，在该级别下的事务可以读取到其他事务中未提交的数据，这种读取的方式也被称为脏读（Dirty Read）。简而言之，脏读是指一个事务读取了另外一个事务未提交的数据。

读已提交（不可重复读）

READ COMMITTED 是大多数 DBMS (如 SQL Server、Oracle) 的默认隔离级，但不包括MySQL。

在该隔离级下只能读取其他事务已经提交的数据，避免了脏读数据的现象。但是在该隔离级别下，会出现不可重复读（NON-REPEATABLE READ）的问题。

可重复读

REPEATABLE READ 是MySQL的默认事务隔离级，它解决了脏读和不可重复读的问题，确保了同一事务的多个实例在并发读取数据时，会看到同样的结果。但在理论上，该隔离级会出现幻读（PHANTOM READ)的现象。

幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不可重复读有些类似，同样发生在两次查询过程中。不同的是，幻读是由于其他事务做了插入记录的操作，导致记录数有所增加。不过，MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读的问题。

串型化

SERIALIZABLE 是最高级别的隔离级，它在每个读的数据行上加锁，使之不会发生冲突，从而解决了脏读、不可重复读和幻读的问题。

但是由于加锁可能导致超时（Timeout) 和锁竞争（Lock Contention)现象，因此 SERIALIZABLE 也是性能最低的一种隔离级。除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级。

查看事务隔离级别

# 查看全局隔离级

SELECT @global.transaction_isolation;

-- REPEATABLE-READ / 。。。

# 查看当前会话中的隔离级

SELECT @@session.transaction_isolation;

# 查看下一个事务的隔离级

SELECT @@transaction_isolation;

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE

如：

-- set session TRANSACTION ISOLATION level REPEATABLE READ

-- set session TRANSACTION ISOLATION level READ UNCOMMITTED

MYSQL 的锁类型

按用锁的风险倾向划分：悲观锁、乐观锁

对于一次的数据修改，我们可以大概将其分为3步。

获取数据

修改数据

提交修改

这里假设A、B两个角色对数据进行修改。

乐观锁

乐观锁( Optimistic Locking ) : 对数据保持一个乐观态度（大概率不会有人和我抢）。

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。

【举例】 A角色获取数据、修改数据时不会对数据加锁，只有在提交修改时才会判断中间是否有其他人对数据进行了修改。（因为没有对数据加锁，B角色可在A角色获取数据后，也获取到数据，甚至比A提前提交数据修改）。

乐观锁的实现方式：基于数据版本( Version )记录机制实现（占绝大多数情况）

乐观锁都是从应用系统层面进行并发控制，常用的是版本号方式和时间戳方式。

何谓数据版本?

即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。

读取出数据时，将此版本号一同读出；

之后更新时，对此版本号 + 1。

此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

进一步的优化方案，比如：可以给表加一个version字段，先查询version字段放在缓存里，每次修改之前，在查询一次version字段，若跟缓存里的数值不一致，则回滚。

例如：A角色在获取数据阶段，获取到的版本号为V1，则在提交修改阶段的执行语句就为

update ....... where version = V1

这样当数据在中途被B角色修改后，version字段将不为原来的V1，此条更新语句也就失效了。不会导致数据更新异常。

乐观锁的优点

避免了长事务中的数据库加锁开销(操作员 A和操作员 B 操作过程中，都没有对数据库数据加锁)，大大提升了大并发量下的系统整体性能表现。

乐观锁的缺点

乐观锁机制往往基于系统中的数据存储逻辑，因此也具有一定的局限性

由于乐观锁机制是在我们的应用系统中实现，来自外部系统的用户余额更新操作不受我们系统的控制。因此，可能会造成脏数据被更新到数据库中。

在系统设计阶段，我们应该充分考虑到这些情况出现的可能性，并进行相应调整(如将乐观锁策略在数据库存储过程中实现，对外只开放基于此存储过程的数据更新途径，而不是将数据库表直接对外公开)。

悲观锁

悲观锁对数据保持一个悲观态度（绝逼会有人来和我抢），则在角色A获取数据后，就对数据进行了加锁，直到修改数据、提交修改后才将锁释放。

总是觉得数据随时被人更改，此次操作会对数据造成冲突，与java里的sychronized和reentrantlock悲观锁类似，效率比较低。

悲观锁是从数据库层面做并发控制，这个应该是真正意义的加锁，分为共享锁和排他锁。

虽然可以加共享锁，但是mysql的update、insert、delete自动加了排他锁。

使用悲观锁，需要关闭mysql的auto_commit模式语句后面加 for update。

乐观锁的实现方式：基于数据库的锁机制实现（绝大多数情况下）

基于数据库的锁机制实现，以保证操作最大程度的独占性。

但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受————而*乐观锁机制**在一定程度上解决了这个问题。

按锁的级别(影响的数据量)划分

从锁的影响数据量划分，MySQL中使用比较多的有：表锁、行锁和间隙锁。

按锁的读/写/共享与否划分

MySQL 中有以下几种常见的锁定类型：

排他锁 (X)：防止其他会话读写表中的任何数据。

日志中的关键词一般是：... lock_mode X locks rec ...

共享锁 (S)：允许其他会话读取表中的数据，但不能写入。

意向写锁 (IX)：表示该会话打算在未来获得排他锁。

意向共享锁 (IS)：表示该会话打算在未来获得共享锁。

共享锁、排他锁的【锁对象】是主键

共享锁、排他锁的【针对的表引擎】是InnoDB

如果是MyISM不是这样的锁机制。

锁表试验

假设:表结构及表数据

表结构

-- show create table test.tb_student

-- drop table `test`.`tb_student`

CREATE TABLE `test`.`tb_student` (

`id` int(11) NOT NULL COMMENT '数据库ID',

`id_card` varchar(20) NOT NULL COMMENT '身份证号码',

`name` varchar(50) NOT NULL COMMENT '姓名',

`sex` varchar(4) DEFAULT 'U' COMMENT '性别',

`birthdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '出生年月',

`grade` int(11) DEFAULT NULL COMMENT '年级',

`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

-- CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

create unique index idx_student_idcard on test.tb_student(`id_card`); -- 创建唯一索引

表的初始化数据

INSERT INTO test.tb_student (id,id_card, name,sex,birthdate,grade,create_time,update_time) VALUES

(1, '110101199908281112', 'jane', NULL,'1994-01-01 00:00:00',12,'2023-09-16 17:08:00','2023-09-16 17:08:00');

INSERT INTO test.tb_student (id,id_card, name,sex,birthdate,grade,create_time,update_time) VALUES

(2, '110101200404132389', 'jack',NULL,'1999-09-01 00:00:00',12,'2024-09-06 19:06:04','2024-09-06 19:06:56');

上锁方式1：SELECT ... FOR UPDATE (别称：写锁 | 排他锁/IX锁=意向排他锁 | 行锁、表锁 | 悲观锁)

上锁方法

这个句型允许你在事务中锁定选中的行，以防止其他事务对这些行进行更新或删除，直到当前事务结束（提交或回滚）。这对于保持数据的一致性和完整性特别有用，特别是在处理涉及多个步骤的操作时。

此操作会锁定返回的记录，直到事务结束。

START TRANSACTION; -- 开始一个事务

SELECT * FROM your_table WHERE condition LIMIT 1 FOR UPDATE; -- 上锁

-- COMMIT; -- 提交事务

-- 或者

-- ROLLBACK; -- 回滚事务

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)

即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。

如果不存在一致性非锁定读的话(读取快照)，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)

select ... for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读(常规的select)

排他锁，不能被多个事务共享，如果一个事务获取了一行数据的排他锁，那么其他事务就不能获取这行数据的锁，包括共享锁和排他锁。

获取到排他锁的事务，可以对数据进行读取和修改，事务提交后，锁释放。

Demo 1

step1 会话窗口1

START TRANSACTION; -- 开始一个事务

-- begin; -- begin 完全等效于 START TRANSACTION

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 FOR UPDATE; -- 上锁

step2 会话窗口2

-- SELECT * FROM test.tb_student WHERE 1=1 and id='2' LIMIT 1 FOR UPDATE; -- 可以获取到 id = '2' 的锁

-- 不论会话窗口1 先开启的事务无论是否有 commit 或 rollback ，查询 id !='1' 的行数据时，因没有被加行锁，会立即返回数据

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 FOR UPDATE; -- 无法获取到锁

-- 在会话窗口1 先开启的事务没有 commit 或 rollback 之前，查询 id='1' 的行数据时，将始终处于等待状态，不会返回数据

-- SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1; -- 没有申请获取锁(for update)，自然不会被阻塞

-- 不论会话窗口1 先开启的事务无论是否有 commit 或 rollback ，查询 id='1' 的行数据时，因本语句没有尝试加锁，会立即返回数据 | 测试版本 : mysql - 5.7.38-220701-log

-- SELECT * FROM test.tb_student -- 没有申请获取锁(for update)，自然不会被阻塞

-- 不论会话窗口1 先开启的事务无论是否有 commit 或 rollback ，查询全表的行数据时，因本语句没有尝试加锁，会立即返回数据 | 测试版本 : mysql - 5.7.38-220701-log

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 lock in share mode -- 申请获取共享锁(lock in share mode)，被阻塞中（直至会话窗口1 的排它锁被释放）

delete from test.tb_student WHERE 1=1 and id='1'; -- 在会话窗口1 先开启的事务没有 commit 或 rollback 之前，删除 id='1' 的行数据时，将处于持续等待状态，不会做任何响应

delete from test.tb_student WHERE 1=1 ; -- 在会话窗口1 先开启的事务没有 commit 或 rollback 之前，删除全表的行数据时，将始终处于等待状态，不会做任何响应

等待超时后的报错信息：SQL 错误 [1205] [40001]: Lock wait timeout exceeded; try restarting transaction

Demo 2

假设有一个名为 accounts 的表，包含 id 和 balance 两个字段，想从账户 A 转账到账户 B，同时确保这两个账户的余额在转账过程中不会被其他事务修改：

START TRANSACTION;

-- 锁定账户A和账户B的余额

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

-- 检查余额，执行转账逻辑...

COMMIT;

使用场景:

银行转账：从一个账户转移到另一个账户时，需要确保两个账户的余额在转账过程中不会被其他事务修改。

库存更新：在电子商务系统中，当你从库存中减去一个商品的数量时，需要确保没有其他事务也在尝试修改这个数量。

注意事项

锁定范围：FOR UPDATE 会锁定满足 WHERE 子句条件的所有行。如果 WHERE 子句没有指定条件时，那么会锁定整个表。

WHERE 子句影响加锁的情况：

主键字段：加行锁

唯一索引字段：加行锁

普通索引字段：加行锁

主键范围：加多个行锁

普通字段：加表锁

查询空数据：不加锁

详情参见： select...for update到底是加了行锁，还是表锁？ - 博客园/苏三说技术、select ... for update学习 - 博客园

隔离级别：不同的数据库事务隔离级别会影响 FOR UPDATE 的行为。例如，在 READ UNCOMMITTED 隔离级别下，FOR UPDATE 可能不会按预期工作。

死锁当两个或多个事务相互等待对方释放锁定的资源时，就会发生死锁。在使用 FOR UPDATE 时，需要注意避免死锁的发生。

性能影响：锁定行会影响数据库的并发性能，因为它限制了其他事务对这些行的访问。因此，应该谨慎使用 FOR UPDATE，并确保只在必要时使用。

InnoDB 存储引擎：FOR UPDATE 只在支持行级锁定的存储引擎（如 InnoDB）中有效。MyISAM 存储引擎不支持行级锁定，因此 FOR UPDATE 在 MyISAM 表中不会按预期工作。

上锁方式2：select ... lock in share mode (别称：读锁 | 共享锁/IS锁=意向共享锁 | 行锁、表锁 | 悲观锁)

上锁方法

select ... from ... lock in share mode;

例如，select，当上锁之后，另一个线程只可以读，不可以修改。

本上锁方式，走的是IS锁(意向共享锁)

即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录，需直到你这个加锁的session执行完成(否则，直接锁等待超时)。

多个事务共享一把锁，但是只能读，不能修改。

当有事务拿到共享锁的时候、且锁未释放时，另一个事务不能去修改加锁的数据。

共享锁的最大特点是可以共享，多个事务可以同时获取锁、且读到数据；

且在锁没有释放时，数据不能被修改，这样可以避免数据库脏读和不可重复读的问题。

Demo 1

会话窗口1

START TRANSACTION; -- 开始一个事务

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 lock in share mode -- 申请获取共享锁(lock in share mode)成功，并上锁

-- COMMIT; -- 提交事务

-- 或者

-- ROLLBACK; -- 回滚事务

会话窗口2

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 FOR UPDATE; -- 无法获取到排他锁(for update)，因为共享锁已经上锁了 ———— 导致阻塞等待，直至会话窗口1的共享锁被释放

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 lock in share mode -- 申请获取共享锁(lock in share mode)成功，并上锁

SELECT * FROM test.tb_student WHERE 1=1 and id='2' LIMIT 1 lock in share mode -- 申请获取共享锁(lock in share mode)成功，并上锁

SELECT * FROM test.tb_student -- 没有申请获取(任何)锁，自然不会被阻塞

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1; -- 没有申请获取(任何)锁，自然不会被阻塞

上锁方式3：LOCK TABLES ... READ/WRITE(MDL读锁/MDL写锁)

MySQL里面表级别的锁有两种：

一种是表锁，

一种是元数据锁（meta data lock，MDL）

MDL锁的上锁语法:

lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

START TRANSACTION; -- 开始一个事务

-- begin; -- begin 完全等效于 START TRANSACTION

-- 表锁的语法 : lock tables t1 read,t2 write, ...;

LOCK TABLES test.tb_student READ;

-- LOCK TABLES test.tb_student WRITE;

-- 例如，下述语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许

-- lock tables t1 read,t2 wirte;

select * from test.tb_student

-- UNLOCK TABLES;

-- rollback;

-- commit;

MDL锁的解锁语法

方式1：利用unlock tables主动释放锁

unlock tables

方式2：在客户端断开连接的时候自动释放

方式3：主动杀会话

找到谁持有MDL写锁，然后把它kill掉

但是由于show processlist的结果里，sessionA的Command列是Sleep，导致查找起来很不方便

可以通过查询 sys.schema_table_lock_waits 这张表直接找出造成阻塞的 process id，把这个连接kill命令断开即可

（MySQL启动时需要设置 performance_schema=on，相比于设置为off会有10%左右的性能损失）

方式4：等 flush

select * from information_schema.processlist where id=1;

通过State列，查出来某个线程状态为Waiting for table flush

这个状态表示的是，现在有一个线程正要对表t做flush操作。MySQL里面对表做flush操作的用法，一般有以下两个：

flush tables t with read lock;

flush tables with read lock;

这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则：表示关闭MySQL里所有打开的表

所以，出现Waiting for table flush状态的可能情况是：有1个flush tables命令被别的语句堵住了，然后它又堵住了select语句

案例分析

Window 1: CREATE TABLE foo ( id int primary key );

Window 1: START TRANSACTION;

Window 1: SELECT * FROM foo; -- it doesn't matter that the table has no data

Window 2: DROP TABLE foo; -- notice it waits

Window 1: SHOW PROCESSLIST;

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

MDL锁，不需要显式使用，在访问一个表的时候会被自动加上。

MDL的作用

保证读写的正确性。

如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行

在MySQL5.5版本引入了MDL:

当对一个表做增/删/改/查操作的时候，加MDL读锁；

当要对表做结构变更操作的时候，加MDL写锁；

互斥情况

MDL读锁之间不互斥。

因此，可以有多个线程同时对一张表增删改查

MDL读写锁之间、写锁之间是互斥的

释放情况

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

参考文献

Mysql数据库管理-mysql锁管理 - CSDN

上锁方式1的补充：select ... for update no wait / select ... for update SKIP LOCKED (仅限于 : MYSQL 8.0+)

在 MySQL 中，FOR UPDATE 子句通常与 SELECT 语句一起使用，在事务中对选定的行进行锁定，以避免其他事务对这些行进行更新或删除。

但直到 MySQL 8.0 之前，并没有直接的方式来指定一个 "no wait" 选项，这意味着着如果尝试锁定的行当前被其他事务锁定，那么请求锁定的事务将会等待，直到行被释放为止。

从 MySQL 8.0 开始，引入了两个新的锁等待选项，允许开发者在处理锁定资源时具有更大的灵活性：

SKIP LOCKED ：如果行被其他事务锁定，则跳过这些行，继续处理未被锁定的行。

NOWAIT ：如果行被其他事务锁定，则立即返回一个错误，而不是等待。

START TRANSACTION; -- 开始一个事务

-- begin; -- begin 完全等效于 START TRANSACTION

SELECT * FROM test.tb_student WHERE 1=1 and id='1' LIMIT 1 FOR update NO WAIT; -- 上锁

要注意的是，虽然 SKIP LOCKED 是可用的，但直接的 NOWAIT 选项并没有作为 SELECT ... FOR UPDATE 语句的一部分直接提供。

不过，你可以通过其他方式模拟 NOWAIT 的行为，如设置事务的锁等待超时时间为非常短的时间（例如，0 毫秒），

但这实际上并不是一个精确的方法，因为即使设置了 0 毫秒的超时，MySQL 仍可能稍微等待一会儿才抛出超时错误。

如何模拟 NOWAIT 特性?

一个近似的 NOWAIT 行为可以通过设置会话级别的 innodb_lock_wait_timeout 为一个非常短的值（接近于 0 但不能是 0，因为 MySQL 不允许完全的即时错误返回）来实现。

例如：

SET SESSION innodb_lock_wait_timeout = 1;

BEGIN;

SELECT * FROM your_table WHERE your_condition FOR UPDATE;

COMMIT;

将 innodb_lock_wait_timeout 设置为 1（或尽可能短的值）可以模拟一个 "NOWAIT" 行为，因为它会让事务在几乎立即发现行被锁定时就放弃。

MYSQL 解锁表

如果锁表对数据库性能造成了影响，你可以使用如下命令来解锁表。

UNLOCK TABLES

注意，解锁表后，其他会话就可以对表中的数据进行修改了。

1 排查方法：查看当前锁表事务

由于出现的是锁表的问题，所以第一步从数据库入手，查看导致锁表的SQL语句是什么；查看是否锁表SQL语句；

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

该命令的输出结果包含了当前事务等待的锁资源的相关信息，包括事务ID、锁的类型、锁的模式、被锁定的对象（表、页等）以及锁的状态，而且也能看到具体执行的SQL以及事务的权重，反应一个事务修改和锁定的行数。

2 排查方法(扩展)

除了上面查询方式，MySQL还提供了很多查看方式，来查看表是否被锁定。以下是常用的几种方式：

方法一：使用SHOW OPEN TABLES命令

-- 针对`FLUSH TABLES `xxx_db`.`xxx_dimdevice` WITH READ lock;` FTWRL 锁，可通过如下2条语句查询到，目标表被锁时`In_Use`字段的值为`1`，反之为0

-- FTWRL 锁的解锁方式 : `UNLOCK TABLES;`

SHOW OPEN TABLES WHERE `Table` = 'table_name' AND `Database` = 'database_name';

SHOW OPEN TABLES where In_use > 0;

这个命令会返回一个结果集，其中包含了表的一些信息，比如表的状态，使用的存储引擎等等。如果表被锁定，那么状态字段会显示In_use。

方法二：使用SHOW PROCESSLIST命令

SHOW PROCESSLIST;

这个命令会返回当前MySQL服务器上所有的活动进程。这个命令将显示当前正在执行的所有会话信息，其中包括锁定的表。

如果表被锁定，那么可以通过查看这个进程列表来确定是否有进程正在使用该表。

可以检查State列中的信息，看是否有进程正在锁定该表。

在 "Info" 列中，你可以看到 "locked" 字样，如果为 "YES"，则表示该会话正在对表进行加锁。

表结构

mysql> select * from performance_schema.processlist\G;

*************************** 1. row ***************************

ID: 5

USER: event_scheduler

HOST: localhost

DB: NULL

COMMAND: Daemon

TIME: 21411

STATE: Waiting on empty queue

INFO: NULL

*************************** 2. row ***************************

ID: 714

USER: root

HOST: localhost

DB: performance_schema

COMMAND: Query

TIME: 0

STATE: executing

INFO: select * from performance_schema.processlist

2 rows in set (0.00 sec)

mysql> desc processlist;

+---------+-----------------+------+-----+---------+-------+

+---------+-----------------+------+-----+---------+-------+

| ID | bigint unsigned | NO | PRI | NULL | |

| DB | varchar(64) | YES | | NULL | |

+---------+-----------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

字段说明

连接标识符。这是在 SHOW PROCESSLIST 语句的 Id 列中显示的相同值，显示在 Performance Schema 线程表的 PROCESSLIST_ID 列中，并在线程内通过 CONNECTION_ID() 函数返回的值相同。

USER

发出该语句的 MySQL 用户。

system user 的值指的是由服务器生成的非客户端线程，用于在内部处理任务，例如延迟行处理线程或在复制主机上用于处理 I/O 或 SQL 的线程。

对于 system user，Host 列中没有指定主机。

unauthenticated user 指的是已与客户端连接关联但尚未对客户端用户进行身份验证的线程。

event_scheduler 指的是监视预定事件的线程。

HOST

发出该语句的客户端的主机名（对于 system user 则没有主机）。

对于 TCP/IP 连接，主机名以 host:port(主机地址:端口) 的格式显示，以便更容易确定哪个客户端在执行什么操作。

线程的默认数据库，如果未选择任何数据库，则为 NULL。

COMMAND

线程代表客户端执行的命令类型，如果会话处于空闲状态，则为 Sleep。此列的值对应于客户端/服务器协议的 COM_xxx 命令和 Com_xxx 状态变量。

TIME

线程在当前状态下已经经过的时间，以秒为单位。对于复制 SQL 线程，该值是上一个复制事件的时间戳与复制主机的实际时间之间的秒数。

STATE

一个动作、事件或状态，表示线程正在进行的操作。大多数状态对应非常快速的操作。如果一个线程在特定状态停留了很多秒，可能存在需要调查的问题。

INFO

线程正在执行的语句，如果没有执行语句则为 NULL。

该语句可以是发送给服务器的语句，或者如果该语句执行其他语句，则为最内层的语句。

EXECUTION_ENGINE

查询执行引擎。

该值可以是 PRIMARY 或 SECONDARY。

在 MySQL HeatWave Service 和 HeatWave 中使用，其中 PRIMARY 引擎是 InnoDB，而 SECONDARY 引擎是 HeatWave（RAPID）。

对于社区版本、企业版（本地部署）以及没有 HeatWave 的 MySQL HeatWave Service，该值始终为 PRIMARY。

COMMAND 字段值说明

COMMAND 字段说明

Binlog Dump 表示这是一个在复制源上的线程，负责将二进制日志内容发送到副本

Change user 表示该线程正在执行一个改变用户操作

Close stmt 该线程正在关闭一个 prepared statement.

Connect 被连接到源的复制接收线程和复制工作线程使用。

Connect Out 一个副本正在连接到其源。

Create DB 正在执行一个创建数据库操作

Daemon 该线程是服务器内部的线程，而不是为客户端连接提供服务的线程

Debug 线程正在生成调试信息

Delayed insert 该线程是一个延迟写入处理程序

Drop DB 该线程正在执行删除数据库操作

Error 错误

Execute 线程正在执行 prepared statement

Fetch 线程正在获取 prepared statement 的结果

Field List 线程正在检索表列的信息

Init DB 该线程正在选择默认数据库

Kill 正在杀死另外一个线程

Long Data 线程正在检索执行准备语句的结果中的长数据

Ping 该线程正在处理服务器 ping 请求

Prepare

Processlist

Query Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.

Quit The thread is terminating.

Refresh The thread is flushing table, logs, or caches, or resetting status variable or replication server information

Reset stmt The thread is resetting a prepared statement.

Set option The thread is setting or resetting a client statement execution option.

Shutdown The thread is shutting down the server.

Sleep The thread is waiting for the client to send a new statement to it.

Statistics The thread is producing server status information.

Time （目前未使用该状态）

详情参考

https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

STATE字段值说明

STATE 字段说明

Sleep：连接处于空闲状态，没有正在执行的命令。

Query：连接正在执行一个查询。

Locked：连接正在等待锁定资源。

Copying to tmp table：连接正在将结果复制到临时表中。

Sending data：连接正在发送查询结果给客户端。

Sorting result：连接正在对结果进行排序。

Waiting for table flush：连接正在等待表的刷新。

Repair by sorting：连接正在执行表的修复操作。

Creating sort index：连接正在创建排序索引。

详情参考

https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

推荐文献

MySQL 官网

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-processlist-table.html

MySQL 8.0 中 4 个默认的系统数据库 - CSDN

mysql8 PROCESSLIST - 51CTO

方法三：使用 INFORMATION_SCHEMA.INNODB_LOCKS

表结构

root@127.0.0.1 : information_schema 13:28:38> desc INFORMATION_SCHEMA.INNODB_LOCKS;

+————-+———————+——+—–+———+——-+

+————-+———————+——+—–+———+——-+

| lock_id | varchar(81) | NO | | | |#锁ID

| lock_mode | varchar(32) | NO | | | |#锁模式

| lock_type | varchar(32) | NO | | | |#锁类型

+————-+———————+——+—–+———+——-+

select * from information_schema.innodb_locks;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE `table_name` = 'table_name';

这个查询语句会返回InnoDB引擎的锁信息。如果表被锁定，你可以在结果集中找到相关的行。

方法四：使用SHOW ENGINE INNODB STATUS命令

-- 查看InnoDB引擎的状态

---- 1. BACKGROUND THREAD : 后台线程。

---- 2. SEMAPHORES

---- 3. LATEST DETECTED DEADLOCK : 死锁情况。这条命令将返回包含死锁信息的文本，对其搜索 `LATEST DETECTED DEADLOCK` 关键字，将显示最近检测到的死锁信息。若存在死锁，则：根据死锁信息进行分析，并采取相应措施来解决死锁问题。

---- 4. TRANSACTIONS : 事务情况。

---- 5. FILE I/O (插入缓冲线程、日志线程、读线程、写线程等)

---- 6. INSERT BUFFER AND ADAPTIVE HASH INDEX : 写缓冲和适应性哈希索引

---- 7. LOG : 日志

---- 8. LOG MIGRATION : 日志迁移

---- 10. BUFFER POOL AND MEMORY : 缓冲池和内存

---- 11. INDIVIDUAL BUFFER POOL INFO : 独立缓冲池信息

---- 12. ROW OPERATIONS : 行操作

SHOW ENGINE INNODB STATUS

-- or : SHOW ENGINE INNODB STATUS \G

这个命令会返回InnoDB引擎的状态信息。你可以在结果中查找TRANSACTIONS和LOCK WAIT字段来确定是否有事务正在等待表锁定。

方法五：使用 INFORMATION_SCHEMA/SYS.innodb_lock_waits 系统视图

表结构

root@127.0.0.1 : information_schema 13:28:56> desc INFORMATION_SCHEMA.innodb_lock_waits;

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SELECT * FROM sys.innodb_lock_waits; -- 网友 : 仅适用于MySQL 8.0及以上版本

这个查询会返回当前等待锁定的事务信息。如果表被锁定，你可以在结果集中找到相关的行。

方法六：使用INFORMATION_SCHEMA.INNODB_TRX表(所有活跃事务及事务加锁情况)

表结构

innodb_trx表 : 主要用于提供关于InnoDB存储引擎中当前所有活跃事务的信息。这个表对于监控和诊断数据库中的事务活动、识别长期运行的事务、检测死锁以及分析性能问题非常有帮助。

root@127.0.0.1 : information_schema 13:28:56> desc INFORMATION_SCHEMA.innodb_trx;

+—————————-+———————+——+—–+———————+——-+

+—————————-+———————+——+—–+———————+——-+

| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#

| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查

| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查

| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#

| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#

+—————————-+———————+——+—–+———————+——-+

通过查询information_schema.innodb_trx表，DBA或开发者可以快速定位到可能引起性能瓶颈或锁冲突的事务，进而采取相应的优化措施，比如终止长时间运行的事务或调整事务处理逻辑。例如，找出所有处于锁等待状态的事务

SELECT * FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

方法七：InnoDB表引擎的锁状态

SHOW STATUS LIKE '%innodb_row_lock%'

重要指标：等待平均时长、等待总时长、等待总次数

3 解决思路：治本之道

以上提供了一些查看问题的方式，那么既然出现了问题，就需要彻底根治，避免系统再次出现问题，针对系统本次出现的问题，从一下几个点进行了优化，具体如下：

(1) SQL 本身优化

对跑批SQL进行了执行计划分析，通过分析查看，发现有些关联表进行了全表查询，所以第一步先多查询速度进行优化，从查询时间上入手解决，通过多次执行计划分析，对进行了全表扫描的做关联关系分析，发现部分表有主键，但为未建索引；

因此从SQL做了以下优化：

1、建索引

CREATE INDEX index_name ON table_name (column_name);

2、减少子查询

3、添加where条件

4、查询条件避免使用函数、模糊搜索等查询效率较慢的查询方式

(2) 编码层面

由于处理的数据量比较多，数据来源比较复杂，来源多个表，所以将有些能抽出来的表抽出来，尽量放代码层面处理，通过代码逻辑控制；

本次优化只是从这几个方面优化，想有优化sql还是需要从sql的本身进行分析，知道执行顺序以及原理，执行原理可见如下文章：

一条SQL语句从开始到结束到底经历了什么？ - CSDN

4 解决方法：锁的释放(临时解决)

本次问题从sql，系统层面解决了，那么为能临时解决锁表，保证系统正常运行，先对导致锁表的事务进行释放，MySQL中锁的释放是自动进行的，当一个会话执行完相关操作后，所持有的锁会自动释放。不过，有些情况下我们可能需要手动释放锁，比如长事务或者死锁的处理。释放锁SQL语句如下：

A、ROLLBACK

当一个会话执行ROLLBACK语句时，所有该会话持有的锁都会被立即释放。例如：

START TRANSACTION;

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

ROLLBACK;

在这个例子中，执行ROLLBACK后，会话所持有的锁会被释放。

B、COMMIT

当一个会话执行COMMIT语句时，所有该会话持有的锁都会被释放。例如：

START TRANSACTION;

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

COMMIT;

在这个例子中，执行COMMIT后，会话所持有的锁会被释放。

C、显式调用UNLOCK TABLES

当一个会话调用UNLOCK TABLES语句时，会释放该会话持有的所有表级锁。例如：

写锁

LOCK TABLES table1 WRITE;

...

UNLOCK TABLES;

读锁

LOCK TABLES table1 READ;

...

UNLOCK TABLES;

在这个例子中，调用UNLOCK TABLES后，会话所持有的锁会被释放。

D、长事务的处理

在MySQL中，长事务是指持续运行时间较长的事务。长事务可能导致锁保持的时间较长，从而影响其他会话的并发访问能力。为了释放长事务持有的锁，可以使用以下方法：

执行ROLLBACK或者COMMIT语句来结束事务。

使用KILL命令终止会话，但这种方法可能会导致事务的回滚。

E、死锁的处理

当多个会话之间出现循环依赖的锁竞争关系时，就会发生死锁。

使用SHOW ENGINE INNODB STATUS命令可以查看死锁信息。例如：

SHOW ENGINE INNODB STATUS

在输出结果中的"TRANSACTIONS" / "LATEST DETECTED DEADLOCK" 部分，可以找到死锁的详细信息；

通过以上方式，从系统本身的编码，SQL语句，数据库表关键字做优化之后，锁表情况也彻底解决；

解决方法

方法1：MYSQL默认的处理机制。MySQL会自动检测到死锁，并选择一个会话进行回滚，以解除死锁。

方法2：分布式锁。

案例分析：基于 SHOW ENGINE INNODB STATUS : LATEST DETECTED DEADLOCK 信息

$ SHOW ENGINE INNODB STATUS

=====================================

2024-08-30 10:21:29 0x7f3526be6700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 3 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 23417696 srv_active, 0 srv_shutdown, 34619507 srv_idle

srv_master_thread log flush and writes: 58037203

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 14152829

OS WAIT ARRAY INFO: signal count 13712253

RW-shared spins 0, rounds 27060088, OS waits 13405032

RW-excl spins 0, rounds 5127571, OS waits 53061

RW-sx spins 122588, rounds 1863429, OS waits 1716

Spin rounds per wait: 27060088.00 RW-shared, 5127571.00 RW-excl, 15.20 RW-sx

------------------------

LATEST DETECTED DEADLOCK

------------------------

2024-04-22 14:10:35 0x7f3527636700

*** TRANSACTION:

TRANSACTION 100645602, ACTIVE 0 sec fetching rows

mysql tables in use 1, locked 1

47 lock struct(s), heap size 8400, 1784 row lock(s), undo log entries 5

MySQL thread id 61032357, OS thread handle 139866270820096, query id 2209943999 172.18.1.53 root updating

UPDATE realtime_job_instance SET record_status='0',

update_time='2024-04-22 14:10:35.274'

WHERE is_delete=0

AND (job_id = 1781265485 AND run_type = 2)

*** HOLDS THE LOCK:

RECORD LOCKS space id 3491 page no 75 n bits 80 index PRIMARY of table `xxx_realtime`.`realtime_job_instance` trx id 100645602 lock_mode X locks rec but not gap

Record lock, heap no 8 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

0: len 8; hex 98bbf5fa74190001; asc t ;;

1: len 6; hex 000005ffbae2; asc ;;

2: len 7; hex 750000028a1550; asc u P;;

3: len 8; hex 800000006a2bf44d; asc j+ M;;

4: SQL NULL;

5: len 15; hex 313931313332383932323730303139; asc 191132892270019;;

6: len 30; hex 006200300033006300610039003500300063006300330039003800620065; asc b 0 3 c a 9 5 0 c c 3 9 8 b e; (total 64 bytes);

7: SQL NULL;

8: len 1; hex 32; asc 2;;

9: len 30; hex 6170706c69636174696f6e5f313731333736353632383338315f30303031; asc application_1713765628381_0001;;

10: len 6; hex 4641494c4544; asc FAILED;;

11: len 5; hex 99b32ce05c; asc , \;;

12: len 0; hex ; asc ;;

13: len 30; hex 687474703a2f2f6465762d6364682d646e2d303030353a383038382f7072; asc http://cdh-dn-0005:8088/pr; (total 75 bytes);

14: len 30; hex 2f646174612f6264702d706c6174666f726d2f6170706c69636174696f6e; asc /data/bdp-platform/application; (total 99 bytes);

15: SQL NULL;

16: SQL NULL;

17: SQL NULL;

18: len 1; hex 31; asc 1;;

19: len 1; hex 30; asc 0;;

20: len 13; hex 7a686f6e676875615f7a686f75; asc zhonghua_zhou;;

21: len 5; hex 99b32ce298; asc , ;;

22: len 1; hex 80; asc ;;

*** WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 100645603:

RECORD LOCKS space id 3491 page no 75 n bits 80 index PRIMARY of table `xxx_realtime`.`realtime_job_instance` trx id 100645602 lock_mode X waiting

Record lock, heap no 8 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

0: len 8; hex 98bbf5fa74190001; asc t ;;

1: len 6; hex 000005ffbae2; asc ;;

2: len 7; hex 750000028a1550; asc u P;;

3: len 8; hex 800000006a2bf44d; asc j+ M;;

4: SQL NULL;

5: len 15; hex 313931313332383932323730303139; asc 191132892270019;;

6: len 30; hex 006200300033006300610039003500300063006300330039003800620065; asc b 0 3 c a 9 5 0 c c 3 9 8 b e; (total 64 bytes);

7: SQL NULL;

8: len 1; hex 32; asc 2;;

9: len 30; hex 6170706c69636174696f6e5f313731333736353632383338315f30303031; asc application_1713765628381_0001;;

10: len 6; hex 4641494c4544; asc FAILED;;

11: len 5; hex 99b32ce05c; asc , \;;

12: len 0; hex ; asc ;;

13: len 30; hex 687474703a2f2f6465762d6364682d646e2d303030353a383038382f7072; asc http://cdh-dn-0005:8088/pr; (total 75 bytes);

14: len 30; hex 2f646174612f6264702d706c6174666f726d2f6170706c69636174696f6e; asc /data/bdp-platform/application; (total 99 bytes);

15: SQL NULL;

16: SQL NULL;

17: SQL NULL;

18: len 1; hex 31; asc 1;;

19: len 1; hex 30; asc 0;;

20: len 13; hex 7a686f6e676875615f7a686f75; asc zhonghua_zhou;;

21: len 5; hex 99b32ce298; asc , ;;

22: len 1; hex 80; asc ;;

*** TRANSACTION:

TRANSACTION 100645603, ACTIVE 11 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 61032354, OS thread handle 139866267846400, query id 2209943997 172.18.1.53 root updating

UPDATE realtime_job_instance SET job_id=1781265485,

run_type=1,

running_status='FAILED',

running_duration='',

update_time='2024-04-22 14:10:35.272'

WHERE is_delete=0

AND (id = 1782288533593587713 AND run_type = 1)

*** HOLDS THE LOCK:

RECORD LOCKS space id 3491 page no 75 n bits 80 index PRIMARY of table `xxx_realtime`.`realtime_job_instance` trx id 100645603 lock_mode X locks rec but not gap waiting

Record lock, heap no 8 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

0: len 8; hex 98bbf5fa74190001; asc t ;;

1: len 6; hex 000005ffbae2; asc ;;

2: len 7; hex 750000028a1550; asc u P;;

3: len 8; hex 800000006a2bf44d; asc j+ M;;

4: SQL NULL;

5: len 15; hex 313931313332383932323730303139; asc 191132892270019;;

6: len 30; hex 006200300033006300610039003500300063006300330039003800620065; asc b 0 3 c a 9 5 0 c c 3 9 8 b e; (total 64 bytes);

7: SQL NULL;

8: len 1; hex 32; asc 2;;

9: len 30; hex 6170706c69636174696f6e5f313731333736353632383338315f30303031; asc application_1713765628381_0001;;

10: len 6; hex 4641494c4544; asc FAILED;;

11: len 5; hex 99b32ce05c; asc , \;;

12: len 0; hex ; asc ;;

13: len 30; hex 687474703a2f2f6465762d6364682d646e2d303030353a383038382f7072; asc http://cdh-dn-0005:8088/pr; (total 75 bytes);

14: len 30; hex 2f646174612f6264702d706c6174666f726d2f6170706c69636174696f6e; asc /data/bdp-platform/application; (total 99 bytes);

15: SQL NULL;

16: SQL NULL;

17: SQL NULL;

18: len 1; hex 31; asc 1;;

19: len 1; hex 30; asc 0;;

20: len 13; hex 7a686f6e676875615f7a686f75; asc zhonghua_zhou;;

21: len 5; hex 99b32ce298; asc , ;;

22: len 1; hex 80; asc ;;

*** WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 100645602:

RECORD LOCKS space id 3491 page no 75 n bits 80 index PRIMARY of table `xxx_realtime`.`realtime_job_instance` trx id 100645603 lock_mode X locks rec but not gap waiting

Record lock, heap no 8 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

0: len 8; hex 98bbf5fa74190001; asc t ;;

1: len 6; hex 000005ffbae2; asc ;;

2: len 7; hex 750000028a1550; asc u P;;

3: len 8; hex 800000006a2bf44d; asc j+ M;;

4: SQL NULL;

5: len 15; hex 313931313332383932323730303139; asc 191132892270019;;

6: len 30; hex 006200300033006300610039003500300063006300330039003800620065; asc b 0 3 c a 9 5 0 c c 3 9 8 b e; (total 64 bytes);

7: SQL NULL;

8: len 1; hex 32; asc 2;;

9: len 30; hex 6170706c69636174696f6e5f313731333736353632383338315f30303031; asc application_1713765628381_0001;;

10: len 6; hex 4641494c4544; asc FAILED;;

11: len 5; hex 99b32ce05c; asc , \;;

12: len 0; hex ; asc ;;

13: len 30; hex 687474703a2f2f6465762d6364682d646e2d303030353a383038382f7072; asc http://cdh-dn-0005:8088/pr; (total 75 bytes);

14: len 30; hex 2f646174612f6264702d706c6174666f726d2f6170706c69636174696f6e; asc /data/bdp-platform/application; (total 99 bytes);

15: SQL NULL;

16: SQL NULL;

17: SQL NULL;

18: len 1; hex 31; asc 1;;

19: len 1; hex 30; asc 0;;

20: len 13; hex 7a686f6e676875615f7a686f75; asc zhonghua_zhou;;

21: len 5; hex 99b32ce298; asc , ;;

22: len 1; hex 80; asc ;;

*** WE ROLL BACK TRANSACTION 100645603

------------

TRANSACTIONS

------------

Trx id counter 117261737

Purge done for trx's n:o < 117261735 undo n:o < 0 state: running but idle

History list length 18

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421346793319968, not started