In a beginner’s guide to concurrency, I mentioned advisory locks. These are not the usual table locks – they are table-agnostic, database-specific way to obtain a named lock from your application. Basically, you use your database instance for centralized application-level locking.
What could it be used for? If you want to have serial operations, this is a rather simple way – no need for message queues, or distributed locking libraries in your application layer. Just have your application request the lock from the database, and no other request (regardless of the application node, in case there are multiple) can obtain the same lock.
There are multiple functions that you can use to obtain such a lock – in PostgreSQL, in MySQL. The implementations differ slightly – in MySQL you need to explicitly release the lock, in PostgreSQL a lock can be released at the end of the current transaction.
How to use it in a Java application, for example with spring. You can provide a locking aspect and a custom annotation to trigger the locking. Let’s say we want to have sequential updates for a given entity. In the general use-case that would be odd, but sometimes we may want to perform some application-specific logic that relies on sequential updates.
@Before("execution(* *.*(..)) && @annotation(updateLock)")
public void applyUpdateLocking(JoinPoint joinPoint, UpdateLock updateLock) {
int entityTypeId = entityTypeIds.get(updateLock.entity());
// note: letting the long id overflow when fitting into an int, because the postgres lock function takes only ints
// lock collisions are pretty unlikely and their effect will be unnoticeable
int entityId = (int) getEntityId(joinPoint.getStaticPart().getSignature(), joinPoint.getArgs(),
updateLock.idParameter());
if (entityId != 0) {
logger.debug("Locking on " + updateLock.entity() + " with id " + entityId);
// using transaction-level lock, which is released automatically at the end of the transaction
final String query = "SELECT pg_advisory_xact_lock(" + entityTypeId + "," + entityId + ")";
em.unwrap(Session.class).doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
connection.createStatement().executeQuery(query);
}
});
}
}What does it do:
- It looks for methods annotated with
@UpdateLockand applies the aspect - the UpdateLock annotation has two attributes – the entity type and the name of the method parameter that holds the ID on which we want to lock updates
- the
entityTypeIdsbasically has a mapping between a String name of the entity and an arbitrary number (because the postgres function requires a number, rather than a string)
That doesn’t sound very useful in the general use-case, but if for any reason you need to make sure a piece of functionality is executed sequentially in an otherwise concurrent, multi-threaded application, this is a good way.
Use this database-specific way to obtain application-level locks rarely, though. If you need to do that often, you probably have a bigger problem – locking is generally not advisable. In the above case it will lock simply on a single entity ID, which means it will rarely mean more than two requests waiting at the lock (or failing to obtain it). The good thing is, it won’t get more complicated with sharding – if you lock on a specific ID, and it relies on a single shard, then even though you may have multiple database instances (which do not share the lock), you won’t have to obtain the lock from a different shard.
Overall, it’s a useful tool to have in mind when faced with a concurrency problem. But consider whether you don’t have a bigger problem before resorting to locks.
| Reference: | Using Named Database Locks from our JCG partner Bozhidar Bozhanov at the Bozho’s tech blog blog. |
