php - 有没有办法让这个数据库锁更有效率？

Question

我编写这段代码是为了确保两个用户不能同时编辑同一个表的同一行。我遇到的唯一问题是它连接到数据库 3 次，一次是添加一个新锁，一次是检查它是否是该行的唯一锁，一次是删除锁或检索数据用户进行编辑。我真的不喜欢这样，但这是我能想到的唯一方法。

有什么方法可以提高效率吗？

<?php
$CountryID = $_GET["CountryID"];
$userID = $_SESSION["userID"];
$currentTime = date('Y-m-d H:i:s');
try{
    include_once 'PDO.php';
    //Adds a new lock into the database.
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $sth = $dbh->prepare('INSERT INTO lock (UserID, TableID, RowID) VALUES (?, ?, ?)');
    $sth->execute(array($userID,1,$CountryID));
    $insertedID = $dbh->lastInsertId();


    //Checks to see if there is more than one lock for this table/row. If there is more than 1 row, it will check to make sure that all OTHER rows are older than 5 minutes old incase of broken locks.
    $sth = $dbh->prepare('SELECT * from lock where TableID = ? AND RowID = ?');
    $sth->execute(array(1,$CountryID));
    $rowCount = $sth ->rowCount();
    $locked = false;
    if ($rowCount >1 ){
        foreach($sth as $row){
            if ($row['LockID'] != $insertedID AND (abs(strtotime($currentTime) - strtotime($row['Date']))) < 300){ 
                $locked = true;
                break;
            }
        }
    }
    if ($locked){
        //Delete the lock we put in first, and tell the user that someone is already editing that field. 
        $sth = $dbh->prepare('DELETE from lock where LockID = ?');
        $sth->execute(array($insertedID));
        echo "Row is currently being edited.";
    }else{
        //Don't delete the lock, and get data from the country table.
        echo "Row isn't being edited.";
        $sth = $dbh->prepare('SELECT * from country where CountryID = ?');
        $sth->execute(array($CountryID));
    }
}catch (PDOException $e){
    echo "Something went wrong: " . $e->getMessage();
}
$dbh = null;
?>

Answer 1

您似乎想要一个建议性持久锁定方案。也就是说，您希望将锁保持相对较长的时间——比 DBMS 事务合理期望从 Web 应用程序执行的时间更长。我称它为“建议”，因为它不是 DBMS 强制执行的“强制性”。

你们很亲近。我建议您使用复合主键 (TableID, RowID) 定义您的 lock 表。这样，向该表中插入重复记录的尝试就会失败。忘记 LockID。你不需要它。 UserID 很有用，因为它会给您诊断问题的提示。

然后，要设置一个锁(在表 1 的示例中，第 $CountryID 行)，您将像这样进行插入:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); /* only once*/

$sth = $dbh->prepare('INSERT INTO lock (UserID, TableID, RowID) VALUES (?, ?, ?)');
$locked = true;
try {
    $sth->execute(array($userID,1,$CountryID));
}
catch (PDOException $e) { 
    $locked = false;
}

这很好，因为您将通过将 $locked 设置为 false 来处理重复键错误(违反完整性约束)。当 $locked 为 false 时，您知道其他人拥有锁而您不能拥有它。这也很好，因为它是防竞争条件的。如果两个用户竞相争夺同一把锁，则其中一个最终获胜，另一个最终失败。

当你想释放锁时，以类似的方式进行删除。

$sth = $dbh->prepare('DELETE FROM lock WHERE TableID = ? AND RowID = ?');
$it_was_locked = false;
$sth->execute(array(1,$CountryID));
if ($sth->rowCount() > 0) {
    $it_was_locked = true;
}

在这里，变量 $it_was_locked 让您知道锁是否已经就位。在任何情况下，在您运行此命令后，锁定都会被清除。

还有一件事。为了系统的完整性，请考虑定义锁定超时。也许应该是十秒，也许应该是十个小时:这取决于您的应用程序的用户体验需求。如果人们开始但没有完成事务，超时将使您的应用程序不会完全卡住。

然后，将 locktime 列放入您的 lock 表中，并自动将当前时间放入其中。您可以在表定义中使用类似这样的一行来执行此操作。

locktime TIMESTAMP DEFAULT CURRENT_TIMESTAMP

然后，每当插入锁时，首先释放所有超时的锁，就像这样。

$stclean = $dbh->prepare('DELETE FROM lock WHERE locktime < NOW() - 10 SECOND');
$sth = $dbh->prepare('INSERT INTO lock (UserID, TableID, RowID) VALUES (?, ?, ?)');
$locked = true;
$stclean->execute();
if ($stclean->rowCount() > 0) {
     /* some timed-out locks were released; make an error log entry or whatever */
}
try {
    $sth->execute(array($userID,1,$CountryID));
}
catch (PDOException $e) { 
    $locked = false;
}

这种东西构成了一个可用的锁定方案。当你在做系统测试时，你可能会经常查看这个锁表，试图找出哪个模块忘记拿锁，哪个模块忘记释放锁。