Geek Answers Handbook

MySQL - How to check if a START TRANSACTION operation is active

I noticed that START TRANSACTION automatically COMMIT previous queries. Due to this and the fact that I have several stored procedures called until the end of the whole transaction, I need to check if I am inside START TRANSACTION or not. As I read the manual, I realized that autocommit is set to false inside a START TRANSACTION , but that doesn't look like this. I wrote 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'; shows autocommit = ON while I was expecting the second to be autocommit = OFF.

How to check if I am inside START TRANSACTION ?

I need to do this check because I have procedure1 that needs START TRANSACTION , then it calls procedure2, which also needs START TRANSACTION . But suppose I have a third different_procedure procedure, which should also call procedure2, but in this case different_procedure does not use START TRANSACTION . In this scenario, I need procedure2 to check if START TRANSACTION been started. Hope this is clear enough.

thanks

+5
source share
2 answers

You can create a function that will use an error that can only occur in a transaction:

 DELIMITER // CREATE FUNCTION `is_in_transaction`() RETURNS int(11) BEGIN DECLARE oldIsolation TEXT DEFAULT @@TX_ISOLATION; DECLARE EXIT HANDLER FOR 1568 BEGIN -- error 1568 will only be thrown within a transaction RETURN 1; 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 TX_ISOLATION = oldIsolation; RETURN 0; END// DELIMITER ;

Check the function:

 set @within_transaction := null; set @out_of_transaction := null; begin; set @within_transaction := is_in_transaction(); commit; set @out_of_transaction := is_in_transaction(); select @within_transaction, @out_of_transaction;

Result:

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

With MariaDB you can use @@in_transaction

+5
source

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

Transactions cannot be nested. This is the result of an implicit commit executed for any current transaction when issuing a START TRANSACTION instruction or one of its synonyms.

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

See also Does setting autocommit = 0 inside a transaction?

+1
source

More articles:


All Articles