Difference between #1 and #2 of
How to use nested DB transactions (MySQL 5+, PostgreSQL)

Revision #2 has been created by wei on Oct 26, 2010, 3:06:27 AM with the memo:

formatting, tags
« previous (#1) next (#3) »

Changes

Title unchanged

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

Category unchanged

Tutorials

Yii version unchanged

Tags changed

db, mysql, postgresql

Content changed


 
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.30
[...]
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.
[...]
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;)
 
41 0
26 followers
Viewed: 46 818 times
Version: 1.1
Category: How-tos
Written by: mindeh
Last updated by: Boaz
Created on: Jun 30, 2009
Last updated: 2 years ago
Update Article

Revisions

View all history