Web based concurrency solution required for database editor in php

Question

Hi,

I want to have a record editor in PHP and I want to be able to detect if it's been updated already during the commit.

I've already read some solutions, and they suggest a timestamp column, or rowversion.

Does anyone have any other general solutions that do not involve modifying the database structure?

I was going to hash the record content on the initial select and store in hidden field, and then after user submits changes:

1. START TRANSACTION
2. Perform same select again, but with an optomisitc lock
3. Check the new hash is not different to original, roll back if so.
4. Update, relying on DB to catch the optomisitc lock violation possible between stage 3 and 4
5. COMMIT

Has anyone got any better methods?

Thanks,

Alan

Answer 1

Keep a modification timestamp on every record - then when you try to write it back....

UPDATE sometable SET .... 
WHERE primary_key=$original_primary_key
AND timestamp=$original_timestamp

If rows updated = 0, then someone else has updated the record since you retrieved it.

C.