[数据库] 排查MySQL锁表情况及解决思路

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

事务

事务的开启、提交、回滚

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

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

START TRANSACTION; 
-- 或 BEGIN;
  • 【提交事务】:上述语句执行后,每一条SQL语句不再自动提交,用户需要使用以下语句手动提交,只有事务提交后,其中的操作才会生效。
COMMIT;
  • 【回滚事务】:如果不想提交当前事务,可以使用如下语句取消事务(即回滚)。
ROLLBACK;

事务的四大特性:ACID

  1. 原子性: Atom
    原子性(Atomicity)是指一个事务必须被视为一个不可分割的最小工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功。
    事务中如果有任何一个SQL语句执行失败,已经执行成功的SQL语句也必须撤销,数据库的状态退回到执行事务前的状态。

  2. 一致性: Consist
    一致性(Consistency)是指在事务处理时,无论执行成功还是失败,都要保证数据库系统处于一致的状态,保证数据库系统不会返回到一个未处理的事务中。
    MySQL中的一致性主要由日志机制实现,通过日志记录数据库的所有变化,为事务恢复提供了跟踪记录。

  3. 隔离性: Isolation
    隔离性(Isolation)是指当一个事务在执行时,不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离,直到事务完成为止,才能看到事务的执行结果。
    隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

  4. 持久性: Durable
    持久性(Durability)是指事务一旦提交,其对数据库的修改就是永久性的。
    需要注意的是,事务的持久性不能做到百分百的持久,只能从事务本身的角度来保证永久性,而一些外部原因导致数据库发生故障,如硬盘损坏,那么所有提交的数据可能都会丢失。

并发事务引起的问题

  1. 脏读:

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

  1. 不可重复读:

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

  1. 幻读:

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

事务隔离级别

事务隔离级别

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

  2. 读已提交(不可重复读)
    READ COMMITTED 是大多数 DBMS (如 SQL Server、Oracle) 的默认隔离级,但不包括MySQL。
    在该隔离级下只能读取其他事务已经提交的数据,避免了脏读数据的现象。但是在该隔离级别下,会出现不可重复读(NON-REPEATABLE READ)的问题。

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

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

  1. 串型化
    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里的sychronizedreentrantlock悲观锁类似,效率比较低。

悲观锁是从数据库层面并发控制,这个应该是真正意义的加锁,分为共享锁排他锁
虽然可以加共享锁,但是mysqlupdateinsertdelete自动加了排他锁
使用悲观锁,需要关闭mysqlauto_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,并且必须开启事务,在begincommit之间才生效。

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; -- 没有申请获取(任何)锁,自然不会被阻塞

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

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

  • MySQL里面表级别的锁有两种:
  • 一种是表锁,
  • 一种是元数据锁meta data lockMDL
  • 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;
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+
| 679 | root | localhost | test | Query | 0 | starting | show processlist | 0 | 0 |
| 680 | root | localhost | test | Query | 4 | Waiting for table metadata lock | drop table foo | 0 | 0 |
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+
  • MDL锁,不需要显式使用,在访问一个表的时候会被自动加上。

  • MDL作用

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

  • MySQL5.5版本引入了MDL:
  • 当对一个表做增/删/改/查操作的时候,加MDL读锁
  • 当要对表做结构变更操作的时候,加MDL写锁
  • 互斥情况
  • MDL读锁之间不互斥。

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

  • MDL读写锁之间、写锁之间是互斥的
  • 释放情况
  • 事务中的MDL锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放
  • 参考文献

上锁方式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;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| ID      | bigint unsigned | NO   | PRI | NULL    |       |
| USER    | varchar(32)     | YES  |     | NULL    |       |
| HOST    | varchar(261)    | YES  |     | NULL    |       |
| DB      | varchar(64)     | YES  |     | NULL    |       |
| COMMAND | varchar(16)     | YES  |     | NULL    |       |
| TIME    | bigint          | YES  |     | NULL    |       |
| STATE   | varchar(64)     | YES  |     | NULL    |       |
| INFO    | longtext        | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

字段说明

  • ID
    连接标识符。这是在 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(主机地址:端口) 的格式显示,以便更容易确定哪个客户端在执行什么操作。

  • DB
    线程的默认数据库,如果未选择任何数据库,则为 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
  • Register Slave The thread is registering a replica server
  • 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 (目前未使用该状态)
  • 详情参考

