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