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

0 序

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

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

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

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

2 排查方法(扩展)

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

方法一：使用SHOW OPEN TABLES命令

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

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

方法二：使用SHOW PROCESSLIST命令

SHOW PROCESSLIST;

这个命令会返回当前MySQL服务器上所有的活动进程。如果表被锁定，那么可以通过查看这个进程列表来确定是否有进程正在使用该表。你可以检查State列中的信息，看是否有进程正在锁定该表。

方法三：使用INFORMATION_SCHEMA系统库

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

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

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

SHOW ENGINE INNODB STATUS

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

方法五：使用sys.innodb_lock_waits系统视图（仅适用于MySQL 8.0及以上版本）

SELECT * FROM sys.innodb_lock_waits;

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

方法六：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、死锁的处理

当多个会话之间出现循环依赖的锁竞争关系时，就会发生死锁。MySQL会自动检测到死锁，并选择一个会话进行回滚，以解除死锁。
使用SHOW ENGINE INNODB STATUS命令可以查看死锁信息。例如：

SHOW ENGINE INNODB STATUS

在输出结果中的"TRANSACTIONS"部分，可以找到死锁的详细信息；
通过以上方式，从系统本身的编码，SQL语句，数据库表关键字做优化之后，锁表情况也彻底解决；

Y FAQ

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

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

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

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

`FLUSH TABLES WITH READ LOCK`的简介

FLUSH TABLES WITH READ LOCK，简称(FTWRL),这个命令通常在热备份时使用，也是瞬时命令。

因为这个命令的特殊性，执行命令时一不留神容易导致穷住。

目前在热备份当中 xtrabackup 和 mysqldump 非常常见。

##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 TABLES和FLUSH 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_name、ALTER table、RENAME table、REPAIR table、ANALYZE table或OPTIMIZE table，则会发生此通知。

FTWRL机制

关闭所有打开的表，并使用全局读锁锁定所有数据库的所有表。

该操作需要FLUSH_TABLES或RELOAD权限

FLUSH TABLES WITH READ LOCK和read_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 参考文献

MySQL数据库查看是否锁表以及如何解决 - CSDN

mysql表只读状态查询 mysql读数据会锁表吗 - 51CTO

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

会员力量，点亮园子希望

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

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

0 序

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

2 排查方法(扩展)

方法一：使用SHOW OPEN TABLES命令

方法二：使用SHOW PROCESSLIST命令

方法三：使用INFORMATION_SCHEMA系统库

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

方法五：使用sys.innodb_lock_waits系统视图（仅适用于MySQL 8.0及以上版本）

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

3 解决思路：治本之道

(1) SQL 本身优化

(2) 编码层面

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

A、ROLLBACK

B、COMMIT

C、显式调用UNLOCK TABLES

D、长事务的处理

E、死锁的处理

Y FAQ

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

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

`FLUSH TABLES WITH READ LOCK`的简介

FTWRL穷住现象

案例1：

案例2：

FTWRL机制

引擎影响

LOCK INSTANCE FOR BACKUP

总结

X 参考文献

X 参考文献

公告

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

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

0 序

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

2 排查方法(扩展)

方法一：使用SHOW OPEN TABLES命令

方法二：使用SHOW PROCESSLIST命令

方法三：使用INFORMATION_SCHEMA系统库

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

方法五：使用sys.innodb_lock_waits系统视图（仅适用于MySQL 8.0及以上版本）

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

3 解决思路：治本之道

(1) SQL 本身优化

(2) 编码层面

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

A、ROLLBACK

B、COMMIT

C、显式调用UNLOCK TABLES

D、长事务的处理

E、死锁的处理

Y FAQ

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

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

FLUSH TABLES WITH READ LOCK的简介

FTWRL穷住现象

案例1：

案例2：

FTWRL机制

引擎影响

LOCK INSTANCE FOR BACKUP

总结

X 参考文献

X 参考文献

公告

`FLUSH TABLES WITH READ LOCK`的简介