Display open transactions in MySQL

Question

I did some queries without a commit  Then the application was stopped   How can I display these open transactions and commit or cancel them

User · Answer

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

User · Answer

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

User · Answer

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  lt  0 134992 undo n o  lt  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

User · Answer

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  lt id gt        e g KILL 16543

[mysql] Display open transactions in MySQL

Examples related to mysql