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.
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
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:
create function is_transaction() returns int
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;