I have two complicated PHP objects, each of which has data in a few MySQL tables.
Sometimes, I just need to remove one object A from the database, and that takes 3 SQL statements.
Sometimes, I need to remove one object B from the database, which is takes 4 SQL statements, and which also needs to find and remove all of the object A's that object B owns.
So inside the function delete_A(), I execute those statements inside of a transaction. Inside of the function that delete_B(), I want to run one great big transaction that covers the activities inside of delete_A(). If the whole atom of deleting a B fails, I need to restore all of its A's in the rollback.
How do I update the definition of delete_A() to only open a new transaction if there isn't already a bigger transaction running.
I expected to be able to do something like this, but the autocommit
attribute doesn't appear to get changed by beginTransaction()
function delete_A($a){
global $pdo;
$already_in_transaction = !$pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);
if(!$already_in_transaction){
$pdo->beginTransaction();
}
//Delete the A
if(!$already_in_transaction){
$pdo->commit();
}
}
function delete_B($b){
global $pdo;
$pdo->beginTransaction();
foreach($list_of_As as $a){
delete_A($a);
}
$pdo->commit();
}
PDO::ATTR_AUTOCOMMIT
is not an indicator attribute, it's a control attribute. It controls whether SQL statements implicitly commit when they finish.
You can call PDO::inTransaction()
which returns 0
if you have no transaction in progress, and 1
if you have a transaction outstanding that needs to be committed or rolled back. However, this function is not documented, so it's hard to say if it's safe to depend on it being present in all future versions of PDO.
I recommend that PHP developers don't try to manage transactions within function or class scope. You should manage transactions at the top-level of the application.
See also:
I ended up just using the exception thrown by ->beginTransaction()
to figure out whether I was in a transaction, and using that to decide whether to commit in the inner loop. So delete_A()
ended up looking like:
function delete_A($a){
global $pdo;
try {
$pdo->beginTransaction();
} catch (PDOException $e) {
$already_in_transaction = true;
}
//Delete the A
if(!$already_in_transaction){
$pdo->commit();
}
}
And delete_B()
works without modification.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With