I have a simple rule when it comes to writing to the database; it should be considered like spanking a child:
- Only do it when it is really, absolutely necessary
- Feel a little guilty every time you do it
- If you are doing it too often, seek professional help
Sure, this is a little tongue-in-cheek, but there is a pretty serious side to it to. The other day on Slashdot I saw a discussion on managing open file/record locks in a database, and a 'marked informative' suggested solution was to update a date field in the database every 10 seconds via Ajax, with an overall 30 second window to allow for network failures/etc. In other words, what was proposed (and popularized) was that every active record should call home every 10 seconds merely to say somebody is looking at it. Gads!!!!! This would add a huge amount of wasted database processing to an application and performance drain on a grand scale (especially if it required transactions and/or write locks).
To maintain a 'lock' on a record, all you need to have a central point from which you can check in/check out a record. The database seems an obvious choice, but it is almost always the wrong one. The solution suggested above is one that isn't adding information to a database repository, but rather abusing it by using it as an application scope. Instead of this, we can use the 'real' application scope to maintain our locks. We can still use Ajax as suggested, but instead of database updates we'll stick to a simple application-scoped component.
The basic process is this: when a record is opened from the database, the web page displaying the record (usually a form) initiates an ajax call on a timed loop that calls home every X seconds to say "I'm still editing this record". A time stamp is refreshed with each call, and as long as the time stamp is less than the proscribed "lock time" limit, the file is considered locked. When the file is submitted (or the user otherwise unlocks the file) it is removed from the list and considered available for access.
First we start with FileLock.cfc. This component is going to live in our application scope and hold all of our active record locks:
The init() function sets up our time- and lock-tracking variables. We pass in "lockTimeLength", which is the length in time we want a record to be considered "locked", and "purgeTimeLength", which sets the frequency (again, in seconds) in which we purge old records. In working with caching recently, I discovered that getTickCount() is a beautiful way to track the age of a record because it gives you a value that can be easily sorted.
The next stage is to add a function that allows us to register our locks:
There's a call in there to purge locks, but we'll deal with that later. Essentially all we are doing here is adding the fileID to a structure in the component. The new record value is getTickCount() so we have a time snapshot of when the record was added. We're going to call this function from Ajax on a regular basis, refreshing the age of the record lock every time we do.
The next step is implementing a way of checking for locks:
There are a couple of steps in this function. The first is to see how old our application is. If the application just restarted, we don't want a bunch of active file locks to be ignored and everybody else piling in and grabbing these records, so we basically say "every file is locked until the minimum lock time has passed". For instance, if we set the "lockTimeLength" variable to 30 seconds, when our application restarts nobody can access a file for 30 seconds. During this period all the active locks out there are calling in and refreshing their status in the 'fileLocks' structure.
If the application is older than the base lock time, it can now check individual locks. We now check to see if a) the fileID is in the structure and b) if the time stamp (tick count) is within our 'locked' time limit. If it is locked we return 'true', if it isn't we clear the lock and return 'false'.
Let's look at the last two functions:
The clearFileLock() function does just that, deletes the fileID from our list. The purgeOldFileLocks() is a clean-up routine that sorts the list by their time stamp (tick count) and then deletes the records that are too old. Once a 'young' record is found, we escape from the loop via <cfbreak> and reset our purge timer.
The example Ajax is pretty straight-forward, thanks to the beauty of jQuery:
Essentially we have three function calls. In this example, I have a hidden form field called "FileID" that contains my, er, FileID. When "doLock" is called, jQuery grabs the field value and passes it via Ajax to a basic file locking/unlocking cfm page. Thanks to the fantastic jquery.timers plugin, we can have this call repeat itself on a regular basis until the timed call is halted (via "releaseLock") or actively terminated (via "dounLock"). If we submit the form, the repeating lock goes away and soon (within "lockTimeLength" seconds) the lock will be purged.
So, all together we have a solution that eliminates the database completely and keeps locks safe even if the application is restarted. By adjusting the purge and lock times, you can find a happy medium between security and network traffic. How long your locks are maintained depends upon your infrastructure; the example has 3-second updates and 10-second locks which would likely be wasteful in a 'real' environment (updates every 20-30 seconds and a lock time of 2-5 minutes is probably more than generous). The only other thing I'd suggest is that you pad your "lockTimeLength" value to allow for at least 2-3 "I'm still locked" calls before releasing your lock. This will allow for a couple of network failures before the lock is released. And if database updates are important, you can at least relegate this to another periodic event where locks are set en masse and at wider intervals rather than individually and often.
You may also want to add locks around your writes, but I don't think they'd be really necessary. Because you've padded your timestamps within the overall lock time, collisions shouldn't occur and if they do it'll be on the side of caution (i.e. the file will return as 'locked' rather than 'available'). You can also enhance this by immediately adding the fileID to the fileLock.cfc when it is requested from the database, or even adding an initial database flag when a file is first called and finally written. The biggest thing is that you don't spank your database unnecessarily!
The database is almost always the most vertical aspect of any application infrastructure. You might have dozens of web servers, cache servers, etc. but the database is usually sitting on one or two expensive and precious servers. Respect the rule that application processing cycles are much much cheaper than database processing cycles and your infrastructure expenses will be much lower.
Example: view the simple demo
Download: download the demo and files
Mar 19, 2010 at 9:36 AM I love this example, but I've notice that it doesn't work in IE. I've toyed with it a bit, but can't get it to work, then I noticed that your demo also doesn't seem to like IE. The lock doesn't re-lock and the lock is purged. Is it the "everyTime" that IE doesn't like?
Mar 19, 2010 at 1:04 PM I tried similar functions using setInterval rather than everyTime, to no avail. For anyone who has come across this dilemma, it turned out to be IE caching. I found a solution from someone who had a similiar issue with IE caching and ajax. Adding a timestamp to the ajax call does the trick. The timestamp will be discarded/unused, but including does away with the caching. In keeping with your example here, to filelock.jquery.js add:
var date = new Date();
var timestamp = date.getTime();
...
url: unlockUrl + "&fileID=" + $("#fileID").val() + "&time="+ timestamp,