I’ve been developing a PHP/MySQL web application that involves multiple users opening and editing records in the database. There is a very real possibility that two or more people will open the same record, make changes, and attempt to save these changes.

The most common concurrent execution problem that I expect to see is the lost update. This would occur when two users open the same record, make changes, and then save the record.

User A opens record “A”.
User B opens record “A”.
User A saves changes to record “A”.
User B saves changes to record “A”.

In this example, User A’s changes are lost - overwritten by User B’s changes.

Initially, I wanted to lock the record using a sort of check-in/check-out system. User A would check-out record “A”, and then have exclusive write access to that record until it was checked back in. The problem with this method is that User A may decide to not make any changes and not save the record, which would leave the record in a checked-out state until further administrative action was taken to unlock it.

I tried to come up with some ingenious way around this, which usually boiled down to somehow automatically unlocking the record after a period of time. But this is not a satisfactory solution. For one thing, a user may have a legitimate reason to keep the record checked-out for longer periods.

So what I eventually came up with is a method of checking whether a record has been changed since it was accessed by the user. My particular way of doing this involves comparing timestamps, but other techniques exist.

Here’s how I’m implementing it:

User A creates record “A” and saves it at 9:00 AM. A “last-saved timestamp” of 9:00 AM is generated and saved to the record.

User A opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the html page.
User B opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the html page.

At 10:30 AM, User A attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 9:00 AM. Because the record has not been changed since it was opened, the record is saved. A new “last-saved timestamp” of 10:30 AM is generated and saved to the record.

At 11:00 AM, User B attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 10:30 AM (User A’s timestamp). Because the record has been changed since it was opened by User B, User B is not allowed to save the record.

User B will have to re-open record “A”, consider the effect that User A’s changes may have, and then make any desired changes.

Unless I’m missing something, this assures that the data from the earlier save will not be overwritten by the later save. To keep things consistent, I’m using PHP to generate all of my timestamps from the server clock, as JavaScript time is based on the user’s system time and is therefore wholly unreliable.

The only drawback that I see is extra work for User B, who has to now review the record as saved by User A before deciding what changes to make.

The strange thing is that I haven’t seen this offered as a solution on any of the pages I found while Googling for solutions to the access control, lost update and other concurrency-related data loss problems.

In this post, I’ll illustrate how to use the IP-to-Country database available from http://ip-to-country.webhosting.info/ to identify the real-world geographic location of visitors to a web page (geolocate) based on their IP addresses. Once you know where a visitor is physically located, you can do all sorts of nifty things, such as send them location-aware […]

In this post, I’ll explain how to reduce the amount of comment spam your WordPress blog receives by using an unobtrusive ‘handshake’ between the two files necessary for a valid comment submission to take place. I’ve written a few different articles on reducing comment spam by means of a challenge response test that the […]

This is a collection of php code snippets that seem to come in handy rather often. They are assembled here more for my own organization than anything else.
String: trim and convert to lowercase
A very straightforward but useful snippet. A string is first trimmed of any leading or trailing white space, and then converted […]

I was getting a 500 error when trying to submit a form mail from my web site via GoDaddy’s gdform.cgi. I turned on error logging and this is what showed up:
SoftException in Application.cpp:624: Could not execute script “/home/content/a/r/d/ardamis/html/cgi/gdform.cgi”
Caused by SystemException in API_Linux.cpp:430: execve() for program “/home/content/a/r/d/ardamis/html/cgi/gdform.cgi” failed: Permission denied
[Sun Dec 2 01:34:05 2007] […]

Comment spam comes from humans who are paid to post it and robots/scripts that do it automatically. The majority of spam comes from the bots. There’s very little one can do to defend against a determined human being, but bots tend to behave predictably, and that allows us to develop countermeasures.
From my observations, […]

This is a rough beta of a new, dark, Plogger 3 theme. It takes elements from the Yahoo! Broadway page of 2007.

Download the beta version (v0.1 beta)
Download the beta version of the Plogger 3 Broadway theme here. I still need to clean up the code and the CSS, and check it more thoroughly […]

I’ve fixed some errors that I was experiencing with version 2.0 (dated 2007-02-02) of the Sociable WordPress plugin by Peter Harkins. Specifically, when running WordPress 2.2+, I would get the following warnings when saving changes:
Warning: implode() [function.implode]: Bad arguments. in PATH\wp-content\plugins\sociable\sociable.php on line 651

Warning: Invalid argument supplied for foreach() in PATH\wp-content\plugins\sociable\sociable.php on line 762

For […]

I was working on a theme for the image gallery Plogger when an old problem cropped up again. I was adding links to thumbnail images, and had given the anchor a padding of 3 pixels and a 1 pixel border so that the link formed a sort of picture frame around the image. […]

This post illustrates a method of centering the thumbnails in the album view of the PHP image gallery Plogger. The method automatically adjusts for thumbnails of varying widths and pages containing less than a full row of images.
This method is implemented in the Plogger 3 theme: Air, but it should work in any theme […]