带有嵌套 PHP 检查的 PHP PDO 事务 [英] PHP PDO Transaction with nested PHP check

问题描述

我必须做 2 个 MySql 查询:

i have to do 2 MySql-queries:

1. SELECT id FROM X WHERE [...]

1. SELECT id FROM X WHERE [...]

插入 [...]

仅当第一个查询返回正确的 id 时才应执行第二个查询.

The second query should only be executed, if the first query returns an correct id.

是否可以在两个查询之间混合 PHP 条件?

Is it possible, to mix PHP conditions between both queries?

例如

try
{   
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();   

    $stmt =  $dbh->prepare("SELECT id FROM [...]");

    $stmt->bindParam(1, [...]);

    if($stmt->execute())
    {
        if($row = $stmt->fetch())
        {
            $matchID = $row['id'];
            $checkD = $this->checkId($matchID);

            if($checkD)
            {
                return '-1';
            }
            else
            {       

                $stmt = $dbh->prepare("INSERT INTO [...]");

                $stmt->bindParam(1,[...]);
                $stmt->execute();

                            stmt = $dbh->prepare("DELETE [...]");

                $stmt->bindParam(1,[...]);
                $stmt->execute();

                $dbh->commit();

                return $matchID;                            
            }
        }
        else
        {
            return '-1';
        }
    }
    else
    {
        return '-1';
    }       
} catch(Exception $e)
{
    $dbh->rollBack();
    return '-1';
}

这是正确的吗?(我得到零错误)如果没有:我怎么能意识到它?

Is this correct? (i get zero errors) If not: how can i realize it?

我想确定，当另一个用户执行 1. 查询时，没有其他用户可以访问 INSERT 查询.

I want to be sure, that no other user could reach the INSERT query, when annother is performing the 1. query.

推荐答案

事务与当前数据隔离.它们的确切行为取决于它们使用的隔离级别.例如，一个具有可序列化隔离级别的事务完全存在于过去，并且它不知道自事务开始以来所做的数据更改.

Transactions are isolated from the current data. How they behave exactly is dependent on the isolation level they use. For example a transaction with serializable isolation level completely lives in the past, and it knows nothing of the data changes have been made since the beginning of the transaction.

如果你想在你的脚本正在处理某些事情时阻止任何人对数据库进行更改，那么你必须锁定您的数据库、表格或行.这通常不是必需的，使用正确的代码.

If you want to prevent anybody to do changes on the database while your script is working on something, then you have to lock your database, tables or rows. This is usually not necessary, with the proper code.

就你而言

• 你可以使用读提交事务隔离级别(默认)调用SELECT之后的DELETE，并检查是否有被DELETE影响的行code> 在 INSERT
之前
• 如果您不想更改查询的顺序，那么您可以
  • 如果 DELETE 不影响任何行，则简单地抛出异常，因此 INSERT 将被回滚
  • 添加一个约束，防止多个INSERT使用相同的数据，所以重复的INSERT会违反约束，并且会被回滚
  • 使用 SELECT FOR 锁定行更新
  • you can use read committed transaction isolation level (default) call the DELETE after the SELECT, and check whether there are affected rows by the DELETE before the INSERT
  • if you don't want to change the order of your queries then you can
    • simply throw an exception if the DELETE does not affect any row, and so the INSERT will be rolled back
    • add a constraint which prevents multiple INSERTs with the same data, so the duplicated INSERT will violate a constraint, and it will be rolled back
    • lock the rows with a SELECT FOR UPDATE

    这篇关于带有嵌套 PHP 检查的 PHP PDO 事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！