How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements

Introduction

Relational database systems employ various Concurrency Control mechanisms to provide transactions with ACID property guarantees. While isolation levels are one way of choosing a given Concurrency Control mechanism, you can also use explicit locking whenever you want a finer-grained control to prevent data integrity issues.

As previously explained, there are two types of explicit locking mechanisms: pessimistic (physical) and optimistic (logical). In this post, I’m going to explain how explicit pessimistic locking interacts with non-query DML statements (e.g. insert, update, and delete).

Domain Model

For the upcoming test cases, we are going to use the following entities:

The Post entity can have one or more PostComment(s) which are associated with their parent entity through the post_id Foreign Key.

Explicit locking

Depending on the underlying database capabilities, a query can also acquire an exclusive (write) or a shared (read) lock on the database rows that are selected by the currently running statement. To see how the pessimistic locks interact with concurrent INSERT, UPDATE, and DELETE statements, we can use exclusive locks since they are better supported by most relational databases.

In the following examples, Alice is going to select all PostComment(s) belonging to a given Post entity while also acquiring an exclusive lock on the selected records. To simplify the lock acquisition, we can use the PESSIMISTIC_WRITELockModeType offered by Java Persistence API. Therefore, Alice query looks as follows:

This way, Hibernate is going to generate the database-specific locking clause on our behalf.

For instance, on Oracle and MySQL, the SQL query looks as follows:

SELECT
pc.id as id1_1_,
pc.post_id as post_id3_1_,
pc.review as review2_1_
FROM post_comment pc
WHERE pc.post_id = 1
FOR UPDATE

On PostgreSQL, an additional OF alias is being employed as well:

SELECT
pc.id as id1_1_,
pc.post_id as post_id3_1_,
pc.review as review2_1_
FROM post_comment pc
WHERE pc.post_id = 1
FOR UPDATE OF pc

While on SQL Server, the syntax is a little bit different than the previous RDBMS:

SELECT
pc.id as id1_1_,
pc.post_id as post_id3_1_,
pc.review as review2_1_
FROM post_comment pc
WITH (UPDLOCK, ROWLOCK)
WHERE pc.post_id = 1

However, you don’t have to worry about all these differences since Hibernate takes care of them for you.

Update statement

Once the database records are locked, no UPDATE statement can modify them, even on a MVCC database engine.

Until Alice releases the locks by ending her transaction, Bob’s UPDATE statement is blocked, and his transaction can no longer make any progress. After Alice has committed, the locks are released, and Bob’s transaction can continue.

Delete statement

Just like the UPDATE, a DELETE statement will also be blocked by the row-level locks acquired previously by Alice’s transaction:

Once the locks are released by Alice, Bob’s transaction can continue, and the DELETE is executed.

Insert statement

Insert statement with MySQL

On MySQL, the explicit pessimistic locking clause, not only it acquires row-level locks on each record that is selected, but it can also acquire predicate locks as well because the InnoDB clustered index supports gaps and next-key locking.

This behavior can be observed only when using the default REPEATABLE READ isolation level. When switching to READ COMMITTED, MySQL behaves like PostgreSQL. One explanation would be that locks that did not match the scan are released after query execution. For more info, read this article on Percona blog.

So, Bob’s INSERT is blocked until Alice releases the predicate lock which was acquired on all (existing and future) PostComment entries that are associated with a given parent Post record.

Therefore, on most RDBMS, explicit physical locks can only prevent record modifications for database records that existed at the time of locking while future records can be added. MySQL is an exception to this rule, preventing INSERT statements against a range of locked entries.

So, the very same outcome you get from using Serializable in regard to Phantom Reads, you also get by using explicit physical locking on a lesser isolation level (e.g. Read Committed). However, just like Two-Phase Locking, explicit locking has its price, so use it judiciously.

Subscribe to our Newsletter

* indicates required

Email Address *

10 000readers have found this blog worth following!

If you subscribeto my newsletter, you'll get:

A free sampleof my Video Course about running Integration tests at warp-speed using Docker and tmpfs