How to display open transactions in MySQL

by

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.

More Related Contents:

  1. How to debug Lock wait timeout exceeded on MySQL?
  2. Reorder / reset auto increment primary key
  3. MySQL Trigger after update only if row has changed
  4. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  5. MySQL Query to select data from last week?
  6. Select last N rows from MySQL
  7. How do I add a custom CHECK constraint on a MySQL table?
  8. How can I modify the size of column in a MySQL table?
  9. MySQL InnoDB foreign key between different databases
  10. How to insert data to MySQL with auto-incremented column(field)?
  11. How to design a multi tenant mysql database
  12. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  13. SQL Order By Count
  14. Replace Into Query Syntax
  15. How to enable INNODB in mysql
  16. Does MySQL allows to create database with dot?
  17. What resources exist for Database performance-tuning? [closed]
  18. How to connect to MySQL from the command line
  19. Remote connect to clearDB heroku database
  20. Laravel join with 3 Tables
  21. Difference between two table structure
  22. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Where does MySQL store database files on Windows and what are the names of the files?
  27. Should I normalize my DB or not?
  28. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  29. How to store data with dynamic number of attributes in a database
  30. Automatic Rollback if COMMIT TRANSACTION is not reached

Leave a Comment