List:General Discussion« Previous MessageNext Message »
From:Fred Read Date:May 5 1999 9:23am
Subject:Re: MySQL and web-concurrency?
View as plain text  
Eric Handbury wrote:
> 
>   I am implementing a web-server with the SQL-engine 
> being MySQL. Since all web transactions are state-less, 
> how do developers resolve the concurrency issue where 
> two people are trying to update the same record? Since 
> each of the two people have their own copies of the current 
> data, won't one of them clobber the others data?

>   I can't see how locking the table would help, since 
> there would be a number of problems with locking like 
> a) a person locking a table then going to lunch effectively
>    shutting-out everyone else, 
> b) a person locking the table then losing net connection, 
> c) lots of other ways...

This is a classic CS problem usually referred to as the 
"Airline Booking Problem" and the solution is almost 
trivial!

If the users are able to change columns X, Y and Z then 
safe updates are made by using an update of the following 
form;

    Update <table>
    Set 
        X = <New X Value>,
        Y = <New Y Value>,
        Z = <New Z Value>,
    where
        X = <Old X Value>,
        Y = <Old Y Value>,
        Z = <Old Z Value>;

and check the number of rows affected by the update. 
If 1 the update was successful. 
If 0 the update failed usually because someone else 
had already updated the record -> X, Y and Z didn't 
have the same values as when the user first loaded 
the row from the database.

Obviously, you have to inform them of this fact, and 
offer them the opportunity to reload the data and do 
the edit over...

If you want me to explain it in more detail drop me a 
line...

Cheers!
-- 

If it ain't opinionated, it ain't Rich Teer.
Thread
MySQL and web-concurrency?Eric Handbury5 May
  • Re: MySQL and web-concurrency?Frederik Lindberg5 May
  • Re: MySQL and web-concurrency?Fred Read5 May