How to use nested DB transactions (MySQL 5+, PostgreSQL)

Original source code and idea are from: 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:

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:

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->transLevel == 0 || !$this->nestable()) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }
 
        $this->transLevel++;
    }
 
    public function commit() {
        $this->transLevel--;
 
        if($this->transLevel == 0 || !$this->nestable()) {
            parent::commit();
        } else {
            $this->exec("RELEASE SAVEPOINT LEVEL{$this->transLevel}");
        }
    }
 
    public function rollBack() {
        $this->transLevel--;
 
        if($this->transLevel == 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:

'db'=>array(
     'pdoClass' => 'NestedPDO',
     'connectionString' => ...
),

That's it, there you go ;)

Links ¶

Chinese version

Total 1 comment

#5264

kamermans at 2011/09/27 09:38pm

This is outstanding!

Really great use of Savepoints! Thanks for sharing - it's things like this that make me confidently recommend Yii for real enterprise-level applications.

If you're reading this saying to yourself "I can't even think of a reason why I would want to use a single transaction, let alone a nested transaction", please take the time to consider data integrity in your applications and pass it on to your fellow programmers :)

Please login to leave your comment.

How to use nested DB transactions (MySQL 5+, PostgreSQL)

Links ¶

Total 1 comment

Leave a comment

Yii Supporters