PHP、MySQL、PDO 事务 - fetchAll() 可以在 commit() 之前出现吗？

Question

更多交易问题!

我现在拥有的是一堆串在一起的查询，如果有任何失败，它们都会被手动撤销:

代码块 1

$stmt1 = $db->prepare(...); // Update table1, set col=col+1
if($stmt1 = $db->execute(...)){

    $stmt2 = $db->prepare(...); // Insert into table2, id=12345
    if($stmt2 = $db->execute(...)){

        $stmt3 = $db->prepare(...); // Select val from table3
        if($stmt3 = $db->execute(...)){

            $result = $stmt3->fetchAll();
            if($result[0]['val'] == something){

                $stmt4 = $db->prepare(...); // Update table4, set status=2
                if($stmt4 = $db->execute(...)){

                    return true;

                }else{
                    $stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
                    $stmt1 = $db->execute(...);

                    $stmt2 = $db->prepare(...); // Delete from table2, where id=12345 (opposite of above)
                    $stmt2 = $db->execute(...);

                    return false;
                }
            }

            return true;
        }else{
            $stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
            $stmt1 = $db->execute(...);

            $stmt2 = $db->prepare(...); // Delete from table2, where id=12345 (opposite of above)
            $stmt2 = $db->execute(...);

            return false;
        }
    }else{
        $stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
        $stmt1 = $db->execute(...);

        return false;
    }
}

一团糟，难以调试，难以添加，查询量大时难以理解，如果连接中途丢失，也不会将所有表恢复到原始状态。

同样的过程在删除行时更糟糕，因为其中的所有内容都需要存储 - 以防需要撤消操作。

现在，我知道大部分在我将它移植到单个事务时仍然有效，但我不确定的部分是:

代码块 2

$result = $stmt3->fetchAll();
if($result[0]['val'] == something){
    ... continue ...
}else{
    ... reverse operations ...
    return false;
}

因为结果收集将在事务中的 commit() 之前进行。如下:

代码块 3

$db->beginTransaction();    

try{
    $stmt1 = $db->prepare(...);
    $stmt1->execute();

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

    $stmt3 = $db->prepare(...);
    $stmt3->execute();

    $result = $stmt3->fetchAll();
    if($result[0]['val'] == something){
        $stmt4 = $db->prepare(...);
        $stmt4->execute();
    }else{
        $db->rollBack();
        return false;
    }

    $db->commit();

    return true;
}catch(Exception $e){
    $db->rollBack();
    throw $e;
    return false;
}

这行得通吗？具体来说，我能否在 commit() 之前包含 $result = $stmt3->fetchAll();，然后执行条件查询？

此外，我对此并不完全确定，但如果代码是在 commit() 之前退出(return false)？

Answer 1

你的第一个问题:

Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?

我看不出它为什么不起作用。事务的行为与没有事务的操作基本相同 - 除了更改只是草稿。您在前面的声明中所做的任何更改都将应用于仅对该单个 session 有效的“工作副本”。对你来说，它会显得完全透明。但是，如果您不提交任何更改，它们将被回滚。

还值得注意(强调我的):

In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

这可能会导致竞争条件。

你的第二个问题:

Also, I'm not entirely sure on this, but do I require the $db->rollBack(); within the try block, if the code is exited (return false) before the commit()?

来自documentation它说:

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.

因此您不一定需要手动回滚，因为它会由驱动程序本身完成。

但是请注意来自同一来源的以下内容:

Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.

所以一定要事先检查兼容性!

一些注意事项

不要在另一个事务中开始一个事务。这将隐含地提交第一个事务。参见 this comment .

来自 documentation 的另一条注释:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.