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

Changes

Title changed

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

Category changed

TutorialHow-tos

Yii version unchanged

Tags unchanged

db, mysql, postgresql, transaction, nested

Content changed

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:
[...]
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:
[...]
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 the behaviour of [CDbConnection::createPdoInstance()]. You can do this by making a subclass of it in protected/components/NestedDbConnection.php
 
 
There, change $pdoClass toNow your class name (NestedPDO in this example):
 
 
```php 
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 thean use it in db configuration array in `protected/config/main.php`: ```php 'db'=>array(        'c'pdoClass' => 'NestedDbConnectionPDO',         'connectionString' => ...         ), ``` That's it, there you go ;)
 
 
Links
 
-----
 
- [Chinese version](http://www.itkuaixun.com/bbs/thread-206-1-1.html "Chinese version")
41 0
26 followers
Viewed: 66 658 times
Version: 1.1
Category: How-tos
Tags: db, mysql, nested, postgresql, transaction
Written by: mindeh
Last updated by: Boaz
Created on: Jun 30, 2009
Last updated: 8 years ago
Update Article
Revert to #3 | Revert to #7

Revisions

View all history