preventing 2 simultaneous mysql update from a GUI

[Fred]

Hi out there,

I have problems finding a way to warn a user that
another user intends soon to update the same specific row.
Let me explain.

User 1 get to a JSP "update customer record" page.
- The page does a read of the existing record and
loads it into the gui fields for edit.

while he is doing is editing ... User 2 wants to update the same
"update customer record" page and attemp to load the same record in.
What mecanism can I use to stop him being able
to load the same record and warn him someone else is
updating that record.

It seems trivial but so far I don't know of a lock to stop
me reading a specific record. I must be missing something.

Thanks for your sugestion and specific sql examples.

Fred

[Richard Sanders]

In article <ef************ **************@ posting.google. com>,
fl*********@hot mail.com says...

Hi out there,

I have problems finding a way to warn a user that
another user intends soon to update the same specific row.
Let me explain.

User 1 get to a JSP "update customer record" page.
- The page does a read of the existing record and
loads it into the gui fields for edit.

while he is doing is editing ... User 2 wants to update the same
"update customer record" page and attemp to load the same record in.
What mecanism can I use to stop him being able
to load the same record and warn him someone else is
updating that record.

It seems trivial but so far I don't know of a lock to stop
me reading a specific record. I must be missing something.

Thanks for your sugestion and specific sql examples.

Fred

I don't know about a lock but you could have a flag field that is set to
signify that the row is in use, that is reset when the updated record is
re written. I know it is brute force and ignorance.

I would also like an elegant solution to this problem.

[2metre]

"Richard Sanders" <ri*****@starda te.ca> wrote in message
news:MPG.1b5e2e 417226da6998968 6@shawnews...

In article <ef************ **************@ posting.google. com>,
fl*********@hot mail.com says...

Hi out there,

I have problems finding a way to warn a user that
another user intends soon to update the same specific row.
Let me explain.

User 1 get to a JSP "update customer record" page.
- The page does a read of the existing record and
loads it into the gui fields for edit.

while he is doing is editing ... User 2 wants to update the same
"update customer record" page and attemp to load the same record in.
What mecanism can I use to stop him being able
to load the same record and warn him someone else is
updating that record.

It seems trivial but so far I don't know of a lock to stop
me reading a specific record. I must be missing something.

Thanks for your sugestion and specific sql examples.

Fred

I don't know about a lock but you could have a flag field that is set to
signify that the row is in use, that is reset when the updated record is
re written. I know it is brute force and ignorance.

I would also like an elegant solution to this problem.

I would recommend using a lock column in the table (or keep a separate lock
table) . The way I'd use it is that whenever anyone opens the record to edit
it, their name/ref is written to the lock field. When they post changes, the
script compares the lock field user ref with that of the person submitting
the changes and only perform the update if the users match. This way there
is no problem with users locking a record then not sending changes. If the
lock field and the user sending changes dont match, they could be sent a
brief summary of diffferences between their data and the data showing in the
table.

In addition you could also consider saving the original data (and re-send it
in hidden fields), compare it with the data in the table and flag an error
if it doesnt match.

[Fred]

Ok,

Thank you guys. I did think of something of the kind, but
I can't imagine there isn't a way of stoping the user 2
before he acctually tries anny update.

Also I still haven't
found a way to lock a row agaisnt a read, which would get
me pretty close to a clean solution. I can lock against
update but can't see why I shouldn't lock a row against
a read.

Anyone knows of a precise reason to use a Read for update?

thanks,

Fred