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

26 followers

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 ΒΆ

Total 6 comments

#18487 report it
Phelipe Folgierini at 2014/11/05 08:53am
Nested Transactions with MS SQL Server and IIS

There are little changes to make it works on SQL Server and IIS. First, the NestedPDO class, as below:

class NestedPDO extends PDO {
    // Database drivers that support SAVEPOINTs.
    protected static $savepointTransactions = array("mssql", "dblib", "sqlsrv");
 
    // 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("SAVE TRANSACTION LEVEL{$this->transLevel}");
        }
 
        $this->transLevel++;
    }
 
    public function commit() {
        $this->transLevel--;
 
        if($this->transLevel == 0 || !$this->nestable()) {
            parent::commit();
        } else {
            $this->exec("COMMIT TRANSACTION LEVEL{$this->transLevel}");
        }
    }
 
    public function rollBack() {
        $this->transLevel--;
 
        if($this->transLevel == 0 || !$this->nestable()) {
            parent::rollBack();
        } else {
            $this->exec("ROLLBACK TRANSACTION LEVEL{$this->transLevel}");
        }
    }
 
    /**
     * Returns last inserted ID value.
     * SQLSRV driver supports PDO::lastInsertId() with one peculiarity: when $sequence's value is null or empty
     * string it returns empty string. But when parameter is not specified at all it's working as expected
     * and returns actual last inserted ID (like other PDO drivers).
     *
     * @param string|null $sequence the sequence name. Defaults to null.
     * @return integer last inserted ID value.
     */
    public function lastInsertId($sequence=null)
    {
            if(!$sequence)
                    return parent::lastInsertId();
            return parent::lastInsertId($sequence);
    }
}

Then in framework\db\CDbConnection.php change the code in function "createPdoInstance()" (near line 421):

elseif($driver==='sqlsrv')
    $pdoClass='CMssqlSqlsrvPdoAdapter';

For this:

elseif($driver==='sqlsrv')
{
    $pdoClass=$this->pdoClass;
}
#15890 report it
Segmentation_Fault at 2013/12/26 11:16pm
Thank you!

Thanks for sharing, it worked as expected, and saved me a lot of work!

#12907 report it
Andres Felipe Diaz at 2013/04/19 03:43pm
Works like a Charm

Thank you for putting this up.

I had a three level transaction and it worked great. No additional work required. Totally recommend this.

#12655 report it
Haensel at 2013/04/04 05:39am
Thanks for sharing

That's exactly what I was searching for. Great job!

#8389 report it
Kevin Higgins at 2012/05/30 03:35pm
Very cool

I might end up using this. Any idea why an option to enable this isn't in Yii yet?

#5264 report it
SteveK 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 :)

Leave a comment

Please to leave your comment.

Write new article