How to use nested DB transactions (MySqlQL 5+, PgSqlostgreSQL)

TutorialHow-tos

db, mysql, postgresql, transaction, nested

Original source code takenand idea are from: [PHP, PDO & Nested Transactions](http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/). tTested with: MySqlQL 5.1.30 + Yii 1.1.8. Problem: Say there is service layer in an application, and one service may use others. Now 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:
 
     
```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. But there's a solution :) First, you'll need to extend PDO class:
 
 
class Ins and save it in your the protected/components directory:
 
 
```php 
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()transLevel == 0 || !$this->transLevel == 0nestable()) { parent::beginTransaction(); } else { $this->exec("SAVEPOINT LEVEL{$this->transLevel}"); } $this->transLevel++; } public function commit() { $this->transLevel--; if(!$this->nestable()transLevel == 0 || !$this->transLevel == 0nestable()) { parent::commit(); } else { $this->exec("RELEASE SAVEPOINT LEVEL{$this->transLevel}"); } } public function rollBack() { $this->transLevel--; if(!$this->nestable()transLevel == 0 || !$this->transLevel == 0nestable()) { 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).
 
 
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;)
 
 
```
 
 
Now you can use it in db configuration array in `protected/config/main.php`:
 
 
 
```php 
'db'=>array(
 
     'pdoClass' => 'NestedPDO',
 
     'connectionString' => ...
 
),
 
```
 
 
That's it, there you go ;)
 
 
 
Links
 
-----
 
- [Chinese version](http://www.itkuaixun.com/bbs/thread-206-1-1.html "Chinese version")