Code Samples – Preventing simultaneous web updates 3

Below are some interesting problems and solutions that I have come accross.

Problem: In a web based system how to you prevent simultaneous web updates (aka overwrites) to the same record by different users seeing potentially different data. This is a play on the old airline reservation problem you find in older computer science text books. If  there is one seat left, and two reservation agents pull it up on their screen. How do you prevent them from duplicate booking (OF course in the real-world airlines frequently overbook  .. but that’s a business problem, not a technical one).

My Solution: This was based on a real-world example for my current project. The first obvious solution would be to lock the database record when displaying the information on the page. This is not practical  in a web based interface because of the nature of HTTP (being connection-less) , the lock would never really end, it could timeout but that may be a while, and would prove impractical in a busy application. Traditional desktop apps, typically use a callback mechanism to alert the screen that the record changed and the data was no longer valid.  I had no way to inform a stale page view that something was updated (although an ajax option could work here). In light of these issues my solution was to.

1. Read the record and create a  hash of the record, store it in a session variable
2. When the user goes to post the record (changes), I again re-read the record (data) from the database and calculate a hash. I check to see if the hashes match. If they match I assume the record DB record wasn’t changed. Then I proceed to write the changes.
3. If the hash is different, It means the record was changed since the user last say the data in which case , I discard (or save the changes to a draft record) and inform the user.

Code Sample
1. First Step when I read the record and display the page.. I simply take an MD5 hash of the data that may change.

$current_rs= execSQL("SELECT * FROM Reservation WHERE flight_id=$fl_id");

//Hash currently read record
$_SESSION['rs_item_hash']=MD5( serialize($current_rs->fields) );

Second Step: When I go to update the record, I again quickly re-read the data and re-hash it , if is the same as the first hash, its safe to update, if not then rollback and inform the users..

<?php
//Record Lock Check:

$id=$_REQUEST['item_id'] ;

$current_rs= execSQL("SELECT * FROM Reservation WHERE flight_id=$fl_id");
$current_hash=MD5( serialize($current_rs->fields) ); //magic happens here

if ($current_hash !=$_SESSION['rs_item_hash']) //check to see if its changed
{
logEvent($_SESSION['user'],"EDIT CONFLICT: The Database RECORD YOU WERE updating HAS BEEN CHANGED SINCE you were in EDIT MODE. This UPDATE is CANCELLED. TRY AGAIN",null,true);
$nextaction="Add";
$action="start"; //default action
break; //rollback don't update anything
}

?>