在MySQL中显示未结交易 [英] Display open transactions in MySQL

问题描述

我没有提交就做了一些查询.然后应用程序停止了.

I did some queries without a commit. Then the application was stopped.

如何显示这些未清交易并提交或取消它们?

How can I display these open transactions and commit or cancel them?

推荐答案

如何显示这些未清交易并提交或取消交易?

How can I display these open transactions and commit or cancel them?

没有打开的事务，MySQL将在断开连接时回滚该事务.
您无法提交交易(IFAIK).

There is no open transaction, MySQL will rollback the transaction upon disconnect.
You cannot commit the transaction (IFAIK).

您使用显示线程

SHOW FULL PROCESSLIST  

请参阅: http://dev.mysql.com/doc/refman/5.1/zh-CN/thread-information.html

这将无济于事，因为您无法通过断开的连接来提交事务.

It will not help you, because you cannot commit a transaction from a broken connection.

连接断开时会发生什么
从MySQL文档中: http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html

What happens when a connection breaks
From the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html

4.5.1.6.3.禁用mysql自动重新连接

4.5.1.6.3. Disabling mysql Auto-Reconnect

如果mysql客户端在发送一条语句时失去了与服务器的连接，它将立即自动尝试重新连接一次到服务器并再次发送该语句. 但是，即使mysql成功重新连接，您的第一个连接也已结束，并且您之前的所有会话对象和设置都丢失了:临时表，自动提交模式和用户定义的和会话变量.另外，任何当前交易都会回滚.

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:

另请参见: http://dev.mysql.com /doc/refman/5.0/en/auto-reconnect.html

如何诊断和解决此问题
要检查自动重新连接，请执行以下操作:

How to diagnose and fix this
To check for auto-reconnection:

如果确实发生了自动重新连接(例如，由于调用mysql_ping())，则没有明确的指示.要检查是否重新连接，请在调用mysql_ping()之前先调用mysql_thread_id()获取原始连接标识符，然后再次调用mysql_thread_id()以查看标识符是否已更改.

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 calling mysql_ping(), then call mysql_thread_id() again to see whether the identifier has changed.

确保将最后一个查询(事务)保存在客户端中，以便在需要时可以重新提交.
并禁用自动重新连接模式，因为这样做很危险，请改为执行自己的重新连接，以便您知道何时发生丢弃并可以重新提交该查询.

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.

这篇关于在MySQL中显示未结交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！