Using Named Database Locks
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.
- It looks for methods annotated with @UpdateLock and 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 entityTypeIds basically 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.
A beginner’s guide to Java Persistence locking
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Implicit locking
In concurrency theory, locking is used for protecting mutable shared data against hazardous data integrity anomalies. Because lock management is a very complex problem, most applications rely on their data provider implicit locking techniques.
Delegating the whole locking responsibility to the database system can both simplify application development and prevent concurrency issues, such as deadlocking. Deadlocks can still occur, but the database can detect and take safety measures (arbitrarily releasing one of the two competing locks).
Physical locks
Most database systems use shared (read) and exclusive (write) locks, attributed to specific locking elements (rows, tables). While physical locking is demanded by the SQL standard, the pessimistic approach might hinder scalability.
Modern databases have implemented lightweight locking techniques, such as MVCC.
The implicit database locking is hidden behind the transaction isolation level configuration. Each isolation level comes with a predefined locking scheme, aimed at preventing a certain set of data integrity anomalies.
READ COMMITTED uses query-level shared locks and exclusive locks for the current transaction modified data. REPEATABLE READ and SERIALIZABLE use transaction-level shared locks when reading and exclusive locks when writing.
Logical locks
If database locking is sufficient for batch processing systems, a multi-request web flow spans over several database transactions. For long conversations, a logical (optimistic) locking mechanism is much more appropriate.
Paired with a conversation-level repeatable read storage, optimistic locking can ensure data integrity without trading scalability.
JPA supports both optimistic locking and persistence context repeatable reads, making it ideal for implementing logical transactions.
Explicit locking
While implicit locking is probably the best choice for most applications concurrency control requirements, there might be times when you want a finer-grained locking strategy.
Most database systems support query-time exclusive locking directives, such as SELECT FOR UPDATE or SELECT FOR SHARE. We can, therefore, use lower level default isolation levels (READ COMMITTED), while requesting share or exclusive locks for specific transaction scenarios.
Most optimistic locking implementations verify modified data only, but JPA allows explicit optimistic locking as well.
JPA locking
As a database abstraction layer, JPA can benefit from the implicit locking mechanisms offered by the underlying RDBMS. For logical locking, JPA offers an optional automated entity version control mechanism as well.
JPA supports explicit locking for the following operations:
- finding an entity
- locking an existing persistence context entity
- refreshing an entity
- querying through JPQL, Criteria or native queries
Explicit lock types
The LockModeType contains the following optimistic and pessimistic locking modes:
| Lock Mode Type | Description |
|---|---|
| NONE | In the absence of explicit locking, the application will use implicit locking (optimistic or pessimistic) |
| OPTIMISTIC | Always issues a version check upon transaction commit, therefore ensuring optimistic locking repeatable reads. |
| READ | Same as OPTIMISTIC. |
| OPTIMISTIC_FORCE_INCREMENT | Always increases the entity version (even when the entity doesn’t change) and issues a version check upon transaction commit, therefore ensuring optimistic locking repeatable reads. |
| WRITE | Same as OPTIMISTIC_FORCE_INCREMENT. |
| PESSIMISTIC_READ | A shared lock is acquired to prevent any other transaction from acquiring a PESSIMISTIC_WRITE lock. |
| PESSIMISTIC_WRITE | An exclusive lock is acquired to prevent any other transaction from acquiring a PESSIMISTIC_READ or a PESSIMISTIC_WRITE lock. |
| PESSIMISTIC_FORCE_INCREMENT | A database lock is acquired to prevent any other transaction from acquiring a PESSIMISTIC_READ or a PESSIMISTIC_WRITE lock and the entity version is incremented upon transaction commit. |
Lock scope and timeouts
JPA 2.0 defined the javax.persistence.lock.scope property, taking one of the following values:
- NORMAL Because object graphs can span to multiple tables, an explicit locking request might propagate to more than one table (e.g. joined inheritance, secondary tables). Because the entire entity associated row(s) are locked, many-to-one and one-to-one foreign keys will be locked as well but without locking the other side parent associations. This scope doesn’t propagate to children collections.
- EXTENDED The explicit lock is propagated to element collections and junction tables, but it doesn’t lock the actual children entities. The lock is only useful for protecting against removing existing children, while permitting phantom reads or changes to the actual children entity states.
JPA 2.0 also introduced the javax.persistence.lock.timeout property, allowing us to configure the amount of time (milliseconds) a lock request will wait before throwing a PessimisticLockException.
Hibernate locking
Hibernate supports all JPA locking modes and some additional specific locking options. As with JPA, explicit locking can be configured for the following operations:
- locking an entity using various LockOptions settings.
- getting an entity
- loading an entity
- refreshing an entity
- creating an entity or a native Query
- creating a Criteria query
The LockModeConverter takes care of mapping JPA and Hibernate lock modes as follows:
| Hibernate LockMode | JPA LockModeType |
|---|---|
| NONE | NONE |
| OPTIMISTIC READ | OPTIMISTIC |
| OPTIMISTIC_FORCE_INCREMENT WRITE | OPTIMISTIC_FORCE_INCREMENT |
| PESSIMISTIC_READ | PESSIMISTIC_READ |
| PESSIMISTIC_WRITE UPGRADE UPGRADE_NOWAIT UPGRADE_SKIPLOCKED | PESSIMISTIC_WRITE |
| PESSIMISTIC_FORCE_INCREMENT FORCE | PESSIMISTIC_FORCE_INCREMENT |
The UPGRADE and FORCE lock modes are deprecated in favor of PESSIMISTIC_WRITE.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Lock scope and timeouts
- scope The lock scope allows explicit locking cascade to owned associations.
- timeout A timeout interval may prevent a locking request from waiting indefinitely.