Need help with nested atomic operations involving PDO transactions

Question

I have two distinct modules that can be used independently, but Module2 is dependent on Module1.

Module2 has an operation that needs to be atomic, and it calls an operation in Module1 that also needs be atomic.

Assuming I have set PDO::ATTR_ERRMODE to PDO:ERRMODE_EXCEPTION, the following heavily genericised and snipped code yields this: PHP Fatal error: Uncaught exception 'PDOException' with message 'There is already an active transaction'

Module1:

<?php
class Module1
{
    ...
    public function atomicOperation($stuff)
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare(...);
            ...
            $this->pdo->commit();
        }
        catch (Exception $ex) {
            $this->pdo->rollBack();
            throw $ex;
        }
    }
}

Module2:

<?php
class Module2
{
    public $module1;
    ...
    public function atomicOperation($stuff)
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare(...);
            ...
            $this->module1->atomicOperation($stuff);
            ...
            $this->pdo->commit();
        }
        catch (Exception $ex) {
            $this->pdo->rollBack();
            throw $ex;
        }
    }
}

I'm not sure the best way to go about this - the nested operation will definitely be called independently and absolutely must be atomic when called in its own right. Placing the onus on the class' user to manage the transaction and preserve atomicity is not desirable as I am certain the users of the class will never enforce it.

Answer 1

You need to create your own class which extends PDO and manage transaction. Something like :

<?php
class Db extends PDO{
  private $_inTrans = false;

  public function beginTransaction(){
    if(!$this->_inTrans){
      $this->_inTrans = parent::beginTransaction();
    }
    return $this->_inTrans;
  }

  public function commit(){
    if($this->_inTrans){
      $this->_inTrans = false;
      return parent::commit();
    }
    return true;
  }

  public function rollBack(){
    if($this->_inTrans){
      $this->_inTrans = false;
      return parent::rollBack();
    }
    return true;
  }

  public function transactionStarted(){
    return $this->_inTrans;
  }

}

You still need to check all queries passed in case some transaction is started there.

Module 1 :

<?php
class Module1
{
    ...
    public function atomicOperation($stuff)
    {
        $transactionAlreadyStarted = $this->pdo->transactionStarted();
        if(!$transactionAlreadyStarted){
            $this->pdo->beginTransaction();
        }
        try {
            $stmt = $this->pdo->prepare(...);
            ...

            if(!$transactionAlreadyStarted && $this->pdo->transactionStarted()){
                $this->pdo->commit();
            }
        }
        catch (Exception $ex) {
            if($this->pdo->transactionStarted()){
                $this->pdo->rollBack();
            }
            throw $ex;
        }
    }
}

Module 2 :

<?php
class Module2
{
    public $module1;
    ...
    public function atomicOperation($stuff)
    {
        $transactionAlreadyStarted = $this->pdo->transactionStarted();
        if(!$transactionAlreadyStarted){
            $this->pdo->beginTransaction();
        }
        try {
            $stmt = $this->pdo->prepare(...);
            ...
            $this->module1->atomicOperation($stuff);
            ...
            if(!$transactionAlreadyStarted && $this->pdo->transactionStarted()){
                $this->pdo->commit();
            }
        }
        catch (Exception $ex) {
            if($this->pdo->transactionStarted()){
                $this->pdo->rollBack();
            }
            throw $ex;
        }
    }
}

Answer 2

Arkh's solution, although correct, is unreliable because commit() and rollback() are basically lying. Calling rollback() or commit() may return true when nothing is really happening.

Instead, you should use SAVEPOINTs.

Savepoints are supported in some form or other in database systems like PostgreSQL, Oracle, Microsoft SQL Server, MySQL, DB2, SQLite (since 3.6.8), Firebird and Informix (since version 11.50xC3). Savepoints are also defined in the SQL standard.

In your custom DB class, you override commit, rollback and beginTransaction() and use SAVEPOINTs where appropriate. You could also try to implement inTransaction(), although beware that implicit commits (CREATE TABLE, etc) in MySQL would mess up the reliability of this.

This blog post from 2008 actually has an implementation of what I say.

This code will only attempt to use the SAVEPOINT code if you’re using a database driver that supports it