NYCPHP Meetup

[David Sklar]

On Thursday, December 11, 2003 12:13 PM,  wrote:

> Basic question. What is/are the recommended strategies for dealing
> with the lost update problem in a PHP/MySQL application? The scenario
> I'm thinking of is not the old shirt inventory example which can be
> solved with relative as opposed to absolute updates. I'm thinking:
> User1 requests a page containing a form populated by a row of John
> Customer's data from a table; so does User2. User1 updates John
> Customer's work phone in the form and User2 updates John Customer's
> home phone. User1 submits form/updates table, then User2 does
> likewise, overwriting User1's update.

1. Include a "last_update" column in the table. This can be a timestamp or
just a monotonically increasing integer that changes with each update.

2. When you read the record and display the form, include the last_update
value as a hidden field in the form.*

3. When the form is submitted, check to see that the last_update value
submitted with the form is the same as the value in the database before
updating.**

4. If the values don't match, take some application-appropriate behavior,
like silently overwriting with the new data; re-presenting the form to the
user with fields highlighted that have changed; etc.

5. If the values do match, then save the data, including a new value for the
last_update column.

* You may want to obfuscate or encrypt the value when you include it in the
form to discourage tampering.

** Obviously, you need to read the last_update value and have your UPDATE
occur with no other updates sneaking in between them. If you're using InnoDB
tables, use a transaction, otherwise, lock the table for writing before you
read the last_update value and then unlock it after you either write the new
data or decide that you can't write because it's changed.


David