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:  


```phpWith 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: ```php class InsNestedPDO extends PDO {
// Database drivers that support SAVEPOINTs.
protected static $savepointTransactions = array("pgsql", "mysql");public function beginTransaction() {
if(!$this->nestable()transLevel == 0 || !$this->transLevel == 0nestable()) {
parent::beginTransaction();
} else {$this->transLevel--;

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

if(!$this->nestable()transLevel == 0 || !$this->transLevel == 0nestable()) {
parent::rollBack();
} else {```

Then, you'll need to alter [CDbConnection::createPdoInstance()]. There, change $pdoClass to your class name (InsPDO in this example).
 
 
 
```php 
protected function createPdoInstance()
 
{Now you can use it in db configuration array in `protected/config/main.php`:
 
 
 
```php 
'db'=>array( $ 'pdoClass='Ins' => '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); 'connectionString' => ...
 
),
 
```
 
 
That's it, there you go ;) } ```
 
 
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;)
 

 
Links
 
-----
 
- [Chinese version](http://www.itkuaixun.com/bbs/thread-206-1-1.html "Chinese version")