MySQL - How to check if START TRANSACTION is active

Question

I have noticed that START TRANSACTION automatically COMMIT the previous queries. Because of this and the fact that I have several stored procedure called before the end of the whole transaction, I need to check if I am inside a START TRANSACTION or not. Reading the manual I understood that autocommit is set to false inside a START TRANSACTION, but it doesn't seem like this. I have written the following procedure:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `test_transaction`()
BEGIN

show session variables like 'autocommit';

start transaction;

show session variables like 'autocommit';

COMMIT;

show session variables like 'autocommit';

END

But each show session variables like 'autocommit'; show autocommit=ON while I expected the second to be autocommit=OFF.

How can I check if I am inside a START TRANSACTION?

I need to perform this check because I have procedure1 that need START TRANSACTION then it calls procedure2 that also need START TRANSACTION. But let's suppose I have a third procedure different_procedure that also need to call procedure2 but in this case different_procedure doesn't use START TRANSACTION. In this scenario I need procedure2 to check if START TRANSACTION was initiated. I hope this is enough clear.

Mason

Answer 1

You can create a procedure that will exploit an error which can only occur within a transaction:

DELIMITER //
CREATE PROCEDURE `is_in_transaction`(OUT $transaction bool)
BEGIN
    DECLARE oldIsolation TEXT DEFAULT @@TRANSACTION_ISOLATION;
    DECLARE EXIT HANDLER FOR 1568 BEGIN
        -- error 1568 will only be thrown within a transaction
        SET $transaction = true;
    END;
    -- will throw an error if we are within a transaction
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    -- no error was thrown - we are not within a transaction
    SET TRANSACTION_ISOLATION = oldIsolation;
    SET $transaction = false;
END//
DELIMITER ;

Test the procedure:

set @within_transaction := null;
set @out_of_transaction := null;

begin;
    CALL is_in_transaction(@within_transaction);
commit;

CALL is_in_transaction(@out_of_transaction);

select @within_transaction, @out_of_transaction;

Result:

@within_transaction | @out_of_transaction
--------------------|--------------------
                  1 |                   0

With MariaDB you can use @@in_transaction

Marika

Unfortunately in current MySQL versions the FUNCTION approach does not work anymore. See bugs.mysql.com/bug.php?id=102531 for reference. However, doing the same thing as a PROCEDURE with an out parameter still seems to work in 5.7 and 8.0. – Tranquilizer

Also @@TX_ISOLATION was removed in 8.0 and is replaced by @@TRANSACTION_ISOLATION, see dev.mysql.com/doc/refman/5.7/en/… – Tranquilizer

Answer 2

From https://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html:

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

I suspect that the problem can be solved by using SET autocommit=0; instead of START TRANSACTION;. If autocommit is already 0, it will have no effect.

See also Does setting autocommit=0 within a transaction do anything?

Kristinakristine

Thanks, that can be a solution, but it's ugly. I believe there should be a clean way to check if you are inside a "start transaction" – Mason

@Stefano Giacone Have you tried SELECT @@autocommit? – Kristinakristine

Yes, it's the same. The SET autocommit=0; solution it's really terrible, I prefer a better one if possible... – Mason

@Stefano Giacone Is there no way you could rewrite your code so that nesting doesn't occur? – Kristinakristine

That would be again a workaround, I believe there should be a way to check if "start transaction" was initiated. I added details about my code in the question. – Mason

@Stefano Giacone Unfortunately I don't know why the autocommit variable is off inside your transaction, so all I can offer is workarounds. – Kristinakristine

So you're saying that it's a strange behavior of mine environment? If you try the same code it's different on your side? – Mason