STATE字段值说明

  • STATE 字段说明
  • Sleep:连接处于空闲状态,没有正在执行的命令。
  • Query:连接正在执行一个查询。
  • Locked:连接正在等待锁定资源。
  • Copying to tmp table:连接正在将结果复制到临时表中。
  • Sending data:连接正在发送查询结果给客户端。
  • Sorting result:连接正在对结果进行排序。
  • Waiting for table flush:连接正在等待表的刷新。
  • Repair by sorting:连接正在执行表的修复操作。
  • Creating sort index:连接正在创建排序索引。
  • 详情参考

推荐文献

  • MySQL 官网

方法三:使用 INFORMATION_SCHEMA.INNODB_LOCKS

  • 表结构
root@127.0.0.1 : information_schema 13:28:38> desc INFORMATION_SCHEMA.INNODB_LOCKS;
+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+————-+———————+——+—–+———+——-+
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;
+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
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;
+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID。InnoDB内部生成的事务唯一ID,对于只读事务或未申请锁的事务,不会分配此ID。
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态。事务的状态,常见的值有RUNNING(事务正在执行)、LOCK WAIT(事务在等待锁)等。
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间。事务开始的时间戳,帮助判断事务运行的时长。
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id。如果事务处于锁等待状态,此字段标识它正在等待的锁的ID。
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间。 如果事务在等待锁,记录等待开始的时间。
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#事务的“重量”。反映事务修改和锁定的行数。虽然不一定精确,但在解决死锁时,InnoDB会倾向于选择重量较小的事务进行回滚。
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事务线程ID。MySQL线程ID,与执行事务的客户端线程相关联。
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL语句。正在执行的事务中的SQL语句文本,这对于诊断问题特别有用,因为它直接展示了导致事务行为的SQL代码。
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事务中有多少个表被使用。当前事务中涉及的表数量
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事务拥有多少个锁。被事务锁定的表数量
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |# 
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的内存大小(B)
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事务更改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事务并发票数
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误
| 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
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793284064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793331584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793329472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793330528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793246048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793248160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793308352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793326304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793325248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793267168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793318912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793317856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793316800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793315744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793314688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793270336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793281952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793309408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793273504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793262944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793255552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793304128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793264000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793259776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793323136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793322080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793276672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793258720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793311520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793279840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793277728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793261888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793250272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793251328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793306240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793278784, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793290400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793307296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793300960, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793299904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793298848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793295680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793293568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793292512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793291456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793289344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793288288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793287232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793286176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793285120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793283008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793269280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793297792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793280896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793266112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793265056, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793244992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793260832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793257664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793296736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793294624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793274560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793272448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793271392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793268224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793253440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793252384, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793249216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793247104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793243936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793242880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793240768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793239712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793256608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793254496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793337920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793336864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793310464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793321024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793303072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793302016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793313632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793312576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793275616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793305184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421346793241824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 31
505 OS file reads, 208700900 OS file writes, 129325540 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 11.33 writes/s, 6.66 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 47502 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
Hash table size 1214393, node heap has 0 buffer(s)
0.00 hash searches/s, 179.94 non-hash searches/s
---
LOG
---
Log sequence number 21112341739
Log flushed up to   21112341630
Pages flushed up to 21112341178
Last checkpoint at  21112340856
0 pending log flushes, 0 pending chkp writes
63581303 log i/o's done, 2.50 log i/o's/second
-------------
LOG MIGRATION
-------------
The Number of switch log successfully in Innodb level is 0
The Number of switch log fail in Innodb level is 0
The Longest time for switch log is 0 ms 
The Current time for swtich log is 0 ms 
The active of log file path is in base log group path set by param innodb_log_group_home_dir
The Status of log migration 
Invalid status?
The count of waiting double write finish in Current log migration is 0
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4294967296
Dictionary memory allocated 6176791
Buffer pool size   256128
Free buffers       77036
Database pages     179092
Old database pages 66028
Modified db pages  4
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 78451, not young 6715
0.00 youngs/s, 0.00 non-youngs/s
Pages read 372, created 283249, written 119400702
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 179092, unzip_LRU len: 0
I/O sum[660]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   64032
Free buffers       19613
Database pages     44419
Old database pages 16376
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27559, not young 6703
0.00 youngs/s, 0.00 non-youngs/s
Pages read 138, created 70129, written 34181382
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44419, unzip_LRU len: 0
I/O sum[165]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   64032
Free buffers       19248
Database pages     44784
Old database pages 16511
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14460, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 71109, written 13991151
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44784, unzip_LRU len: 0
I/O sum[165]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   64032
Free buffers       19100
Database pages     44932
Old database pages 16566
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21126, not young 5
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25, created 71204, written 24042590
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44932, unzip_LRU len: 0
I/O sum[165]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   64032
Free buffers       19075
Database pages     44957
Old database pages 16575
Modified db pages  2
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15306, not young 7
0.00 youngs/s, 0.00 non-youngs/s
Pages read 123, created 70807, written 47185579
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44957, unzip_LRU len: 0
I/O sum[165]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2324, Main thread ID=139866748737280, state: sleeping
Number of rows inserted 716797470, updated 19623068, deleted 4990696, read 167830712105
0.00 inserts/s, 0.33 updates/s, 0.00 deletes/s, 143.95 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

