Is there any way to make this database lock more efficient?

Question

I've written this code to ensure that two users can't edit the same row of the same table at the same time. The only problem that I have with it, is that it connects to the database 3 times, once to add a new lock, once to check that it's the only lock for that row and once to either delete the lock or to retrieve the data for the user to edit. I really don't like this, but it was the only way that I could imagine doing it.

Are there any ways of making this more efficient?

<?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

It looks like you want a scheme for advisory persistent locking. That is, you want to keep the locks in place for a relatively long time -- longer than a DBMS transaction could reasonably expect to do from a web app. I call it "advisory" because it's not "mandatory" in the sense that the DBMS enforces it.

You are very close. I suggest you define your lock table with a compound primary key (TableID, RowID). That way, attempts to insert duplicate records into that table will fail. Forget about the LockID. You don't need it. UserID is helpful because it will give you a hint at diagnosing trouble.

Then, to set up a lock (in your example on table 1, row $CountryID) you will do your insert like so:

$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;
}

This is nice because you'll handle the duplicate-key error (integrity constraint violation) by setting $locked to false. When $locked is false you know somebody else has the lock and you can't have it. It's also nice because it's race-condition-proof. If two users are racing to take the same lock, one of them definitively wins and the other definitively loses.

When you want to release the lock, do the delete in a similar fashion.

$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;
}

Here, the variable $it_was_locked lets you know whether the lock was already in place. In any case after you run this command the lock will be cleared.

One more thing. For the integrity of your system, please consider defining a lock timeout. Perhaps it should be ten seconds, and perhaps it should be ten hours: that's up to your application's user-experience needs. The timeout will keep your application from getting all jammed up if people start but don't complete transactions.

Then, put a locktime column into your lock table and automatically put the current time into it. You'd do this with a line like this in your table definition.

locktime TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Then, whenever you insert a lock, first release all locks that have timed out, like so.

$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;
}

This kind of thing makes for a serviceable locking scheme. When you're doing system test, you can expect to look at this lock table a lot, trying to figure out what module forget to take a lock and what module forgot to release it.