MySQL-如何检查START TRANSACTION是否处于活动状态
我注意到START TRANSACTION
自动COMMIT
先前的查询.因此,在整个事务结束之前我已经调用了多个存储过程,因此我需要检查我是否在START TRANSACTION
内部.阅读手册后,我了解到START TRANSACTION
内的autocommit设置为false,但看起来不是这样.我编写了以下过程:
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
但是每个show session variables like 'autocommit';
都显示autocommit = ON,而我希望第二个显示为autocommit = OFF.
But each show session variables like 'autocommit';
show autocommit=ON while I expected the second to be autocommit=OFF.
如何检查我是否在START TRANSACTION
内?
我需要执行此检查,因为我的procedure1需要START TRANSACTION
,然后它调用也需要START TRANSACTION
的procedure2.但是,让我们假设我有第三个过程different_procedure,它也需要调用procedure2,但是在这种情况下,different_procedure不使用START TRANSACTION
.在这种情况下,我需要procedure2来检查是否启动了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 ;
测试功能:
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;
结果:
@within_transaction | @out_of_transaction
--------------------|--------------------
1 | 0
通过MariaDB,您可以使用@@in_transaction
With MariaDB you can use @@in_transaction