Not so simple after all.

This is a classic database problem, a situation that every database front end needs to address. The solution usually involves database locks. A database lock is just that, a lock implemented at the database level; locking a row prevents any other requests from making updates or even placing a lock, so using database locks, the prior scenario would play out like this:

This type of locking occurs within the database (not in the client), and most databases provide mechanisms with which to lock and unlock rows as needed.

But this is where things start to get messy - database locks are generally only of use within a database session, as long as clients are connected to the database. Database locks are rather useless when the client is a Web browser accessing the database via an application server. The page and script development model used in browser-based applications makes database locking very tricky - there is typically no clean way to lock a row in one page and then unlock it in another page. And even if there was, because Web browsers are not connected clients (connections are made and broken as needed), you'd run the risk of having locks that never get unlocked, page refreshes unable to access data as the same client already has a lock, and more.

This is not a ColdFusion problem, this is a fundamental limitation of Web clients - the statelessness of the Web makes traditional database locking not very useful. Database locks can work on a single page but not across pages. As for ColdFusion developers, <CFLOCK> can't help you here. For starters <CFLOCK> only locks within ColdFusion and would be useless if other clients were used too. But more important, <CFLOCK> can only lock within a request and cannot span requests (just like database locks). <CFTRANSACTION> has the same limitations.

So, are there any options? Actually, there are several, all usable and all imperfect. I am going to present three of these to you and explain the pros and cons of each.

Solution 1: Database Flags
One obvious solution is to implement your own database-locking scheme to be used in lieu of actual locking. This usually involves adding a flag field to each database table, maybe called "locked." You could set this field value to true (1) to place a lock and to false (0) to release a lock. You (and all developers) would need to check this field before any update (or delete) operations are attempted, and would be responsible to set the value yourself.

This is actually a very workable solution, one that can even work when multiple clients are used (not just your ColdFusion code).

It is also a very risky solution. Why? First of all, you and your developers will need to be sure to always check and set the flags, and it is dangerous to rely on developers remembering to do this. You could enforce the process by not allowing direct access to the tables and forcing the use of stored procedures, but that is a lot of work and the wrapper code is not trivial. But the biggest problem with this solution is that you'd need to implement a way to deal with orphan locks. What would happen if a user requested a row (locking it) and then closed the browser or went to another page? What would happen if the user tried to refresh the page and was told that the row was locked even though it was that user who placed the lock? The risk of orphan locks is very high, so you would need to implement a timeout mechanism of your own.

Solution 2: The 'Lock List'
Another option is to maintain your own lock list. In ColdFusion this could be an array in the APPLICATION scope that stores the primary keys of rows that are "locked." When a user wants to lock a row, your code would scan the array to see if that row's primary key was already listed as locked. If yes, you'd not allow the lock, and if not, you'd lock it by adding it to the array.

As a ColdFusion array the code would be easy to implement, and timing out entries would not be difficult either (you'd store the lock time with the primary key and would check for timeouts regularly, maybe prior to each scan).

But this is also a very dangerous solution. Why? Again, you and your developers would need to be careful to always scan the array, but you'd not really be able to enforce this process. But the biggest problem is that the lock list is ColdFusion specific - there is nothing to prevent another client from updating (or even deleting) the row while you have it "locked." This would make the solution useless unless you were absolutely certain that only your ColdFusion code made table changes.

Solution 3: Just Don't Do It
If database locks aren't usable, and manual locking schemes are too risky, then maybe the best option is to not even bother locking.

Database locking is based on the premise that users should never be able to reach an edit screen if that edit cannot be completed. But what if you did allow them to proceed with an edit even though it might cause a conflict? What if instead of worrying about locking so as to avoid dueling edits, you allowed the edit and instead notified the user upon form submission if the edit could not be completed? How could you do this? It's quite simple.

All you need to do is keep a copy of the pre-edit data (perhaps in SESSION variables), and then when the user submits the edit form, SELECT the row to be edited and check that it is still the same as it was when the edit form was first created (you could also check that it still exists and was not deleted). If the row contents match the saved values you would save the edits, and if not you would notify the user (perhaps showing the various changes and prompting as you see fit). Of course, the data could change even within the time between checking the record and proceeding with the update. <CFTRANSACTION> and its ISOLATION attributes can help with that, depending on the database.

This is a very workable solution. The beauty of it is that there are no locks to clean up or time out, and the solution works even if edits are made using other clients.

Is there a downside? Yes, first of all you and your developers need to remember to do this for each update; the process cannot be enforced. In addition, this solution would be highly inappropriate for applications where there is a frequent risk of concurrent edits (fortunately this is usually not the case). And finally, the ColdFusion code is not trivial (although a well-written Custom Tag or CFC could make the job easier).

Summary
The inability to use standard database locks is a real problem, and one too often overlooked by application developers. This is not a ColdFusion limitation; it is a byproduct of the very nature of the Web. Talented developers have created all sorts of solutions to this problem, and I've presented three popular ones above. Try them, experiment, and if you come up with creative solutions of your own I'd love to hear about them.