Introduction

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.

SQL NOWAIT

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
    )
);

That’s it!

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW