在 MySQL 中显示打开的事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7598373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display open transactions in MySQL
提问by Alex
I did some queries without a commit. Then the application was stopped.
我在没有提交的情况下做了一些查询。然后应用程序被停止。
How can I display these open transactions and commit or cancel them?
如何显示这些打开的事务并提交或取消它们？
采纳答案by Johan
How can I display these open transactions and commit or cancel them?
如何显示这些打开的事务并提交或取消它们？
There is no open transaction, MySQL will rollback the transaction upon disconnect.
You cannot commit the transaction (IFAIK).
没有打开的事务，MySQL 会在断开连接时回滚事务。
您不能提交事务 (IFAIK)。
You display threads using
您显示线程使用
SHOW FULL PROCESSLIST
See: http://dev.mysql.com/doc/refman/5.1/en/thread-information.html
参见：http: //dev.mysql.com/doc/refman/5.1/en/thread-information.html
It will not help you, because you cannot commit a transaction from a broken connection.
它不会帮助您，因为您无法从断开的连接提交事务。
What happens when a connection breaks
From the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html
连接中断时会发生什么
来自 MySQL 文档：http: //dev.mysql.com/doc/refman/5.0/en/mysql-tips.html
4.5.1.6.3. Disabling mysql Auto-Reconnect
If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back.
This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it:
4.5.1.6.3. 禁用 mysql 自动重新连接
如果 mysql 客户端在发送语句时失去与服务器的连接，它会立即自动尝试重新连接到服务器并再次发送语句。但是，即使 mysql 成功重新连接，您的第一个连接也已结束并且您之前的所有会话对象和设置都将丢失：临时表、自动提交模式以及用户定义和会话变量。此外，任何当前事务都会回滚。
这种行为对您来说可能很危险，如下例所示，服务器在您不知情的情况下在第一个和第二个语句之间关闭并重新启动：
Also see: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html
另请参阅：http: //dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html
How to diagnose and fix this
To check for auto-reconnection:
如何诊断和解决此问题
要检查自动重新连接：
If an automatic reconnection does occur (for example, as a result of calling mysql_ping()), there is no explicit indication of it. To check for reconnection, call
mysql_thread_id()to get the original connection identifier before callingmysql_ping(), then callmysql_thread_id()again to see whether the identifier has changed.
如果确实发生了自动重新连接（例如，作为调用 mysql_ping() 的结果），则没有明确的指示。要检查重新连接，请
mysql_thread_id()在调用 之前调用获取原始连接标识符mysql_ping()，然后mysql_thread_id()再次调用以查看标识符是否已更改。
Make sure you keep your last query (transaction) in the client so that you can resubmit it if need be.
And disable auto-reconnect mode, because that is dangerous, implement your own reconnect instead, so that you know when a drop occurs and you can resubmit that query.
确保您将上次查询（交易）保留在客户端中，以便您可以在需要时重新提交。
并禁用自动重新连接模式，因为这很危险，请改为实现您自己的重新连接，以便您知道何时发生丢弃并可以重新提交该查询。
回答by Sanghyun Lee
Although there won't be any remaining transaction in the case, as @Johan said, you can see the current transaction list in InnoDB with the query below if you want.
尽管在这种情况下不会有任何剩余事务，但正如@Johan 所说，如果需要，您可以使用下面的查询查看 InnoDB 中的当前事务列表。
SELECT * FROM information_schema.innodb_trx\G
SELECT * FROM information_schema.innodb_trx\G
From the document:
从文件：
The INNODB_TRX table contains information about every transaction (excluding read-only transactions) currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.
INNODB_TRX 表包含有关当前在 InnoDB 中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁定、事务何时开始以及事务正在执行的 SQL 语句（如果有）。
回答by Marc B
You can use show innodb status(or show engine innodb statusfor newer versions of mysql) to get a list of all the actions currently pending inside the InnoDB engine. Buried in the wall of output will be the transactions, and what internal process ID they're running under.
您可以使用show innodb status（或show engine innodb status对于较新版本的 mysql）来获取当前在 InnoDB 引擎内挂起的所有操作的列表。隐藏在输出墙中的是交易，以及它们在什么内部进程 ID 下运行。
You won't be able to force a commit or rollback of those transactions, but you CAN kill the MySQL process running them, which does essentially boil down to a rollback. It kills the processes' connection and causes MySQL to clean up the mess its left.
您将无法强制提交或回滚这些事务，但您可以终止运行它们的 MySQL 进程，这实际上归结为回滚。它会终止进程的连接并导致 MySQL 清理其左侧的混乱。
Here's what you'd want to look for:
这是您要查找的内容：
------------
TRANSACTIONS
------------
Trx id counter 0 140151
Purge done for trx's n:o < 0 134992 undo n:o < 0 0
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 17004, OS thread id 140621902116624
MySQL thread id 10594, query id 10269885 localhost marc
show innodb status
In this case, there's just one connection to the InnoDB engine right now (my login, running the showquery). If that line were an actual connection/stuck transaction you'd want to terminate, you'd then do a kill 10594.
在这种情况下，现在只有一个到 InnoDB 引擎的连接（我的登录名，运行show查询）。如果该行是您想要终止的实际连接/卡住事务，那么您将执行kill 10594.
