How can a PHP PDO object figure out if it's already in a MySQL Transaction?

Question

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();
}

Answer 1

One way is to create your own PDOConnect class, which has a $hasTransaction variable. Then you just check that. An example can be found on the comments of the beginTransaction function on php.net here http://www.php.net/manual/en/pdo.begintransaction.php#81022

That would be my preference. Granted that example will need tweaking and dressing up etc, but should be a good foundation to start from.

Side Note Remember your table should be INNODB for transactions to work. And since you need to be in INNODB for transactions to work, you should take prodigitalson's advice and use foreign key constraints.

Answer 2

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:

• How do detect that transaction has already been started?
• Multiple Service Layers and Database Transactions

Answer 3

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.