I am doing this in a PHP script, but it may be mostly a database question. I am trying to cancel a mysql transaction by checking some external flag.
I have a long running mysql transaction that sometimes I want to cancel and just rollback based off come factor external to the transaction itself. The same program making the connection must be the one to check the value.
I thought a basic flag where if X = true, stop and rollback the transaction would work. The problem is that using the db itself to set the flag only works if the transaction isolation level is set to read-uncommitted, because otherwise the first time the program checks the flag, it will retain the same value every other time it checks it during the transaction, even if it has been changed in the db.
What I do currently is place a function that checks the db flag wherever I want to potentially stop the transaction.
My script looks basically like this:
<?php // set transaction isolation level to read-committed $pdo->beginTransaction(); // do work CheckIfTransactionShouldContinue(); // do work CheckIfTransactionShouldContinue(); $pdo->commit();
I would rather not have to set the transaction isolation level to read-uncommitted due to an increased likelihood of (very hard to diagnose) transaction errors.
Is this the correct architecture? And if it is, am I implementing it correctly?