How to nest DB transactions without actually nesting them.

To secure your database from inconsistencies resulting from failing (complex) operations, you (should) use transactions. However, complex methods often rely on other complex methods that themselves build on transactions. This HowTo presents a method to nest or embed transactions without relying on the database's nesting capabilities.

If you are looking to do real transaction nesting, look at How to use nested DB transactions (MySQL 5+, PostgreSQL) .

Here is the replacement for CActiveRecord which provides beginTransaction, rollback and commit as methods of CActiveRecord:

class YActiveRecord extends CActiveRecord {
    /**
     * Keeps transaction status (true if previous transaction, transaction if
     * local trasaction, null if no transaction).
     *
     * @var CDbTransaction bool null
     */
    private $_transaction=null;

    /**
     * Begin a transaction on the database.
     */
    public function beginTransaction() {
        if(YII_DEBUG) {
            Yii::trace("Begin transaction");
        }
        if($this->_transaction !== null) {
            throw new CException('Transaction already started');
        }
        $dc=$this->getDbConnection();
        $transaction=$dc->getCurrentTransaction();
        if($transaction) {
            $this->_transaction=true;
        } else {
            $this->_transaction=$dc->beginTransaction();
        }
    }

    /**
     * Commit a transaction on the database.
     *
     * @throws CException
     */
    public function commit() {
        if(YII_DEBUG) {
            Yii::trace("Commit transaction");
        }

        if($this->_transaction === null) {
            throw new CException('No ongoing transaction');
        } elseif($this->_transaction!==true){
            $this->_transaction->commit();
        }
        $this->_transaction=null;
    }

    /**
     * Rollback a transaction on the database.
     *
     * @throws CException
     */
    public function rollback() {
        if(YII_DEBUG) {
            Yii::trace("Rollback transaction");
        }
        if($this->_transaction === null) {
            throw new CException('No ongoing transaction');
        } elseif($this->_transaction!==true){
            $this->_transaction->rollback();
        }
        $this->_transaction=null;
    }
}

In the above code, '$result' indicates the success of the operation. You can use this to check if you should continue performing the operation or not in the complex operation - in the end it must indicate the overall success of the operation. As the callers rely on this result, the overall transaction is rolled back even if one of the subtransactions fails.

Each method has its (dis)advantages, so it is up to you to choose.

1 0
1 follower
Viewed: 5 733 times
Version: 1.1
Category: How-tos
Written by: le_top
Last updated by: le_top
Created on: Nov 22, 2014
Last updated: 3 years ago
Update Article

Revisions

View all history

Related Articles