咸鱼翻身记

mysql锁表排查

2024/10/10 SQL调优

数据表突然间查询不出数据，表也删除不掉，超时无响应，此时我们应该检查该数据表是不是被锁了。

导致死锁的原因：服务器卡顿导致累积了很多等待进程，网络差，同时多个线程操作同一张表。

# 查询客户端会话

SHOW PROCESSLIST 是 MySQL 中的一个命令，它用于显示当前在 MySQL 服务器上运行的所有客户端会话的详细信息。

以下是 SHOW PROCESSLIST 命令提供的一些关键信息：

  • Id：每个连接的唯一标识符。

  • User：连接到 MySQL 服务器的用户名。

  • Host：客户端连接的主机名或 IP 地址。

  • db：当前正在使用的数据库。

  • Command：当前客户端会话正在执行的命令。例如，它可以是“Query”、“Sleep”、“Connect”等。

  • Time：从客户端连接到服务器开始经过的时间（以秒为单位）。

  • State：描述了当前 SQL 语句的状态。

  • Info：当前执行的 SQL 语句或查询。

这个命令对于监控和调试 MySQL 服务器非常有用，因为它可以帮助你了解当前服务器负载、识别长时间运行的查询、检测潜在的 SQL 注入攻击等。然而，需要注意的是，频繁地执行此命令可能会对服务器性能产生一些影响，因此建议谨慎使用。

# 定位锁表进程

使用以下SQL命令来查询当前数据库系统中所有正在执行的事务，找到锁表进程：

SELECT * FROM information_schema.innodb_trx;
1

# 查看锁表进程持有的锁

使用以下SQL命令来查看锁表进程持有的锁：

SELECT * FROM information_schema.innodb_locks;
1

此时可能会报错找不到information_schema.innodb_locks表，mysql 8以后用以下命令：

select * from performance_schema.data_locks;
1

# 查看锁表进程等待的锁

使用以下SQL命令来查看锁表进程等待的锁：

SELECT * FROM information_schema.innodb_lock_waits;
1

此时可能会报错找不到 information_schema.innodb_lock_waits表，mysql 8以后用以下命令：

select * from performance_schema.data_lock_waits;
1

# 解决锁表问题

根据锁表进程的状态和锁信息，可以采取以下措施解决锁表问题：

  • (1) 终止锁表进程：

    使用以下SQL命令来终止锁表进程：

    KILL [进程ID];
    1

  • (2) 调整事务隔离级别：

    根据实际情况，可以考虑降低事务隔离级别，减少锁表的概率。

  • (3) 优化SQL语句：

    优化SQL语句，减少锁表的概率。例如，避免使用SELECT *，减少锁表的范围，使用索引等。

  • (4) 调整数据库参数：

    根据实际情况，可以调整数据库的一些参数，例如innodblockwaittimeout，innodbdeadlock_detect等，来减少锁表的概率。