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;

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

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;

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

D、长事务的处理

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

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

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

E、死锁的处理

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

SHOW ENGINE INNODB STATUS

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

X 参考文献

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

标签：事务,会话,锁表,MySQL,排查,死锁,SQL
From： https://www.cnblogs.com/johnnyzen/p/18150246

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

0 序