The best way to use SQL NOWAIT
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!
In this article, we are going to see what is the best way to use the amazing SQL NOWAIT feature that allows us to avoid blocking when acquiring a row-level lock.
Since all the top major database support this functionality, Hibernate offers a NOWAIT option that allows you to render the proper database-specific syntax associated with this feature without risking database portability.
Lock acquisition blocking
Relational database systems operate like state machines, and database transactions change the database from one consistent state to another consistent state. In case of a data integrity issue, the database system must be able to successfully roll back all the pending changes and restore all modified records to their previous consistent state.
In order to provide consistent rollbacks, relational database systems must avoid the Dirty Write or G0 anomaly. You can read more about this anomaly in this scientific paper from 1995 that’s remarkably accurate to the day.
Even if most relational database systems use the MVCC (Multi-Version Concurrency Control) mechanism to coordinate read and write operations, pessimistic locking is still employed whenever an UPDATE or DELETE operation is executed.
Whenever we are executing an UPDATE or a DELETE statement on a given table record, a relational database system will acquire and hold an exclusive lock on that record until the current transaction ends with a commit or a rollback, as depicted by the following diagram.
Alice’s UPDATE takes a lock on the
post table record. So, when Bob wants to acquire a lock using a
FOR UPDATE clause, his lock acquisition request will block until either Alice’s transaction end or a lock acquisition timeout is thrown.
The same behavior can be emulated using SELECT queries that use the
FOR UPDATE clause, as illustrated by the following diagram:
By acquiring and holding the exclusive locks until the transaction end, relational database systems manage to avoid the Dirty Write anomaly and, therefore, guarantee Atomicity.
YugabyteDB doesn’t use pessimistic locking to prevent the Dirty Write mechanism. Instead, it uses optimistic locking to detect this anomaly at commit time.
Check out this article for more details about how YugabyteDB provides a fully-optimistic approach when it comes to coordinating access.
To avoid blocking a SQL statement on the lock acquisition request, we can use the NOWAIT clause, as illustrated by the following diagram:
Now, upon acquiring a lock, the statement will immediately throw a lock acquisition failure instead of blocking, so you can catch the exception and continue doing other things before retrying to acquire the lock later on.
Depending on the database system you are using, you may need to use a specific SQL clause for the NOWAIT clause:
| Database | Exclusive lock with NOWAIT clause | |------------|----------------------------------------| | Oracle | FOR UPDATE NOWAIT | | SQL Server | WITH (UPDLOCK,HOLDLOCK,ROWLOCK,NOWAIT) | | PostgreSQL | FOR NO KEY UPDATE NOWAIT | | MySQL | FOR UPDATE NOWAIT |
Luckily, when using JPA and Hibernate, you don’t need to write a native SQL query to get the NOWAIT clause since the framework can properly generate the SQL syntax based on the underlying database.
To do that, you just have to use the
LockOptions.NO_WAIT option when acquiring a row-level lock, as illustrated by the following example:
Post post = entityManager.find( Post.class, postId, LockModeType.PESSIMISTIC_WRITE, Map.of( SpecHints.HINT_SPEC_LOCK_TIMEOUT, LockOptions.NO_WAIT ) );
I'm running an online workshop on the 11th of October about High-Performance SQL.
The SQL NOWAIT clause is very handy when you want to avoid blocking threads.
And not only that it’s widely supported, but JPA and Hibernate make it very easy to use this feature in a portable way.