Re: [PHP] Concurrency in a multi-user environment

Don Proshetsky wrote:
I'm been googling to see if I can find information on how programmers handle concurrency in a multi-user PHP/MySQL environment. Here's a snippet of my app.

I have a form where users can maintain client information such as Name, Address, telephone, email, etc.... I've been wondering how to handle a situation where two users are editing the same record.

One idea is to lock the record when User A enters but this may make it inaccessible for a long period if User A decided to get up and leave for a three hour lunch. Also, does locking a record prevent file locks (when new records need to be added)? Finally, locking a record means I have to convert from MyISAM to InnoDB but this is probably necessary for concurrency.

Another idea is to use a semaphore where User A enters and retrieves a numeric field in the table. Upon leaving, the user locks and checks if the field is the same value. If yes, save, increment the numeric field and leave. If not, it indicates another user entered has edited, saved and left. I'm not crazy about this as it would mean User A could spend 15 minutes updating a record only to get a message that User B already edited it.

Does anyone have any user friendly suggestions or can give me some URLs where I can read up on this?

Personally I would approach this from a non-locking perspective. Store a last updated timestamp with each row. Keep a copy of the data you presented to the user when they started editing. When a modification request comes in you...

1) Lock the row
2) Get the row
3) Compare it to the incoming data and your stored copy
- You can work out which fields the user has changed by comparing the incoming data with the stored copy
- You can work out which fields have been changed since the user started editing by comparing the stored copy with the latest you got in step 2
- You can use the intersection of these two to see if there are any conflicts
4) If there are conflicts, show the user and let them make a decision
5) Update and unlock the row

Note that step 4 actually contains a copy of all the steps again, but before you start you update the stored copy with the latest data you fetched in step 2.

Locking is bad, m'kay!

Hope that makes sense.

-Stut

--
http://stut.net/
.

Relevant Pages

  • Re: [PHP] Concurrency in a multi-user environment
    ... One idea is to lock the record when User A enters but this may make it inaccessible for a long period if User A decided to get up and leave for a three hour lunch. ... locking a record means I have to convert from MyISAM to InnoDB but this is probably necessary for concurrency. ... Compare it to the incoming data and your stored copy ... You can work out which fields have been changed since the user started editing by comparing the stored copy with the latest you got in step 2 ...
    (php.general)
  • Re: Lock-free databases
    ... > lock, latch, enqueue, or other name is a lock for the purpose of this ... Database concurrency control. ... be it Oracle or SQL Server ...
    (comp.databases.theory)
  • Re: How to update multiple rows atomically
    ... I don't want to lock just one up, ... be subject to deadlock or starvation? ... Whether it might experience deadlock or starvation or any other concurrency problem will depend on what concurrency mechanisms the dbms implements, which concurrency options the dba and various users choose, and how the dbms implements them. ...
    (comp.databases.theory)
  • about concurrency (again)
    ... I have been reading the recent thread about concurrency, ... def disable_safe_mode: ... # "instantiate" the manager in the main thread ... the lock could be a file lock or a transactional lock. ...
    (comp.lang.functional)
  • Re: Concurrency Issues
    ... >>done on the file such as ADD at EOF, Modify, Delete, make rise to race ... > If you use file locking, the lock can be specific to a particular ... you lose the appearance of concurrency. ...
    (comp.unix.programmer)