MySQueries Revealed: Transaction-in-motion condition detection solution

Every now and then, you want to be able to do certain things if you're inside a transaction that you wouldn't otherwise do, or conversely do some things only if you're not inside a transaction. MySQL doesn't give you a simple way to do that. You might think just checking the value of @@autocommit would do it, but no such luck, because that doesn't work if the transaction was started with start transaction (or one of its aliases, e.g. begin).

I found one creative solution, which uses savepoints. The idea being that if you call savepoint name it will only create the savepoint in question if you are in a transaction. If you then release savepoint name it will throw a "doesn't exist" error (code 1305). Throw in a handler for that error and we have an instant conditional test for whether or not we are in a transaction.

Only problem is, it doesn't work. At least not always. You see, "[a] new savepoint level is created when a stored function is invoked or a trigger is activated." Which means that the savepoint command does in fact work inside a function or trigger, even if we're not inside a transaction. So we can't depend on that to fail in all cases where we might need to detect a transaction in progress.

So after some digging, I did find one thing that will reliably behave differently when inside a transaction, namely set transaction isolation level level. It will fail with a nice 1568 error if there is a transaction in progress as it is intended to set the level for a transaction that has not yet begun. Great, we just need to handle that error and voila!... Except we don't necessarily want to really change the isolation level, do we? So we want to end up with the same isolation level as we started with. And using dynamic SQL to set the isolation level to its current value is a non-starter, as prepare and execute are verboten inside stored functions and triggers, the very cases which we are working so hard to handle properly.

Fortunately there are two ways to set the isolation level, only one of which has the 1568 error. If you set tx_isolation=level (using the hyphenated form as returned by "select @@tx_isolation"), there are no errors. So at last we have a way to detect transactions anywhere and everywhere, without leaving a trace:


delimiter $
create function is_transaction() returns int
begin
declare old_level text default @@tx_isolation;
declare exit handler for 1568 begin return 1; end;
set transaction isolation level repeatable read;
set tx_isolation = old_level;
return 0;
end$
delimiter ;

Trackback URL for this post:

http://tigretigre.com/trackback/18

Comments

No good for 5.0

Turns out the error that I am relying on for this trick doesn't occur in MySQL 5.0, and I haven't really been able to find a suitable substitute. The closest I can come right now is trying to start an XA transaction, but there are possible issues with xid conflicts between parallel processes that don't admit a satisfactory solution. It also occurs to me that it's probably wise to check the value of @@autocommit first, as if it is 0, then you know you are in a transaction.