Below are some interesting problems and solutions that I have come accross.
Problem: In a web based system how to you prevent multiple updates 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 duplicate book .. but that’s a business problem)
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 updating PO, but in a web based interface this poses problem because of the nature of HTTP (being connection-less) , the lock would never really end, it could timeout but that may be a while. I had no way to inform a stale page view that something was updated (athough an ajax option could work here). So my solution was to.
- Read the record and create a hash of the read in record, store it in a session variable
- 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.
- 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
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 uers
//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
}