Locking for concurrency control is not directly addressed by PriDE but can be achieved by simple patterns. Usually, the object locking strategy of an application is a more general design decision and nothing you decide individually for every single table / entity. The examples in the section therefore demonstrate pattern which can be encapsulated in base classes and need to be implemented only once.
This manual will not go into details about what kind of locking to prefer over the other for which kind of business requirements. There lots of general introductions on concurrency control on the Internet, e.g. on Wikipedia.
Optimistic Locking is a typical concept for the management of concurrent update access from multiple applications on the same record in a database. An update of an existing record is only performed if it has not been modified by someone else since the current application has read the record of interest from the DB the last time. If it was modified, the caller is informed about a concurrent access conflict. The concept requires a version counter in the table. Every update operation increments this version counter and performs the actual update only if the entity’s version counter value in memory is still the same as in the database.
Optimistic locking only makes sense for single-record updates by primary key, so the essential aspect of the pattern is to override the update() method of an adapter class resp. a hybrid entity.
The CUSTOMER table being used in almost all the manual examples, can be equipped for optimistic locking, by adding an appropriate counter:
create table LOCKABLECUSTOMER (
id integer not null primary key,
name varchar(20),
first_name varchar(30),
version integer not null
);The resulting entity class gets an appropriate attribute int version; and the entity’s update() method must be overridden like that:
@Override
public int update() throws SQLException {
version++;
int numRows = update(where().and(COL_VERSION, version-1));
if (numRows == 0) {
version--;
throw new SQLException("optimistic lock error");
}
return numRows;
}The update() method without parameters is the update by primary primary key, and the override above works as follows:
The code may be well encapsulated in a base class for all entity types which require optimistic locking. You can find an appropriate base class and derived OptimisticCustomer class in package locks of the PriDE manual source code repository on GitHub.
In SQL databases, pessimistic locking is usually achieved by selecting-for-update operations. I.e. instead of overriding the update() method, pessimistic locking requires to override the find() method without parameters. Like the update() method without parameters, find() addresses the entity’s primary key and therefore is a single-record operation. As a difference to optimistric locking, it does not require any additional columns to organize the locking, However, it requires the database to actually support select-for-update which is sometimes not the case for server-less databases. SQLite e.g. doesn’t support select-for-update as any manipulative DB operates always locks the whole database.
The following override will do the job:
@Override
public boolean find() throws SQLException {
return find(where().forUpdate());
}As you already from the optimistic locking example, the method where() assembles a selection criterion based on the entity’s primary key attributes. The appended call of forUpdate() adds the required “… FOR UPDATE” to the constraint which then is used to call the find() method accepting a WhereCondition.
Keep in mind that there are a few more find() methods which you may have to override. Method findXE() is based on find(), so it doesn’t need an extra override, but findRC() e.g. has its own implementation. The overrides can be well encapsulated in a base class for all entity types which require pessimistic locking. You can find an appropriate base class and derived PessimisticCustomer class in package locks of the PriDE manual source code repository on GitHub.