参考文献

Y FAQ

Q: MySQL问题之基于Java Hibernate ORM框架,当数据库数据源被锁(Table Metadata Lock)时的解决方案

注:原文发于: [数据库] MySQL问题之当数据库数据源被锁(Table Metadata Lock)时的解决方案 - 博客园/千千寰宇 | 2017-11-05 18:55 【原文已删,归档至此】

  1. 发生的原因分析:【hibernate的线程池连接导致了不能修改被锁定数据库的数据库型模式】
  2. 关掉hibernate的所有线程池(选择退出IDE或者其他办法)
  3. 查看被锁的进程ID:show full processlist
  4. 杀掉该进程ID:kill + ID
  5. 提交导致数据源被锁的事务:COMMIT 或者 ROLLBACK

5 扩展:MySQL 全局读锁(FLUSH TABLES WITH READ LOCK)

FLUSH TABLES WITH READ LOCK的简介

  • FLUSH TABLES WITH READ LOCK,简称(FTWRL),这个命令通常在热备份时使用,也是瞬时命令
  • 因为这个命令的特殊性,执行命令时一不留神容易导致穷住
  • 目前在热备份当中 xtrabackupmysqldump 非常常见。
##xtrabackup 
shell# xtrabackup --defaults-file=/etc/my.cnf --no-server-version-check --backup -uroot -p --socket=/tmp/mysql.sock --target-dir=/tmp/back
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220819 11:26:05  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3381;mysql_socket=/opt/data8.1/data/mysql.sock' as 'root'  (using password: YES).
。。。
2022-08-19T11:26:06.058537+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (2677710324)
2022-08-19T11:26:06.226237+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /tmp/back/undo_001
2022-08-19T11:26:08.834591+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH TABLES WITH READ LOCK...

##mysqldump
shell# mysqldump -uroot -p -S /tmp/mysql.sock --set-gtid-purged=OFF  --all-databases --master-data=2 --single-transaction >  /tmp/full.sql
2022-08-19T16:33:11.447763+08:00  120 Connect root@localhost on  using Socket
2022-08-19T16:33:11.448073+08:00  120 Query   /*!40100 SET @@SQL_MODE='' */
2022-08-19T16:33:11.448357+08:00  120 Query   /*!40103 SET TIME_ZONE='+00:00' */
2022-08-19T16:33:11.448512+08:00  120 Query   /*!80000 SET SESSION information_schema_stats_expiry=0 */
2022-08-19T16:33:11.448706+08:00  120 Query   SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400
2022-08-19T16:33:11.448866+08:00  120 Query   FLUSH /*!40101 LOCAL */ TABLES
2022-08-19T16:33:11.451661+08:00  120 Query   FLUSH TABLES WITH READ LOCK
...

备注:在mysqldump--master-data--lock-all-tables参数引发FLUSH TABLESFLUSH TABLES WITH READ LOCK的输出。

FTWRL穷住现象

下面两个案例中FLUSH TABLES WITH READ LOCK 导致数据库出现穷住情况。

案例1:

#session1
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              32 |
+-----------------+
1 row in set (0.00 sec)

#session2
mysql> INSERT INTO tmr_tzsy...

  • Waiting for global read lock

正在等待全局读锁:这种情况是执行FLUSH TABLES WITH READ LOCK命令 或 正在设置全局read_only系统变量时出现

案例2:

  • Waiting for table flush
  • 线程正在执行FLUSH TABLES,等待所有线程关闭它们的表,或者线程得到一个通知,表的底层结构已经改变,它需要重新打开表来获得新的结构。但是,要重新打开表,必须等到所有其他线程都关闭了这个表。
  • 当线程使用FLUSH TABLES或其他语句之一:FLUSH TABLES tbl_nameALTER tableRENAME tableREPAIR tableANALYZE tableOPTIMIZE table,则会发生此通知。

FTWRL机制

  • 关闭所有打开的表,并使用全局读锁锁定所有数据库的所有表。
  • 该操作需要FLUSH_TABLESRELOAD权限
  • FLUSH TABLES WITH READ LOCKread_only变量:不会阻止服务器向日志表插入行,所以对于日志表没影响。
  • 从相关描述中不难理解,操作表缓存,那就必须获取元表相关的锁,因为每个表在内存中都有一个table_cache,不同表的cache对象通过hash链表维护。

  • 关闭表过程中,如果有大查询导致关闭表等待,那么所有访问这个表的查询和更新都需要等待;

  • 全局读锁会导致所有更新操作都会被堵塞

