PHP，MySQL，PDO事务-在调用commit()之后可以使用rollBack()吗? [英] PHP, MySQL, PDO Transaction - Can rollBack() be used after commit() has been called?

问题描述

我已经浏览了 rollBack() ， commit() 和解决方案

您无法回滚已提交的更改.

与您的其他问题一样代码块3是要走的路.即使提交可能失败，也不会因为常见错误(例如错误的语法或违反约束的条件或其他原因)而失败.假想整个PHP进程可能在两次提交之间就被杀死，从而重置了后者，使您没有机会修复代码中的错误.但是，您将不得不分别处理那些罕见的异常(例如备份)，因为我看不到一种有效的方式来以代码方式处理它们.

还请记住，在提交更改时，更改已经应用，但尚未发布".因此，提交本身很少会失败(仅出于特殊原因).

@EDIT 1

处理错误的方式取决于设置PDO实例的方式.有关PDO如何处理错误的信息，请参见文档.. >

如果您使用默认模式(未明确设置错误模式或将其设置为PDO::ERRMODE_SILENT)，您的代码块4将起作用.

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();.

解决方案

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).

这篇关于PHP，MySQL，PDO事务-在调用commit()之后可以使用rollBack()吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！