PHP，mysqli和表锁? [英] PHP, mysqli, and table locks?

问题描述

我有一个数据库表，需要在其中拉出一行，测试用户输入是否匹配，然后更新该行以标识进行匹配的用户.如果出现竞争状况，我需要确保第一位用户的更新不会被另一位用户覆盖.

I have a database table where I need to pull a row, test user input for a match, then update the row to identify the user that made the match. Should a race condition occur, I need to ensure that the first user's update is not overwritten by another user.

为此，我打算:
1.读取行
2.锁表
3.再次读取行并与原始行进行比较
4.如果行与更新匹配，否则不执行任何操作(另一位用户已经更新了该行)

To accomplish this I intend to:
1. Read row
2. Lock table
3. Read row again and compare to original row
4. If rows match update, otherwise do nothing (another user has already updated the row)

根据我在Google上找到的信息，我希望锁表语句能够阻塞，直到获得锁为止.我在PHP中设置了一个小测试脚本，该脚本会停顿10秒钟，让我有时间手动创建竞态条件.

Based on information I found on Google, I expected the lock table statement to block until a lock was aquired. I set up a little test script in PHP that would stall for 10 seconds to allow me time to manually create a race condition.

 // attempt to increment the victor number
$aData["round_id"] = $DATABASE["round_id"];

// routine to execute a SELECT on database (ommited for brevity)
$aRound = $oRound->getInfo($aData);  

echo "Initial Round Data:";
print_r($aRound);

echo "Locking...";
echo $oRound->lock();

echo "Stalling to allow for conflict...";
sleep(10);
echo "Awake...";

$aLockedRound = $oRound->getInfo($aData);
if($aRound["victor_nation"] == $aLockedRound["victor_nation"]){
    $aData["victor_nation"] = $aRound["victor_nation"] + 1;
    $oRound->update($aData);
    echo "Incremented Victor Nation";
}

其中锁定例程定义为

function lock(){
        global $oDatabase;
        $iReturn = 0;

        // lock the table
        $iReturn = $oDatabase->m_oConnection->query("LOCK TABLES round WRITE");     
        return $iReturn;
    }

上面，$oDatabase->m_oConnection是一个mysqli连接，我用来在数据库上执行准备好的语句.

Above, $oDatabase->m_oConnection is a mysqli connection that I use to execute prepared statements on the database.

当我运行测试脚本时，我首先启动了第一个用户，然后等待说出允许发生冲突的步骤..."，然后启动第二个脚本.在第二个脚本中，我希望它在锁定..."处被阻止，但是，第二个脚本也继续为总计以允许发生冲突...".

When I run my test script I kick off the first user and wait for "Stalling to allow for conflict..." , then start a second script. On the second script I expected it to block at "Locking...", however, the second script also continues to "Stalling to allow for conflict...".

由于LOCK语句似乎没有被阻塞，也未返回获取锁的任何指示符(返回值已回显且为空白)，因此我不清楚我实际上是在获取锁.即使我不知道如何继续.

Since the LOCK statment doesn't appear to be blocking, nor returning any indicator of acquiring the lock (return value is echoed and blank), it's unclear to me that I'm actually acquiring a lock. Even if I am, I'm not sure how to proceed.

有指针吗?

推荐答案

故障排除:您可以通过尝试使用另一个未锁定的表来测试表锁定是否成功.如果获得了锁，则尝试写入未包含在锁语句中的表应该会产生错误.

Troubleshooting: You can test for table lock success by trying to work with another table that is not locked. If you obtained the lock, trying to write to a table that was not included in the lock statement should generate an error.

您可能要考虑替代解决方案.而不是锁定，而是执行更新，其中将更改后的元素作为where子句的一部分.如果自您读取以来更改的数据已更改，则更新将失败"并返回修改后的零行.这样就消除了表锁以及所有可能带来的麻烦，包括死锁.

You may want to consider an alternative solution. Instead of locking, perform an update that includes the changed elements as part of the where clause. If the data that you are changing has changed since you read it, the update will "fail" and return zero rows modified. This eliminates the table lock, and all the messy horrors that may come with it, including deadlocks.

这篇关于PHP，mysqli和表锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！