How to use nested DB transactions (MySql 5+, PgSql)

Tutorials

db, mysql, postgresql

 
source code taken from: [PHP, PDO & Nested Transactions](http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/)

tested with: MySql 5.1.30If there are services A and B here's how it might happen:

     
```php 
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.First, you'll need to extend PDO class:

 
```php 
class InsPDO 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 [CDbConnection::createPdoInstance()]. There, change $pdoClass to your class name (InsPDO in this example).  
```php 
protected function createPdoInstance() {     $pdoClass='InsPDO';     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); }
 
``` It'd probably be better OOP to extend [CDbConnection], especially since createPdoInstance is protected. But then either there are inconsistencies with private/protected properties of [CDbConnection], or I didn't spent enough time to explore how to properly extend [CDbConnection]. That's it, there you go;)