I am writing a web app for a medical billing company. Which users will be able to add, update and delete records. I was wondering if there was a way to lock a record while it is in use so that other users may not access or make changes to this record while it is being used by another.

Example

I have the information for Jon Doe on my screen and you have the information for Jon Doe on your screen.

I decide to update his name to be John Doe and save it. The record in the table will now reflect my changes.

Your screen however will still say Jon Doe. So if you make any changes and save them my record will be overwritten and the information I updated will be lost.

Any information is helpful. Thank you.

Make your application only update changed columns.

Thanks for the reply.

How does it know if the column has changed? Do I test this with php. I thought about that as a solution, but it seems like a lot of work to be testing everyfield for a change on every point of entry.

Thanks

I think I may need to change the title of my thread now.

Apparently there is a portion of MySQL That is called InnoDB that supports row-level locking.

If you could tell me more about InnoDB I would appreciate it, or if you have an alternate solution I would love to hear that too.

Thanks

Row level locking won't help. Somebody is going to SELECT one row from your table, and then disconnect, leaving the row unlocked. Then they'll type something into a text box, hit a button, and your php code will UPDATE the row. That's twice that it will be locked for a fraction of a second, and they might be five minutes apart. In the meantime, another person SELECTs the same row, and gets the old data because it hasn't been changed yet, then fires off an UPDATE query. Again, it's two locks for like 0.0001 second each.

It's not a locking problem so much as a concurrency one. A thousand people have struggled with this before you, and come to all different kinds of solutions. If you used ASP and Access instead of PHP and MySQL the second person in your example could get notified that someone else saved changes to the record, and automatically see them. But since that's not the case, I still think building a query that only saves the fields that have been changed is your best bet.

Learning Newbie, Could you please help me understand what you are trying to say a little better. I am not really getting your example.

I feel like row locking should work.

query -> lock -> display -> update -> unlock

It seems like this is what I am looking for. I don't understand why it wouldn't.

Thanks

Originally Posted by Creadiv

It seems like this is what I am looking for. I don't understand why it wouldn't.

Because the time between when you query the database and display the data to the end user is going to be much, much less than a second. This is an acceptable amount of time to hold a lock for. But the time between when the user sees the data, and goes in and makes the change could be five minutes, or they could click the detail link, to to lunch, and come back an hour later. It's unacceptable to hold a lock that long.

Also, I'm not an expert on MySQL, but how would you do that? The row is locked while the SELECT query is executing, and unlocked as soon as the db sends data to the web server. Then it's locked again when you run your UPDATE is running. But in between the two, it's no locked, available for any kind of change.

Imagine:

User A pulls up the Jon Doe record
User B pulls up the Jon Doe record
User A has a sip of coffee, then changes it to John Doe, and hits save
User B has a conference call with the customer and a manager, and gets approval to mark that the customer should get a 15 % discount on their next order
User B hits save, but they got the data before User A saved it, so the results from the query say Jon Doe, they don't know that a different user changed a different field on the same record. When User B hits save, it wipes out User A's change.

The simple way to do this, and without locking, is to use a timestamp in the table. When you read the record you also read the timestamp (just a field with a time in it). When you update the record you make sure that the record being updated has the same timestamp that you read and update the timestamp yourself.

If no record is updated (because the timestamp has changed) then alert the user.

 
User A reads Jon Does record with timestamp 9:15
User B reads Jon Does record with timestamp 9:15
 
-- user A updates the record successfully at 9:30
update table
set   fields = ...
      timestamp = '9:30'
where ....
       and timestamp = '9:15'
 
-- user B fails to update the record at 10:30
-- because the timestamp has now changed 9:30
update table
set   fields = ...
      timestamp = '10:30'
where ....
       and timestamp = '9:15'

Even better to use a trigger to maintain the timestamp.

Mike