Mysql数据库死锁挂起的处理方法

发表于 | 分类于
Mysql数据库死锁挂起的处理方法

死锁解决方法

MySQL在进行一些alter table等DDL操作时，如果该表上有未提交的事务则会出现 Waiting for table metadata lock，

而一旦出现metadata lock，该表上的后续操作都会被阻塞。

杀死后续的操作

操作所有库

检查被占用的表：

1
show OPEN TABLES where In_use > 0;

显示进程：

1
show processlist;

找到正在运行sql的进程

杀死挂起的进程即导致表锁死的进程：

1
kill 17909; ---17909是进程的id

操作单库

查询某个库所有进程：

1
SELECT * FROM information_schema.PROCESSLIST WHERE db = 'xhkjedu_school';

注意

SLEEP 进程是 MySQL 连接管理的自然结果，并不会对数据库性能造成负面影响。

当有新查询到达时，这些 Sleep 进程会迅速被唤醒并处理请求。

杀某个库所有进程：

1
2
3
SELECT CONCAT('KILL ', id, ';') AS KillStatement
FROM information_schema.processlist
WHERE db = 'xhkjedu_school';

正在执行时间大于10秒的进程：

1
SELECT * FROM information_schema.PROCESSLIST WHERE db = 'xhkjedu_school' and STATE='executing' and TIME>10;

杀死正在执行的进程：

1
2
3
SELECT CONCAT('KILL ', id, ';') AS KillStatement
FROM information_schema.processlist
WHERE db = 'xhkjedu_school' and STATE='executing' and TIME>10;

杀死事务

事务的进程通过上面的方式是可以看到的，杀掉后这里也就没了。

使用管理员权限登录mysql数据库查看未提交的事务：

（如果不是管理员权限会报错：Access denied; you need (at least one of) the PROCESS privilege(s) for this operation）

1
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

这时会看到所有的事务，有以下相关信息：

  • trx_state: 事务状态，一般为RUNNING
  • trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
  • trx_mysql_thread_id: MySQL的线程ID，用于kill
  • trx_query: 事务中的sql

杀死线程ID，问题解决。

1
kill 12345;

批量杀进程

1
2
SELECT CONCAT('KILL ', trx_mysql_thread_id, ';') AS KillStatement
from information_schema.innodb_trx;

这是一个用于查询 MySQL 数据库中 InnoDB 存储引擎事务信息的 SQL 查询语句。

具体来说，它会返回以下列的信息：

  1. trx_state：表示事务的当前状态。可能的值包括：

    • RUNNING：事务正在运行。
    • LOCK WAIT：事务正在等待锁资源。
    • ROLLING BACK：事务正在执行回滚操作。
    • COMMITTING：事务正在提交。
    • COMMITTED：事务已经提交。
    • ROLLED BACK：事务已经回滚。

  2. trx_started：表示事务开始的时间。

  3. trx_mysql_thread_id：表示 MySQL 线程 ID，每个连接到 MySQL 的客户端都有一个唯一的线程 ID。

  4. trx_query：表示当前事务执行的 SQL 查询语句。

这个查询语句可以帮助你了解当前正在运行的事务的状态以及相应的信息，对于监控和诊断数据库性能问题非常有用。

例如，你可以使用它来查找是否有长时间运行的事务，以及它们的状态和相关的查询语句。

需要注意的是，执行这个查询可能需要相应的权限，通常需要具有 PROCESS 或者 SUPER 权限才能查询 information_schema.innodb_trx 表。

索引

一般查询时间太长都是因为没有索引我们可以在SQL前添加EXPLAIN来分析是否用到索引

查看 EXPLAIN 的输出结果，注意以下字段：

  • id: 查询的标识符，如果涉及多个表或子查询，会有不同的标识符。

  • select_type: 查询的类型，例如 SIMPLE、PRIMARY、SUBQUERY 等。

  • table: 表的名称。

  • type: 访问类型，表示查询时如何访问表。

    依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引。

    除了index_merge之外，其他的type只可以用到一个索引。

  • possible_keys 和 key: 潜在可用的索引和实际使用的索引。

  • rows: 预估的扫描行数。

  • Extra: 额外的信息，例如是否使用了临时表、文件排序等。

特别关注 possible_keys 和 key 字段。

possible_keys 列出了可能用到的索引，而 key 表示实际使用的索引。

如果一个关联或检索的字段不在possible_keys里，那么它就需要添加索引。

如果 possible_keys 为空，说明没有针对该查询的潜在索引。

如果 key 为空，表示该查询没有使用索引。

如果 possible_keys 中没有列出索引，并且 key 字段为空，那么表示查询中涉及的所有字段都没有使用索引。

例如

1
EXPLAIN SELECT count(0) FROM (SELECT q.questionid, q.score, q.complexity, q.qstem, q.qstemtxt, q.qoption, q.qanswer, q.qanalyze, q.qtypeid, q.qtypename, q.count, q.ctype, q.source, q.year, q.region, q.schoolname, q.qlevel, q.hearfile, q.hashear, q.belong, q.createid, q.schoolid, (SELECT count(*) FROM t_question q2 WHERE q2.shareqid = q.questionid AND q2.qstate = 1 AND q.schoolid = 2) sharenum FROM t_question q LEFT JOIN t_question_director qd ON q.questionid = qd.questionid WHERE q.qstate = 1 AND qd.lsbid = '3B2OZP' AND q.subjectid = '3B2OZS' AND (q.belong IN (1, 2) OR (q.belong IN (3, 10) AND q.schoolid = 2) OR (q.belong = 4 AND q.createid = 36)) AND q.qlevel < 3 GROUP BY q.questionid) table_count;