How to display outstanding transactions in MySQL

Question

I ran some queries without submitting. Then the application is stopped.

How do I display these open transactions and submit or cancel them?

Answer 1

Although there won't be any remaining transactions in this case, as @Johan said, if you wish, you can view the current list of transactions in InnoDB via the query below.

Select *

from information_schema.innodb_trx\G

From Documentation:

Answer 2

Without an open transaction, MySQL will roll back the transaction on disconnect.
You cannot commit the transaction (IFAIK).

You display threads using

SHOW FULL PROCESSLIST

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

This won't help you since you can't commit a transaction from a broken connection.

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

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

How to diagnose and resolve this issue
Check for automatic reconnection:

Make sure to keep the last query (transaction) in the client so that you can resubmit it if needed.
And disable automatic reconnect mode as this is dangerous, implement your own reconnect instead so you know when a drop occurs and can resubmit that query.