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

unchanged
Title
How to use nested DB transactions (MySQL 5+, PostgreSQL)
unchanged
Category
How-tos
unchanged
Tags
db, mysql, postgresql, transaction, nested
changed
Content
SourceOriginal source code takenand idea
are from: [PHP, PDO & Nested
Transactions](http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/).

Tested with: MySQL 5.1.30 + Yii 1.1.8.

Problem:
Say there is service layer in an application, and one service may use others. No
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 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() ||
$this->transLevelif($this->transLevel ==
0)0 || !$this->nestable()) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }

        $this->transLevel++;
    }

    public function commit() {
        $this->transLevel--;

        if(!$this->nestable() ||
$this->transLevelif($this->transLevel ==
0)0 || !$this->nestable()) {
            parent::commit();
        } else {
            $this->exec("RELEASE SAVEPOINT
LEVEL{$this->transLevel}");
        }
    }

    public function rollBack() {
        $this->transLevel--;

        if(!$this->nestable() ||
$this->transLevelif($this->transLevel ==
0)0 || !$this->nestable()) {
            parent::rollBack();
        } else {
            $this->exec("ROLLBACK TO SAVEPOINT
LEVEL{$this->transLevel}");
        }
    }
}
~~~

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 ;)