MySQL 5.6 – Transaction Identifier Possibilities

MySQLmysql-5.6transaction

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?

Best Answer

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.

Related Question