Broadcast Radio Forums

Database Record Locking Modes

By Peter Jarrett - Thursday, October 27, 2005 8:32:20 PM

Most of our products now store their information in shared Databases. These files allow very fast searching for information, as well as making applications more scalable as it is very easy for us to add new data - and therefore new facilities.

The drawback is that they have to use 'Record Locking' to ensure that a user doesn't overwrite anothers information by accidentally saving at the same time. This is why you may occasionally see messages such as \"Record Locked by user 'admin' on machine XXXX\".

As the number of computers opening a given database increases, you are more likely to encounter these Record Locking errors when adding or editing information.

Most of our products use the Microsoft Jet Database Engine (v3.6 or v4.0 depending on who at Microsoft you ask) which is an industry standard database that is very easy to setup and administer, as well as providing a very simple and easy way to back up all of your data.

To reduce the number of times you may see the above message, we are changing the way that we access the various databases from 'Page Level Locking' to a new method called 'Record Level Locking'.

Although a full description is beyond the scope of this post, a quick summary is to say that data is stored in databases in 'pages' of information. Each page contains 1 or more 'Records' - a record is single item in the database such as a Scoop Story, an AutoTrack Song or Link, a Directory Contact or a Log Entry in Myriad.

With Page Level Locking, if a user is updating a single record on a page, the whole page has to be locked, with the side effect that it prevents other users from updating other records on the same page - i.e. in Scoop, the stories near the one being edited, and in the Myriad Log, the entries above and below the one being edited.

With Record Level Locking, only the record being updated is locked, so the others are free to be edited.

The drawback to this method is that it is slightly slower, but this shouldn't be a factor on our software as the number of edits happening at any one time is always very low. In fact you are unlikely to notice any significant decreases unless you are running 10 or more busy users from the same database.

Now, the only caveat here is that the new mode can only be activated when a new or existing database is opened for the first time. If a database is already in use by other programs, then the record locking reverts to the old Page locking mode. Therefore you have to quit from all products accessing a database on all computers before it can be activated. In the case of the P Squared Directory, this is obviously used by almost all of our new generation of products, so you may need to plan ahead. Also, don't forget 'backoffice' programs like Scoop Capture that also use the database but can easily be forgotten. If you leave these running, they will keep the database open and the new mode can't therefore be activated.

For more information about this, please see the following article on the Microsoft Website:
By Peter Jarrett - Wednesday, June 21, 2006 4:08:54 PM

If you experience any problems with using the new Locking method, these 2 files allow you to disable the new method, and to re-enable it again. Simply download them onto the computers you need to change and double click on the appropriate file. Then restart ALL of your computers at the same time so that the mode can be fully reset.

Please note that these files will only have an effect on the products listed above - unfortunately they cannot be used to activate the new mode on older versions of our software. They will not cause any harm, but equally, won't cause any good either!