source code taken from: PHP, PDO & Nested Transactions

tested with: MySql 5.1.30

Problem: Say there is service layer in an application, and one service may use others. No each service deals will complex business logic will needs to wrap that into transactions.

If there are services A and B here's how it might happen:

class ServiceA {
    public function transA() {
        $trans = Yii::app()->db->beginTransaction();
        // code
        Yii::app()->serviceB->transB();
        // code
        // exception handling and stuff
        $trans->commit();
    }
}

class ServiceB {
    public function transB() {
        $trans = Yii::app()->db->beginTransaction();
        // code
        // exception handling and stuff
        $trans->commit();
    }
}

With default Yii implementation, PDO will throw exception saying there already is active transaction.

But there's a solution:)

First, you'll need to extend PDO class and save it in your the protected/components directory:

class NestedPDO extends PDO {
    // Database drivers that support SAVEPOINTs.
    protected static $savepointTransactions = array("pgsql", "mysql");

    // The current transaction level.
    protected $transLevel = 0;

    protected function nestable() {
        return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
                        self::$savepointTransactions);
    }

    public function beginTransaction() {
        if(!$this->nestable() || $this->transLevel == 0) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }

        $this->transLevel++;
    }

    public function commit() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::commit();
        } else {
            $this->exec("RELEASE SAVEPOINT LEVEL{$this->transLevel}");
        }
    }

    public function rollBack() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::rollBack();
        } else {
            $this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->transLevel}");
        }
    }
}

Then, you'll need to alter the behaviour of [CDbConnection::createPdoInstance()]. You can do this by making a subclass of it in protected/components/NestedDbConnection.php

There, change $pdoClass to your class name (NestedPDO in this example):

class NestedDbConnection extends CDbConnection
{
    protected function createPdoInstance()
    {
        $pdoClass='NestedPdo';
        if(($pos=strpos($this->connectionString,':'))!==false)
        {
            $driver=strtolower(substr($this->connectionString,0,$pos));
            if($driver==='mssql' || $driver==='dblib')
              $pdoClass='CMssqlPdoAdapter';
        }
        return new $pdoClass($this->connectionString,$this->username,
                             $this->password,$this->attributes);
    }
}

Note that $this->_attributes was also changed to $this->attributes so the subclassing will work.

Now you can add the class name to the db configuration array in protected/config/main.php

'db'=>array(
            'class'=>'NestedDbConnection',
            'connectionString' => ...
        ),

That's it, there you go;)