DBBrain最佳实践：未提交事务的处理与应对

原创

王文安@DBA

发布于 2022-03-03 14:19:09

2.6K2

发布于 2022-03-03 14:19:09

文章被收录于专栏：腾讯云数据库专家服务腾讯云数据库专家服务

背景

DBBrain 上经常会有用户来咨询“未提交事务”的事件会有什么问题，该如何处理等。其实这个问题的影响属于可大可小，所以正好来专门分析一下，避免因为轻视了这个问题导致严重的业务故障。

问题描述

未提交事务指的是有连接在数据库中开启了事务，但是却一直没有提交事务的现象。如果事务一直不提交，那么对应数据行的锁始终无法释放，表的元数据锁也会一直持有，导致这个表的 DDL 会被一直阻塞。

DBBrain 针对这个问题有专门的监控，当发现这个现象之后就会推送“未提交事务”的异常事件。

分析

点开DBBrain可以看到有异常事件“未提交事务”。

这一类未提交事务的信息可以在DBBrain的事件详情，或者在命令行检查：

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 149573207
                 trx_state: RUNNING
               trx_started: 2022-03-03 13:17:36
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 194632
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

这段信息中，比较重要的信息是事务开始时间和 thread_id，如上例中，可以根据事务开始时间估算是什么业务模块/脚本发起的。trx_mysql_thread_id 显示的是这个事务是由哪个连接发起的，通过 processlist 中的 id 找到这个线程。

mysql> show processlist;
+--------+------+----------------+--------------------+---------+------+----------+------------------+
| Id     | User | Host           | db                 | Command | Time | State    | Info             |
+--------+------+----------------+--------------------+---------+------+----------+------------------+
| 194631 | root | 10.0.0.6:37912 | information_schema | Query   |    0 | starting | show processlist |
| 194632 | root | 10.0.0.6:37914 | test               | Sleep   | 1611 |          | NULL             |
+--------+------+----------------+--------------------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

可以看到 194632 这个 id 对应的是 test 用户，info 显示的 NULL，不显示具体 SQL 是因为 SQL 语句已经执行完了，然后没有再执行任何操作，处于空闲状态。

如果事务未提交，那么其他连接在操作同样的数据库行时，就会遇到锁等待报错，DDL 也会有 MDL 锁，通过简单的示例模拟一下：

Seesion 1:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update t1 set name = 'stu-108-m' where id = 8;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>
mysql> alter table t1 engine=innodb;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>

Session 2:
mysql> show processlist;
+--------+------+----------------+------+---------+------+---------------------------------+------------------------------+
| Id     | User | Host           | db   | Command | Time | State                           | Info                         |
+--------+------+----------------+------+---------+------+---------------------------------+------------------------------+
| 194632 | root | 10.0.0.6:37914 | test | Query   |    0 | starting                        | show processlist             |
| 195373 | root | 10.0.0.6:57056 | test | Query   |   11 | Waiting for table metadata lock | alter table t1 engine=innodb |
+--------+------+----------------+------+---------+------+---------------------------------+------------------------------+
2 rows in set (0.00 sec)

如果 lock_wait 默认设置得很高，那么未提交事务会长时间阻塞其他的连接，严重一点的会导致大量的连接堆积在数据库中导致数据库hang死，DDL 操作同理，不管是 MDL 锁还是线程堆积都会影响到线上的业务语句。

解决办法其实比较简单，在 DBBrain 的会话中杀死这个 id 为 195373 的连接，或者是在命令行，使用 test 这个用户登录进去，使用 kill 命令杀死 195373 的连接。

PS：使用 test 的原因是腾讯云数据库 MySQL 默认是没有 super 权限的，所以必须要使用同一个账号来操作。