本文介绍了PHP,MySQL,PDO事务-在调用commit()之后可以使用rollBack()吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我已经浏览了 rollBack() commit() 各种交易内容,但是我找不到在已经调用commit()之后是否可以调用rollBack()的情况.

情况是这样的:

我有两个不同的数据库:$dbu = new PDO(..db1..)和$dbb = new PDO(..db2..)

两个数据库都具有在单个函数中更新的表.操作为全部或全部-所有表都已成功更新,或者全部未更新.

使用两个单独的事务,如果$dbu的事务成功完成,但是$dbb的事务失败,则我必须撤消在第一个事务中所做的操作:

代码块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();
}

这很麻烦,而且如果两个主要交易之间的连接发生问题,也不可靠.

所以我想做的是将第二个事务嵌套在第一个事务中.我能够做到这一点似乎合乎逻辑,因为$dbu和$dbb是两个唯一的PDO对象,它们指向两个单独的数据库.看起来像:

代码块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();
}

由于在整个$dbb事务之后,在之后将的commit()称为,因此可能会出现$dbb成功而$dbu失败的情况.如果发生这种情况,我需要撤消$dbb事务中完成的操作.

所以...

我可以打电话给$dbb->rollBack();(在代码块2末尾附近)之后 $dbb->commit();已经运行了吗?还是我陷入了最初的困境,必须手动撤消$dbb事务中发生的一切?再次,这不是理想的.如果连接在其中途中断,则可能会在$dbb表中留下不应存在的数据(因为$dbu事务失败).

也许我可以将两个事务合并到一个try/catch块中?

代码块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();
}

但这看起来与代码块2 并没有太大不同,因为我们仍然可以遇到$dbu->commit();成功但$dbb->commit();失败的情况.如果发生这种情况,那么我们仍在尝试$dbu->rollBack();的伙伴提交之后调用它.

如果在commit()之后我不能调用rollBack(),是否有解决此2-DB问题的常用方法?效率与rollBack()一样高,并且不需要整个额外的事务即可撤消前一个操作.

编辑1

代码块3 的基础上,我能否验证每个执行的名称?

代码块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();
}

这是否有助于确保两个commit语句具有最大的成功机会?还是try/catch块会在调用自定义变量之前自动抛出PDOException?相对于整个$e->getMessage();,最好有一个简单的标识符来知道哪个事务失败了.

解决方案

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

与您的其他问题一样代码块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()吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 07:29
查看更多