引擎影响

  • 对于xtrabackup来说 MySQL8.0版本的 innodb和myisam引擎做了优化:

  • 在8.0版本中如实例中没有MyISAM引擎,就不会上FTWRL锁。5.6版本和5.7版本 系统表本身就存在MyISAM引擎,所以FTWRL锁 避免不了。
#Mysql5.7
mysql> SELECT TABLE_SCHEMA, ENGINE, COUNT(*)
     FROM INFORMATION_SCHEMA.TABLES
     WHERE  TABLE_TYPE='BASE TABLE'
     GROUP BY TABLE_SCHEMA,  ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA       | ENGINE             | COUNT(*) |
+--------------------+--------------------+----------+
| db1                | InnoDB             |       14 |
| mysql              | CSV                |        2 |
| mysql              | InnoDB             |       19 |
| mysql              | MyISAM             |       10 |
| performance_schema | PERFORMANCE_SCHEMA |       87 |
| sbtest             | InnoDB             |     3000 |
| sys                | InnoDB             |        1 |
+--------------------+--------------------+----------+
16 rows in set (0.87 sec)

备注:上述备份执行命令行里其实还存在FLUSH NO_WRITE_TO_BINLOG BINARY LOGS命令。
默认情况下,服务器将FLUSH语句写入二进制日志,以便将它们复制到副本。要禁止日志记录,可以指定可选的NO_WRITE_TO_BINLOG关键字或别名LOCAL

LOCK INSTANCE FOR BACKUP

  • MySQL8.0采用 LOCK INSTANCE FOR BACKUP 获取一个实例级备份锁,该锁允许在联机备份期间进行DML操作,同时防止可能导致快照不一致的操作。
  • 防止:文件被创建、重命名或删除。
  • 阻塞:REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE,以及账户管理语句。
  • 阻止:对InnoDB重做日志中没有记录的修改InnoDB文件的操作。
  • 阻止:发出PURGE BINARY LOGS命令
  • 允许:只影响用户创建的临时表的DDL操作。实际上,当持有备份锁时,属于用户创建的临时表的文件可以被创建、重命名或删除。
  • 允许:创建binary log二进制日志文件。
  • UNLOCK INSTANCE释放当前会话持有的备份锁。如果会话终止,会话持有的备份锁也会被释放。其中lock_wait_timeout定义LOCK INSTANCE FOR BACKUP语句在放弃之前等待获得锁的时间。
  • 执行需要BACKUP_ADMIN权限,从早期版本到MySQL 8.0的本地升级时,BACKUP_ADMIN特权会自动授予具有RELOAD特权的用户。
LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;


FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

总结

对于FLUSH TABLES WITH READ LOCK命令导致“Waiting for global read lock”,“Waiting for table flush”现象出现,最好的应对方式,就是把发出FTWRL命令的进程kill掉,释放资源。

FTWRL目前很难避免,应该减少对数据库影响:

  • 不管5.7版本还是8.0版本,引擎采取innodb引擎。
  • 备份一般放在从库就行,当然有需要主库也可以。
  • 备份期间选择负载低,如存在长时间SQL语句,暂停一下。后期需要对长时间SQL进行优化。
  • 对于8.0版本备份机制,LOCK INSTANCE FOR BACKUP限制要遵守。
  • DDL肯定禁止。
  • 合理利用相关参数优化
类型 参数 说明
xtrabackup ftwrl-wait-timeout 在执行ftwrl之前如果被活跃会话阻塞了,就等待其执行完成,如果超时时间到了活跃会话还没执行完则备份失败退出。
xtrabackup ftwrl-wait-threshold 在执行ftwrl之前,如果有超过该设置时间的活跃会话ftwrl将会等待,直到超过ftwrl-wait-timeout则备份失败退出。
xtrabackup kill-long-query 当kill-long-queries-timeout设置非零,限制innobackup可以kill的查询类型,select或者all。
xtrabackup kill-long-queries-timeout 在执行ftwrl过程中不会立刻kill掉阻塞ftwrl执行的活跃会话,而是等待设置的时间,默认是0即不会kill掉任何会话。
mysql innodb_lock_wait_timeout 锁等待的时间。

X 参考文献

X 参考文献

posted @ 2024-04-22 11:06  千千寰宇  阅读(4206)  评论(0编辑  收藏  举报