Home > Coding > PHP, PDO & Nested Transactions

PHP, PDO & Nested Transactions

December 2nd, 2008

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 , , , ,

  1. chris
    | #1

    Awesome code. Thanks for posing this.

  2. | #2

    Good stuff – you should perhaps mention that the RELEASE SAVEPOINT statement is only available in MySQ 5.

  3. | #3

    Cheers for pointing that out. It should really version check the DB being used and check the SQL it wants to run is going to work, etc… Just hoping that code gets the basic method/idea across. :)

  4. jj
    | #4

    naize :)

  5. Kyosuke
    | #5

    i think that there is a mistake with the managing of $this->transLevel

    in the metods commit and rollback, it could be at the end of the function NOT in front.

    Nice script indeed 😉

  6. Kyosuke
    | #6

    so :

    public function rollBack() {

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

    $this->transLevel–;
    }

    and :

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

  7. | #7

    @Kyosuke

    I disagree.

    Start with the case where transLevel = 0 (e.g. no calls to the transaction functions.)

    beginTransaction() increments transLevel to 1.

    Calling commit() or rollBack() with your code will try to ROLLBACK or RELEASE the save points where none have been created yet.

    However with transLevel—; at the start of the function it will decrement it first then correctly try to call the parent::commit() or parent::rollBack() functions as appropriate.

  8. | #8

    Very nice tip.
    However, beware that some operations (create table…) will call an implicit commit and thus will break the translevel counter.
    But still, this is very useful.

  9. | #9

    Great tip, works like a charm!

  10. Luciano
    | #10

    Perfect!

  11. | #11

    Excellent stuff 😉 works like a charm, and its beautifully made.
    Thumbs up mate

  12. | #12

    I’ve taken this code, added the ability to use a closure as a transaction and released it on github.

    Check it out at: https://github.com/wakeless/transaction_pdo

  1. No trackbacks yet.