mysql事务未commit

如果一个连接中开启事务且未显式提交或回滚，在不考虑其他因素的前提下，确实应该是只有在连接断开的时候才会回滚。 如果出现锁等待，则是根据innodb_rollback_on_timeout参数进行下一步动作，如果此参数是off，则会回滚上一条语句，不会提交或回滚，直到显式提交或回滚（事务一直存在，直到连接断开）。如果此参数是on，在超时后会回滚整个事务。

1、mysql 如何查看未提交的事务

方法一：

(root@localhost)[performance_schema]>  SELECT * FROMinformation_schema.INNODB_TRX\G
***************************1. row ***************************
                    trx_id: 756996
                 trx_state: RUNNING
               trx_started: 2022-02-08 15:08:07
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 4
                 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: REPEATABLE READ
         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
1 row inset (0.00 sec)

通过以上可看出线程id为4 一直未提交，事务开始的时间为2022-02-08 15:08:07。

方法二：通过 show engine innodb status

其中有一段关于事务的描述

TRANSACTIONS
------------
Trx idcounter 756998
Purgedone for trx's n:o < 0 undo n:o < 0 state: running but idle
Historylist length 0
LIST OFTRANSACTIONS FOR EACH SESSION:
---TRANSACTION421519065333360, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION421519065332448, not started
0 lockstruct(s), heap size 1136, 0 row lock(s)
---TRANSACTION756996, ACTIVE 914 sec
2 lockstruct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140041791522560, query id25 localhost root

从以上也可以看出线程id号为4的事务一直未提交。

2、mysql如何解决未提交的事务

方法一：如果能知道哪个用户在执行这个操作，让他提交一下（这种可能性很小）。

方法二：kill掉这个线程id号，让事务回滚。

(root@localhost)[information_schema]> show processlist;
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
| Id |User            | Host             | db                 | Command | Time | State                  | Info             |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost        | NULL               | Daemon  | 4469 | Waiting on empty queue | NULL             |
|  4 | root            | localhost        | apex               | Sleep   |  871|                        | NULL             |
|  5 | root            | localhost        | apex               | Sleep   |   82|                        | NULL             |
|  6 | root            | localhost        | information_schema | Query   |    0| starting               | showprocesslist |
|  7 | root            | 192.168.1.1:3708 | NULL               | Sleep   | 3221 |                        | NULL             |
+----+-----------------+------------------+--------------------+---------+------+------------------------+------------------+
5 rowsin set (0.00 sec)
 
(root@localhost)[information_schema]> kill 4;
QueryOK, 0 rows affected (0.01 sec)