mysql 自动提交状态,MySQL-如何检查START TRANSACTION是否处于活动状态

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.

解决方案

You can create a function that will exploit an error which can only occur within 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 ;

Test 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