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 schemeWhere 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 update X set locked=1234
where id=10 and locked is null ... 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... select id from X
where id=10 and locked=1234 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 select id from dataconnection where id=GetConnection(); ... 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 Further extensionsThe 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 |
How-to Guides >