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
    March 20th, 2009 at 22:44 | #1

    Awesome code. Thanks for posing this.

  2. April 7th, 2009 at 23:33 | #2

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

  3. April 17th, 2009 at 10:57 | #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
    April 27th, 2009 at 13:10 | #4

    naize :)

  5. Kyosuke
    May 29th, 2009 at 09:29 | #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
    May 29th, 2009 at 09:36 | #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. August 27th, 2009 at 20:49 | #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. November 3rd, 2009 at 14:26 | #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. December 13th, 2010 at 22:27 | #9

    Great tip, works like a charm!

  10. Luciano
    December 3rd, 2011 at 21:35 | #10

    Perfect!

  11. February 16th, 2012 at 22:26 | #11

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

  12. July 26th, 2012 at 05:15 | #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.

Current day month ye@r *