Polyhedra uses a simple transactional model that - prior to release 8.7 - did not support pessimistic locking: that is, locking part of the database so that no-one else can alter it in the time between you making a query and then updating the database based on the results of that query. Polyhedra now supports both pessimistic and optimistic locks with fine-granularity, and avoids one client blocking changes for too long by ensuring pessimistic locks automatically convert to optimistic locks after a time-out.

In some cases, though, it is nice to have a have an application-specific locking mechanism, that allows a number of client applications to work together and apply a more flexible locking scheme than that offered by the database software. This article outlines one possible approach to implementing such a scheme.

A simple, co-operative locking scheme

Where a particular requirement exists, applications can co-operate to enforce a application locking protocol using fields or records in the database to record the 'locks'. For example, a table X could have an integer field called locked, and if a client wished to 'lock' a record it could claim it as follows:

... where the number 1234 is the process ID of the client doing the lock, say. To find out whether the lock was successfully claimed, one simply needs to ask how many records were affected by the query: if it is zero, then the record did not exist, or was already claimed (as the locked field is not null). All the Polyhedra client APIs allow application code to discover how many records were directly affected by an SQL transaction, so this does not require an extra interaction with the server.  Alternatively, the client can query the record and check it was able to get the lock by issuing a query such as...

If one record is returned, the 'lock request' was successful.

using the process ID of the client will work if all clients are on the same machine, but could give problems if connections are allowed from remote machine. Fortunately, there is a easy way around this. If you define a dataconnection table in the database (as described in the utility classes manual), then the system automatically creates a record in this table for each new client connection, and the primary key of this record can be used by the client as its unique identifier when locking. Once a connection has been established, the id for that connection can be determined by issuing the following query from the client:

... or one can simply use the GetConnection() function rather than an explicit value when doing the 'update' statement to claim the lock.

At the end of the operations that need to be done, the locked attribute should be reset to NULL. If one wants to lock tables rather than individual records, one can have a 'lock' table with, say, tablename as its primary key and another attribute called locked.

Further extensions

The application-level locking scheme described above can be enhanced by using CL attached to the database, both to simplify creating locks and also to enforce some safety checks. For example, code can be attached to the table to fail any transaction that changed the locked attribute from one non-null value to another. Another possibility would be for the CL to check that only a client that had the 'lock' could update the record (or selected fields of the record), and CL could also ensure both that locks 'time out' and also that locks are freed if the connection is closed or lost. Finally, using CL it also becomes easy to mix the use of record-level locking and table-level locking on the same table.