Display open transactions in MySQL

Question

I made some queries without committing. Then the application was stopped.

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

Answer 1

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

There is no open transaction, MySQL will cancel the transaction when reconnecting.
You cannot complete a transaction (IFAIK).

You show streams with

SHOW FULL PROCESSLIST 

See: http://dev.mysql.com/doc/refman/5.1/en/thread-information.html

This will not help, because you cannot complete a transaction from a broken connection.

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

4.5.1.6.3. Disabling mysql auto-connect

If the mysql client loses its connection with the server when sending the instruction, it immediately and automatically tries to reconnect to the server and resubmit the request. However , even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings have been lost : temporary tables, autocomplete mode, and user and session variables. In addition, any current transaction is reverted back .

This can be dangerous for you, as in the following example, when the server was disconnected and restarted between the first and second statements without knowing it:

See also: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html

How to diagnose and fix this
To check auto reconnect:

If automatic reconnection occurs (for example, as a result of calling mysql_ping ()), then there is no explicit indication of it. To check reconnection, call mysql_thread_id() to get the original connection identifier before calling mysql_ping() , then call mysql_thread_id() again to see if the identifier has changed.

Make sure that you save your last request (transaction) on the client so that it can be resent if necessary.
And turn off auto-reconnect because it is dangerous, instead do your own reconnection so you know when the crash occurs and you can resubmit this request.

Answer 2

You can use show innodb status (or show engine innodb status for newer versions of mysql) to get a list of all the actions that are currently expected within the InnoDB engine. Buried in the output wall are transactions and what internal process identifier do they work with.

You cannot force a transaction or rollback these transactions, but you can kill the MySQL process that runs them, which essentially comes down to rollback. It kills the connection of processes and makes MySQL clear the mess of its left.

Here is what you would like to look for:

 ------------ TRANSACTIONS ------------ Trx id counter 0 140151 Purge done for trx 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 is only one connection to the InnoDB engine right now (my login executing the show request). If this line was the actual connection / stuck transaction you would like to terminate, you should do kill 10594 .

Answer 3

Although in this case there will be no remaining transaction, as @Johan said, you can see the current list of transactions in InnoDB with the request below if you want.

SELECT * FROM information_schema.innodb_trx\G

From the doc :

The INNODB_TRX table contains information about all the transactions (excluding read-only transactions) that are currently running inside InnoDB, including whether the transaction is waiting for a lock when the transaction starts and the SQL statement executed by the transaction, if any.