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.
