Show currently active transaction(s) in mysql

Question

Is there a way to ask MySQL to tell me if I am currently inside a transaction? I am in an interactive session from the commandline mysql client, I have opened and closed a couple of transactions, and now I should not be in a transaction but it's acting as if maybe I am. So how do I examine/verify my connection state? I tried my luck and typed SHOW TRANSACTION, but there's no such thing.

Due diligence:

I've looked at other questions (and the transaction documentation, of course), and didn't find an answer. This question is about recovering transactions after a connection has been dropped. This one seems to be asking if there are transactions active in other threads. I want to see if my connection is in a transaction.

I also tried SELECT @@AUTOCOMMIT FROM DUAL, as suggested here. But it doesn't help: When I start a transaction, its value doesn't change but remains 1 ("autocommit enabled").

Answer 1

information_schema.innodb_trx will tell you if you're in a transaction inside InnoDB. The catch is that if you haven't yet accessed any tables or created a read snapshot explicitly, you're only in a transaction inside MySQL (the "server layer,") and not inside InnoDB (the "storage engine layer").

mysql> SELECT count(1) FROM information_schema.innodb_trx 
    -> WHERE trx_mysql_thread_id = CONNECTION_ID();
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

Okay, I didn't before, but now I have a transaction, and...

mysql> SELECT count(1) FROM information_schema.innodb_trx 
    -> WHERE trx_mysql_thread_id = CONNECTION_ID();
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

...there's still nothing for my current CONNECTION_ID() in innodb_trx.

But, if I write to or just read from an InnoDB table...

mysql> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
|      301 |
+----------+
1 row in set (0.00 sec)

...now, I can see my transaction, because InnoDB is aware of it.

mysql> SELECT count(1) FROM information_schema.innodb_trx 
    -> WHERE trx_mysql_thread_id = CONNECTION_ID();
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

Let's verify that it's gone...

mysql> SELECT count(1) FROM information_schema.innodb_trx 
    -> WHERE trx_mysql_thread_id = CONNECTION_ID();
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Now, tell the server to tell the storage engine that my MVCC view starts now, not later:

mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

Note that this doesn't actually give me a "consistent" snapshot unless my isolation level allows it. But it's enough that InnoDB now knows I'm here.

mysql> SELECT count(1) FROM information_schema.innodb_trx 
    -> WHERE trx_mysql_thread_id = CONNECTION_ID();
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

...and InnoDB learns of the transaction immediately.

---

Now, there is another way to determine whether you're in a transaction right now. Or, more correctly, I should say there's another way to determine that you are not in a transaction right now.

I use this for stored procedures that must be run within a transaction -- the caller is responsible for starting and committing or rolling back, and the procedure will refuse to run if there's not an active transaction. How?

The procedure calls another procedure that silently succeeds if I do have a transaction, but throws an exception if I don't. When one procedure calls a second procedure, and the second procedure throws an exception, the first procedure terminates with that same exception, unless the first procedure has installed a HANDLER to catch the error.

So when my outer procedure calls this procedure, if there's a transaction active, nothing happens, and the outer procedure is allowed to run:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL mysql.require_transaction;
Query OK, 0 rows affected (0.00 sec)

^^^ this ^^^ is what I do near the beginning, inside my stored procedures that need to run only if they are called from within a transaction.

No error, we were in a transaction. If this had been another procedure calling it, that procedure would have simply continued on to the next instruction.

But if we call my require_transaction procedure and we aren't in a transaction:

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL mysql.require_transaction;
ERROR 1644 (42000): you must have an active database transaction before attempting
this operation

Neat. We crash our caller with a custom error message. How?

DELIMITER $$
CREATE PROCEDURE `mysql`.`require_transaction`()
BEGIN

-- test the session's transactional status, 
-- throwing an exception if we aren't in a transaction,
-- but finishing successfully if we are

DECLARE CONTINUE HANDLER 
        FOR 1305 
        SIGNAL SQLSTATE '42000' 
        SET MESSAGE_TEXT = 'you must have an active database transaction before attempting this operation';

SAVEPOINT `we created to be sure you were in a transaction`;
ROLLBACK TO SAVEPOINT `we created to be sure you were in a transaction`;

END $$
DELIMITER ;

This has been my long-time workaround for what I believe to be a significant oversight in the design of MySQL -- the apparent inability to determine definitively, from the SQL interface, whether you are currently in a transaction. Here's why this works:

• Creating a SAVEPOINT and immediately rolling back to it is essentially a no-op. As long as there wasn't already an active savepoint with the same name, no harm, no foul. I've used the highly improbable name we created to be sure you were in a transaction for my SAVEPOINT.

• Creating a SAVEPOINT can't be done if you're not in a transaction, but this actually fails silently.

• Rolling back to a SAVEPOINT that doesn't exist will throw error 1305, so if you aren't in a transaction, it wasn't created, and now it won't exist, and there's your error. If you are in a transaction, the SAVEPOINT is created and then released, leaving your transaction as it was.

mysql> ROLLBACK TO SAVEPOINT `we created to be sure you were in a transaction`;
ERROR 1305 (42000): SAVEPOINT we created to be sure you were in a transaction does not exist
mysql>

Hahahaha that's a spiffy hack. Now you see why I used the name I did for my phony savepoint -- "does not exist" is appended to the object name, to form the error message.

On MySQL 5.1, which doesn't have SIGNAL, my require_transaction stored procedure simply terminates with that native error, which is almost meaningful... or at least meaningful enough that somebody will come ask the DBA (me) what it means.

To make it prettier, in MySQL Server 5.5 and up, we catch error 1305 with a CONTINUE HANDLER that allows us to set our own custom error message using SIGNAL.

Setting and then immediately rolling back to a savepoint is a tragically hacky but surefire way to determine whether you are in a transaction.

Answer 2

Have you trying to use 22.31.4 The INFORMATION_SCHEMA INNODB_TRX Table?

SELECT
  COUNT(`trx_id`) `inTransaction?`
FROM
  `INFORMATION_SCHEMA`.`INNODB_TRX`
WHERE
  `trx_mysql_thread_id` = CONNECTION_ID();