How to handle multiple update rquest to a record

[thepreacher]

There is the possibility that more than one person will be trying to update a record  in a database on a web site. Using php how do i ensure that the the update is done the write way (order)?

Also, when users register the user id is auto generated and it is serial. As more that one person could be registering at the time how do i manage this as serial request will be sent at the same time to access a record.

Thanks

[COBOLdinosaur]

With auto increment key ids there is no issue.  Only one row can be wwritten at a time and a unique is applied to each one.  there is no such thing as two rows being written at the same time the second one there gets queued until the first operation completes even if thre is only a micro second difference in arrial time.

As for the update of records, you lock the table for write and then unlock it when the operation is completed.  That prevents te data being acquired for writing by a scecond process.  If there is a possibility that more than one user will acquire the same record for update, then we would have to look at the specific application as there are a number of different strategies and adoption needs to loook at the need for concurency, the availability requirements and volitility of the data to come up with the best solution.  However one general rule will be that the table needs to be lock when you begin the transaction and not unlocked until there is a commit or rollback of the transaction.

[VGR]

ok, I will elaborate a bit (quickly) on CD&'s answer

MySql automatically locks the row when you update it, so the  second update will not mangle the first, except for the data therein of course. Both will theoretically update the same initial data and the results can be surprising to the person doing the first (and "superceded" by the second) update.

Usually, for web sites, this event is very unlikely. People ADD records (answers in a bulletin borad, for instance) but do not UPDATE at the VERY SAME TIME the same row (record), so generally speaking I don't care at all.

You have two solutions if this event has any significant probability of occurence :
- either use explicitly locking. MySql has the ability - it's not that common, even for Oracle - to perform row-locking, page-locking and table-locking (if I'm not too confused at 7:20 am). I think there is a read lock possibility somewhere. The drawback is the table looks unavailable in the meantime, so you've to ***ensure*** you unlock the table whatever happens in your code !
you would just issue :
LOCK TABLE xxx;
statements
UNLOCK table xxx;

- either use a TRANSACTION. On Mysql, this involves using the "InnoDB" engine, so your table should be declared (CREATE TABLE...ENGINE=InnoDB" or altered (ALTER TABLE SET engine=InnoDB" appropriately beforehand
then use BEGIN TRANSACTION... END TRANSACTION to create an atomic critical section
This is useful, theoretically, when you update multiple tables or issue multiple statement you want to ensure the amoticity of ; normally it's not for a single-table update.

[VGR]

the above was at the MySql level (see the mysql online manual for explanation on LOCK or BEGIN TRANSACTION)

At the PHP level, you have more solutions.

For instance, I suppose the operation is clearly identified and rather unique. Let's suppose you can isolate the code part where the table update occurs. You now want to ensure only one person can ***begin*** to update that table at the same time ; thus you want to prevent others to ***engage*** the update ; this means the simpliest way is to prevent them from ***reading*** the table rows.
You could create a semaphore for this section. One solution is to create a "lock file" on the server when the operation starts, refuse to answer (or say' update in progress') when you enter and the lock is there, and free the lock after operation completion. You should in fact manage a queue.

Think twice (or thrice ;-) before implementing such a solution. Is the operation so critical you want to be sure no update can take place if one is running ? Are your users coming so near to each other at the same record ? Say, inside the 100 ms I can think or a reasonable period of time to ensure atomicity ?

[COBOLdinosaur]

One method to avoid a lot of lock management and overhead is to use an update timestamp. When the original data is acquire the timestamp is included.  When the user sends an update, the timestamp is updated to the current value. The where clause of the update includes "U_timestamp = $original_timestamp".  If the record has been updated from the users version then the update fails.  Then instead of an error, the response is simply to re-acquire the data and send it back to the user with a message that the data has changed.  Then they can re-apply their update if they wish.

That works effectively for single table updates.  For multi-table updates you can use a table with the update timestamps associated with the user's session, but it is not ideal.  For multiple table you really need to manage it with a transaction and row locks. Anytime you are using locks, you have to make sure that in all circusmstances the locks will get released.

[VGR]

100% agree with the two solutions provided by the saurian above ;-)

yes, use the timestamp (I wlways have one, and you should too) in the UPDATE WHERE clause. If it fails, it means you fell in the "bad case". Show two sets of data to the user, the one he had written and tried to push to the DB, the other one from the DB. Then reoffer to update.

You've practically no chance of failing three times this way.

Very good idea

I never thought doing this ;-)