PHP，MySQL，PDO事务 - 在调用commit（）之后可以使用rollBack（）吗？

Question

I've looked through resources for rollBack(), commit() and various transaction stuff, but I cannot find whether rollBack() can be called after commit() has already been called.

The situation is this:

I have two different databases: $dbu = new PDO(..db1..) and $dbb = new PDO(..db2..)

Both databases have tables that are being updated within a single function. The operation is all or none - either all tables are successfully updated, or none are.

Using two separate transactions, if the transaction for $dbu is successfully completed, but the transaction for $dbb fails, I have to undo what was done in the first transaction:

Code Block 1

$dbu->beginTransaction();
try{
    $stmt = $dbu->prepare(...);
    $stmt->execute();

    // stuff

    $dbu->commit();
}catch(Exception $e){
    // do stuff

    $dbu->rollBack();
    exit();
}

$dbb->beginTransaction();
try{
    $stmt = $dbb->prepare(...);
    $stmt->execute();

    // stuff

    $dbb->commit();
}catch(Exception $e){
    // do stuff

    $dbb->rollBack();

    // Need to undo what we did
    $dbu->beginTransaction();
    try{
        $stmt = $dbu->prepare(...);
        $stmt->execute();

        // opposite of whatever operation was in the first transaction

        $dbu->commit();
    }catch(Exception $e){
    }

    exit();
}

This is messy, and unreliable if something happens to the connection in between the two primary transactions.

So what I'd like to do instead is nest the second transaction within the first. It seems logical that I'd be able to do this, because $dbu and $dbb are two unique PDO objects, that point to two separate databases. It looks like:

Code Block 2

$dbu->beginTransaction();
try{
    $stmt = $dbu->prepare(...);
    $stmt->execute();

    // stuff

    $dbb->beginTransaction();
    try{
        $stmt = $dbb->prepare(...);
        $stmt->execute();

        // stuff

        $dbb->commit();
    }catch(Exception $e){
        // do stuff

        $dbb->rollBack();
        $dbu->rollBack(); // Since $dbu was first part of transaction, it needs to be rolled back too
        exit();
    }

    $dbu->commit();
}catch(Exception $e){
    // do stuff

    $dbu->rollBack();
    $dbb->rollBack(); // **THIS IS THE TRICKY LINE!**
    exit();
}

Since commit() for $dbu is called after the entire $dbb transaction, the case may arise where $dbb was successful, and $dbu failed. If that happens, I need to undo what was done in the $dbb transaction.

So...

Can I call $dbb->rollBack(); (near the end of Code Block 2) AFTER $dbb->commit(); has run? Or am I stuck in the same situation as I initially was, where I have to manually reverse whatever happened in the $dbb transaction? Again, this isn't ideal. If the connection drops in the middle of this, I could be left with data in the $dbb tables that shouldn't be there (because the $dbu transaction failed).

Perhaps I can combine the two transactions into a single try/catch block?

Code Block 3

$dbu->beginTransaction();
$dbb->beginTransaction();

try{
    $stmt = $dbu->prepare(...);
    $stmt->execute();

    $stmt2 = $dbb->prepare(...);
    $stmt2->execute();

    // stuff

    $dbu->commit();
    $dbb->commit();
}catch(Exception $e){
    // do stuff

    $dbu->rollBack();
    $dbb->rollBack(); // **THIS IS THE TRICKY LINE!**
    exit();
}

But this doesn't look a whole lot different than Code Block 2, because we can still have the situation where $dbu->commit(); is successful, but $dbb->commit(); fails. If that happens, then we are still trying to call $dbu->rollBack(); after its partner commit has already been processed.

If I cannot call rollBack() after commit(), is there a commonly used method to tackle this 2-DB problem? Something that is as efficient as rollBack() and doesn't require an entire extra transaction to undo the former operation.

EDIT 1

Adding on to Code Block 3, could I verify each execution as they are called?

Code Block 4

$dbu->beginTransaction();
$dbb->beginTransaction();

try{
    $stmt = $dbu->prepare(...);
    if(!$stmt->execute()){
        throw new Exeption('something somethign');
    }

    $stmt2 = $dbb->prepare(...);
    if(!$stmt2->execute()){
        throw new Exeption('something two');
    }

    // stuff

    $dbu->commit();
    $dbb->commit();
}catch(PDOException $e){
    // do stuff

    $dbu->rollBack();
    $dbb->rollBack(); // **THIS IS THE TRICKY LINE!**
    exit();
}catch(Exception $e){
    // do stuff

    $dbu->rollBack();
    $dbb->rollBack(); // **THIS IS THE TRICKY LINE!**
    exit();
}

Will this help ensure the two commit statements have the best possible chance of succeeding? Or does the try/catch block automatically throw PDOException before the custom ones are ever called? It would be nice to have a simple identifier to know which transaction is failing, opposed to the entire $e->getMessage();.

Answer 1

You cannot roll back committed changes.

As with your other question code block 3 is the way to go. Even though a commit might fail it will not fail because of common errors (like wrong syntax or constraint violation or what else). Hypothetical the whole PHP process might be killed right in between both commits resetting the latter letting you with no chance to fix the resulting errors in-code. However you will have to take care of those rare exceptions separately (e.g. backups) because I don't see an efficient way to handle them in-code.

Also remember that when committing the changes have already been applied but not been "published". So the commit itself is rarely to fail (only for exceptional reasons).

@EDIT 1

The way you handle errors depends on how you set up your PDO instances. See the documentation on how errors can be handled by PDO.

Your code block 4 will work if you use the default mode (not setting the error mode explicitely or setting it to PDO::ERRMODE_SILENT).

Answer 2

It's impossible to have a proper transaction across different database connections.

Although you can do some awkward workarounds, it won't have your transaction a real one.

So, you have to either keep all the operations within a single database or forget about transactions