Transaction Identifier - possible with MySQL?

Question

I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if everything goes OK.

What I need is to detect if the connection at the end is still within the same transaction, as the user code can query commit/rollback and start a new transaction.

What is the industry practice for this? Can MySQL database return some transaction ticket/ID? Or MySQL store transaction only variable, which will be detectable after commit/rollback?

Answer 1

I doubt if there is a transaction id. But here are some "good practices":

• Turn off auto-reconnect. (This is a parameter on certain APIs when establishing the connection.) A disconnect terminates transactions and rolls them back. With auto-reconnect OFF, you will get an error on whatever happens next.

• Check for errors after ever SQL statement.

• Avoid spaghetti code. Arrange the code so that it is obvious that there is no way to sneak out and do a COMMIT or ROLLBACK. I like to have the START TRANSACTION at the beginning of a subroutine and the COMMIT and ROLLBACK near the end. Then make sure that any subroutines it calls assume that they are inside a transaction.

This might help you discover if you have started another transaction in your connection:

SHOW SESSION STATUS LIKE 'Com_begin';

or

SELECT  Variable_Value
    FROM  information_schema.SESSION_STATUS
    WHERE  Variable_name = 'Com_begin';

Collect that right after doing the BEGIN or START TRANSACTION; collect it again just before your COMMIT to see if it has changed. ("Changed" == something is wrong.) Note: I am using SESSION, not GLOBAL.

Answer 2

Can MySQL database return some transaction ticket/ID? Or MySQL store transaction only variable, which will be detectable after commit/rollback?

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.

For more details, check out this article.