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

unchanged
Title
How to use nested DB transactions (MySql 5+, PgSql)
unchanged
Category
Tutorials
changed
Tags
db, mysql, postgresql
changed
Content

source code taken from: [PHP, PDO & Nested
Transactions](http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/)

tested with: MySql 5.1.30

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    class ServiceA {
    public
        public function transA() {
        $trans
            $trans = Yii::app()->db->beginTransaction();
        //
            // code
        Yii::app()->serviceB->transB();
        //
            Yii::app()->serviceB->transB();
            // code
        //
            // exception handling and stuff
        $trans->commit();
            $trans->commit();
        }
    }
}

class    
    class ServiceB {
    public
        public function transB() {
        $trans
            $trans = Yii::app()->db->beginTransaction();
        //
            // code
        //
            // exception handling and stuff
        $trans->commit();
            $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:

~~~
[php]
class	class InsPDO extends PDO {
    //
	    // Database drivers that support SAVEPOINTs.
    protected
	    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->transLevel == 0) {
	            parent::beginTransaction();
	        } else {
	            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
	        }
	
	        $this->transLevel++;
	    }
	
	    public function commit() {
	        $this->transLevel--;
	
	        if(!$this->nestable() || $this->transLevel == 0) {
	            parent::commit();
	        } else {
	            $this->exec("RELEASE SAVEPOINT
LEVEL{$this->transLevel}");
	        }
	    }
	
	    public function rollBack() {
	        $this->transLevel--;
	
	        if(!$this->nestable() || $this->transLevel == 0) {
	            parent::rollBack();
	        } else {
	            $this->exec("ROLLBACK TO SAVEPOINT
LEVEL{$this->transLevel}");
	        }
	    }
	}

    // The current transaction level.
    protected $transLevel = 0;Then, you'll need to alter
CDbConnection::createPdoInstance. There, change $pdoClass to your class name
(InsPDO in this example).

    protected	protected function nestable() {
        return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
                        self::$savepointTransactions);
    }createPdoInstance()
	{
		$pdoClass='InsPDO';
		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);
	}

    public function beginTransaction() {
        if(!$this->nestable() || $this->transLevel == 0) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }It'd probably be better OOP to extend CDbConnection,
especially since createPdoInstance is protected. But then either there are
inconsistencies with private/protected properties of CDbConnection, or I didn't
spent enough time to explore how to properly extend CDbConnection.

        $this->transLevel++;
    }That's it, there you go;)

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

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

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

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

Then, you'll need to alter [CDbConnection::createPdoInstance()]. There, change
$pdoClass to your class name (InsPDO in this example).

~~~
[php]
protected function createPdoInstance()
{
    $pdoClass='InsPDO';
    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);
}
~~~

It'd probably be better OOP to extend [CDbConnection], especially since
createPdoInstance is protected. But then either there are inconsistencies with
private/protected properties of [CDbConnection], or I didn't spent enough time
to explore how to properly extend [CDbConnection].

That's it, there you go;)