I’ve been using PDO as my database library and it works reasonably well (as long as you remember it’s not a full blown database abstraction library), however recently I needed to use nested transactions to ensure that the database remains consistent while doing a series of SQL statements.

Unfortunately PDO does not support nested transactions although PostgreSQL and MySQL do. I decided to extend the PDO class to support nested transactions while also using PDO to keep track of the first transaction. I came up with the following class (released under the GNU General Public License, Version 3):-

class MyPDO 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->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}");
        }
    }
}

This code will only attempt to use the SAVEPOINT code if you’re using a database driver that supports it (it should probably version check the database server) this then means that in your code you can do things like:-

$pdo = new MyPDO(DB_DSN, DB_USER, DB_PASS);
$pdo->beginTransaction();
try {
    $pdo->exec(...);
    $pdo->exec(...);

    $pdo->beginTransaction();
    try {
        $pdo->exec(...);
        $pdo->exec(...);
        $pdo->exec(...);
        $pdo->commit();
    } catch(PDOException $e) {
        // If this statement fails, rollback...
        // NOTE: This will only rollback statements made in the
        //       inner try { block and not the outer one.
        $pdo->rollBack();
    }

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
}

NB: I’ve tweaked the code slightly when transferring it to my blog and I haven’t tested it, so there could be some minor errors – please leave comments if you spot any. Thanks!

Coding mysql, pdo, php, postgresql, transactions