Concurrent Database Access and Locking (Programming the Perl DBI)

2.6. Concurrent Database Access and Locking

Before we start looking at DBM file storage management, we should discuss the issues that were flagged earlier regarding concurrent access to flat-file databases, as these problems affect all relatively low-level storage managers.

The basic problem is that concurrent access to files can result in undefined, and generally wrong, data being stored within the data files of a database. For example, if two users each decided to delete a row from the megalith database using the program shown in the previous section, then during the deletion phase, both users would be operating on the original copy of the database. However, whichever user's deletion finished first would be overwritten as the second user's deletion copied their version of the database over the first user's deletion. The first user's deletion would appear to have been magically restored. This problem is known as a race condition and can be very tricky to detect as the conditions that cause the problem are difficult to reproduce.

To avoid problems of multiple simultaneous changes, we need to somehow enforce exclusive access to the database for potentially destructive operations such as the insertion, updating, and deletion of records. If every program accessing a database were simply read-only, this problem would not appear, since no data would be changed. However, if any script were to alter data, the consistency of all other processes accessing the data for reading or writing could not be guaranteed.

One way in which we can solve this problem is to use the operating system's file-locking mechanism, accessed by the Perl flock() function. flock() implements a cooperative system of locking that must be used by all programs attempting to access a given file if it is to be effective. This includes read-only scripts, such as the query script listed previously, which can use flock() to test whether or not it is safe to attempt a read on the database.

The symbolic constants used in the following programs are located within the Fcntl package and can be imported into your scripts for use with flock() with the following line:

use Fcntl ':flock';

flock() allows locking in two modes: exclusive and shared (also known as non-exclusive). When a script has an exclusive lock, only that script can access the files of the database. Any other script wishing access to the database will have to wait until the exclusive lock is released before its lock request is granted. A shared lock, on the other hand, allows any number of scripts to simultaneously access the locked files, but any attempts to acquire an exclusive lock will block.[12]

[12]Users of Perl on Windows 95 may not be surprised to know that the flock() function isn't supported on that system. Sorry. You may be able to use a module like LockFile::Simple instead.

For example, the querying script listed in the previous section could be enhanced to use flock() to request a shared lock on the database files, in order to avoid any read-consistency problems if the database was being updated, in the following way:

### Open the data file for reading, and die upon failure
open MEGADATA, $ARGV[0] or die "Can't open $ARGV[0]: $!\n";

print "Acquiring a shared lock...";
flock( MEGADATA, LOCK_SH )
        or die "Unable to acquire shared lock: $!. Aborting";
print "Acquired lock. Ready to read database!\n\n";

This call to flock() will block the script until any exclusive locks have been relinquished on the requested file. When that occurs, the querying script will acquire a shared lock and continue on with its query. The lock will automatically be released when the file is closed.

Similarly, the data insertion script could be enhanced with flock() to request an exclusive lock on the data file prior to operating on that file. We also need to alter the mode in which the file is to be opened. This is because we must open the file for writing prior to acquiring an exclusive lock.

Therefore, the insert script can be altered to read:

### Open the data file for appending, and die upon failure
open MEGADATA, "+>>$ARGV[0]"
    or die "Can't open $ARGV[0] for appending: $!\n";

print "Acquiring an exclusive lock...";
flock( MEGADATA, LOCK_EX )
        or die "Unable to acquire exclusive lock: $!. Aborting";
print "Acquired lock. Ready to update database!\n\n";

which ensures that no data alteration operations will take place until an exclusive lock has been acquired on the data file. Similar enhancements should be added to the deletion and update scripts to ensure that no scripts will ``cheat'' and ignore the locking routines.

This locking system is effective on all storage management systems that require some manipulation of the underlying database files and have no explicit locking mechanism of their own. We will be returning to locking during our discussion of the Berkeley Database Manager system, as it requires a slightly more involved strategy to get a filehandle on which to use flock().

As a caveat, flock() might not be available on your particular operating system. For example, it works on Windows NT/2000 systems, but not on Windows 95/98. Most, if not all, Unix systems support flock() without any problems.