113

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

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

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Alex
  • 32,506
  • 16
  • 106
  • 171
  • I think all your transactions are canceled upon disconnect, but not 100% sure. – Johan Sep 29 '11 at 13:57
  • What type of tables are you using? MyISAM, InnoDB, etc? – cdeszaq Sep 29 '11 at 14:05
  • @cdeszaq, obviously not MyISAM it does not have transactions, besides the question really has nothing to do with tables. – Johan Sep 29 '11 at 14:24
  • 2
    @Johan - I only gave MyISAM as an example of the table type. And it very much _does_ matter, because not all tables that support transactions behave the same way with regard to transactions on connection loss. – cdeszaq Sep 29 '11 at 14:26
  • @cdeszaq, The MySQL docs state something very different. – Johan Sep 29 '11 at 14:38

5 Answers5

85

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).

You display threads using

SHOW FULL PROCESSLIST

See: 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

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:

Also see: 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 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.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • This has nothing to do with the question. This only impacts the mysql client, and the OP is talking about a generic application, which likely means _his_ application. Additionally, since the calling application stopped, how would it be able to keep the transaction in memory? – cdeszaq Sep 29 '11 at 14:27
  • @cdeszaq, it has everything to do with the question. An application typically uses `mysqld.dll` AKA **the client** And you keep the **SQL-statement** that contains the full transaction in memory, so that you can play it back when the connection drops. Or you keep it locally on disk, so that upon restart you can resubmit it. – Johan Sep 29 '11 at 14:34
  • There is only my process list command displayed in the SHOW FULL PROCESSLIST. So I guess there are just no open transactions. The funny part is that the autoincrement_ids seem to be lost. – Alex Sep 29 '11 at 14:47
  • @alex the official docs state that, so that is documented behaviour. See the links. – Johan Sep 29 '11 at 15:23
  • 1
    Beautiful, Johan. Answered the question, and showed some consequences and the solutions to those consequences, all within a couple of paragraphs. – Gerard ONeill Dec 30 '13 at 22:31
  • "upon reconnect" or "upon disconnect"? – Peter Chaula May 26 '19 at 19:09
77

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.

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.

Sanghyun Lee
  • 21,644
  • 19
  • 100
  • 126
  • Don't suppose there's any way to tell if the transactions in that table belong to your specific request/session? – Captain Hypertext Jan 24 '20 at 12:33
  • 4
    Please note `\G` modifier at the end is only useful if you want to format query output within mysql CLI tool. If you use GUI tool like Mysql Workbench, you don't need it. – barell Jul 13 '20 at 09:32
  • 1
    @CaptainHypertext the `trx_mysql_thread_id` column corresponds to the connection ID in the `full processlist` – Eugene Pankov Jan 11 '22 at 20:27
40

You can use show innodb status (or show engine innodb status for 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.

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.

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 show query). If that line were an actual connection/stuck transaction you'd want to terminate, you'd then do a kill 10594.

Benubird
  • 18,551
  • 27
  • 90
  • 141
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • There is really no need to activly kill a connection after a timeout the connection will be killed anyway and pending transaction from a broken connection cannot be commited so they can be resubmitted without fear of duplications. – Johan Sep 29 '11 at 15:25
  • 3
    Better to kill the stuck transactions without waiting for a timeout to clean up - you risk deadlocks otherwise. – Marc B Sep 29 '11 at 15:28
  • Ah yes, +1 for that comment. Forgot about those deadlocks for a minute. – Johan Sep 29 '11 at 15:35
  • @MarcB, Why did they change it to `show engine innodb status`? – Pacerier Apr 09 '15 at 04:53
3

By using this query you can see all open transactions.

List All:

SHOW FULL PROCESSLIST

if you want to kill a hang transaction copy transaction id and kill transaction by using this command:

KILL <id>    // e.g KILL 16543
M. Hamza Rajput
  • 7,810
  • 2
  • 41
  • 36
2

With this query below, you can check how many transactions are running currently:

mysql> SELECT count(*) FROM information_schema.innodb_trx;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
Jesse
  • 1,386
  • 3
  • 9
  • 23
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129