PHP/MySQL关键部分 [英] PHP/MySQL Critical section

问题描述

我正在将PHP与PDO和InnoDB表一起使用.

I'm using PHP with PDO and InnoDB tables.

我只希望代码允许一个用户提交的操作完成，用户可以取消或完成.但是，如果用户同时执行这两项操作，则我希望其中一个请求失败并回滚，而这目前并没有发生，这两个请求都已完成而没有异常/错误.我认为在检查行后删除该行就足够了.

I only want the code to allow one user-submitted operation to complete, the user can either cancel or complete. But in the case that the user posts both operations, I want one of the requests to fail and rollback, which isn't happening right now, both are completing without exception/error. I thought deleting the row after checking it exists would be enough.

$pdo = new PDO();

try {

    $pdo->beginTransaction();

    $rowCheck = $pdo->query("SELECT * FROM table WHERE id=99")->rowCount();

    if ($rowCheck == 0)
        throw new RuntimeException("Row isn't there");

    $pdo->exec("DELETE FROM table WHERE id = 99");

    // either cancel, which does one bunch of queries. if (isset($_POST['cancel'])) ...
    // or complete, which does another bunch of queries. if (isset($_POST['complete'])) ...
    // do a bunch of queries on other tables here...

    $pdo->commit();

} catch (Exception $e) {

    $pdo->rollback();

    throw $e;

}

如何使取消/完成操作成为关键部分?第二项操作必须失败.

How can I make the cancel / complete operations a critical section? The second operation MUST fail.

推荐答案

该代码很好，但有一个例外:将 FOR UPDATE 添加到初始的 SELECT 中.这样就足以阻止第二个按钮被按下，直到第一个 DELETE 发生，从而导致第二个失败".

The code is fine, with one exception: Add FOR UPDATE to the initial SELECT. That should suffice to block the second button press until the first DELETE has happened, thereby leading to the second one "failing".

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

注意使用 SELECT FOR UPDATE 锁定行以进行更新仅适用当自动提交被禁用时(通过使用 START TRANSACTION 或通过将autocommit设置为0.如果autocommit为启用后，符合规范的行不会被锁定.

Note Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

这篇关于PHP/MySQL关键部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！