Getting the current transaction ID with MySQL

Question

Is it possible with MySQL 5.5 to get the current transaction ID? Something like...

BEGIN;
SELECT CURRENT_XID(); -- foo
...
SELECT CURRENT_XID(); -- also foo
ROLLBACK;

SELECT CURRENT_XID(); -- NOT foo

The actual value isn't important, as long as I can get some unique identifier that will always return the same value throughout the same transaction.

Solution 1

Here is a bad solution:

CREATE FUNCTION CURRENT_XID() RETURNS VARCHAR(18)
BEGIN
    RETURN (SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX 
            WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID());
END

1. Returns NULL if your only doing read only operations inside of a transaction.
2. Most importantly the INNODB_TRX is not synchronised with the transactions. So if you call CURRENT_XID() immediately after ending the transaction you will get a value back, but then wait a second and run it against and you will get NULL.

Solution 2

Have a gander at http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html

There is no simple function to return that information but you could issues a special query look for it in that table and fetch the transaction_id

Solution 3

When using MySQL or MariaDB, you can execute the following SQL query to get the current transaction id:

SELECT tx.trx_id
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id()

The innodb_trx view in the information_schema catalog provides information about the currently running database transactions. Since there can be multiple transactions running in our system, we need to filter the transaction rows by matching the session or database connection identifier with the currently running session.

Note that, starting with MySQL 5.6, only read-write transactions will get a transaction identifier.

Because assigning a transaction id has a given overhead, read-only transactions skip this process. This read-only transaction optimization works the same way in MariaDB, meaning that a transaction id is only assigned for read-write transactions only.