Multi-User Environment

jkrische

Howdy All!

I need your advice. I work for a web hosting company that for one project is using MySQL 3.x as the back end. This DB is interfaced via a custom UI I built in PHP 4.2. The problem I face is the old transaction control problem. For future projects, we'll probably go with PG instead of My, precisely for this reason.

This DB gains records from both general public input and our own staff, but all existing record updates are by our staff only. Now, suppose employee X and employee Y happen to be looking at the same customer record at the same time in the PHP GUI I built. In Filemaker I never had to worry about the two users overwriting each other's changes to that record (since Filemaker has record-level locking), but here I do.

By what methods, if any, can someone in my position take steps to ensure that no record has two people editing it at the same time or failing that, ensure that only the first person's changes are saved and all others receive a "update failed - simultaneous users" message?

One thought I had was a websession field in the affected tables, which gets set on page load time (for the record details display page), and queried-against for a match at record update time. If match, allow update, else show error. This would mean that only the last simultaneous user's changes would get saved, but it is at least SOME control... But perhaps there's a better way?

Your thoughts appreciated...

Sxooter

Simple, everytime you write a record, put a time stamp into it. When you read it, read the time stamp and hide it in a hidden input type in your form. When the user submits the data from the form for update, submit it like so:

So, you grab the record

select field1, field2, stamp from table where id='$id'

build a form, and put the stamp value into a hidden field. Then you generate a new timestamp when you get the form back, and update the table

$newstamp = microtime();
$query = "update table test set field1='$val1', field2='$val2', stamp='$newstamp' where stamp='$stamp' and id='$id'"

If the update fails, then tell the user that someone must have changed the data, otherwise it should take.

jkrische

Thanks Sxooter...

was starting to head that direction in the meantime, but thanks much for the confirmation on the idea & fleshing out! There's already a timestamp field in the affected tables, so half my job is done.

MrAlaska

To be even more careful, you write a pessimistic SQL statement that checks ALL the imported fields against the existing data in you WHERE statement, not just the timestamp.

EDIT: Although, I can think of no real-time scenario where the timestamp method (using microtime()) might fail, so perhaps my suggestion might be wasting bandwidth. I just feel better explicitly checking the data to be changed, I guess.