Introduction

In this article, we are going to investigate the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE when locking a parent record and inserting a child row.

Domain Model

To see the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE locking clauses, consider the following one-to-many table relationship where the post table is the parent, and the post_comment is the child since it has a post_id Foreign Key column referencing the post table Primary Key:

PostgreSQL FOR UPDATE

As I explained in this article, the FOR UPDATE clause is used to take an exclusive lock on a table record that will prevent any concurrent transaction from executing an UPDATE or DELETE statement on the locked record until the locking transaction ends via a commit or a rollback.

However, if we acquire an exclusive lock on a post table record via the FOR UPDATE clause, then we will also prevent other transactions from inserting records in child tables.

As illustrated by the following diagram, the FOR UPDATE lock on the post record with the identifier value of 1 prevents the INSERT into the post_comment if the child record has a post_id Foreign Key column value referencing the locked post table row:

We can easily test the PostgreSQL FOR UPDATE behavior using the following test case:

AtomicBoolean prevented = new AtomicBoolean();
 
doInJPA(entityManager -> { 
    final Post _post = (Post) entityManager.createNativeQuery("""
        SELECT id, title
        FROM post p
        WHERE id = :id
        FOR UPDATE
        """, Post.class)
    .setParameter("id", 1L)
    .getSingleResult();
 
    executeSync(() -> {
        try {
            doInStatelessSession(session -> {
                session.doWork(this::setJdbcTimeout);
 
                session.insert(
                    new PostComment()
                        .setId(POST_COMMENT_ID.incrementAndGet())
                        .setReview(
                            String.format(
                                "Comment nr. %d", 
                                POST_COMMENT_ID.get()
                            )
                        )
                        .setPost(_post)
                );
            });
        } catch (Exception e) {
            prevented.set(ExceptionUtil.isLockTimeout(e));
        }
    });
});
 
assertTrue(prevented.get());
LOGGER.info("Insert was prevented by the explicit parent lock");

When running the above test case, we get the following log messages:

[Alice]
SELECT id, title
FROM post p
WHERE id = 1
FOR UPDATE

[Bob]
SET statement_timeout TO 1000

[Bob]
INSERT INTO post_comment 
  (post_id,review,id) 
VALUES
  (1, Comment nr. 1, 1)

[Bob]
SQL Error: 0, SQLState: 57014
ERROR: canceling statement due to statement timeout
  Where: while locking tuple (0,1) in relation "post"
SQL statement
SELECT 1 
FROM ONLY "public"."post" x 
WHERE "id" OPERATOR(pg_catalog.=) $1 
FOR KEY SHARE OF x

[Alice]
-- Insert was prevented by the explicit parent lock

Looking at the SQL log, we can see why the FOR UPDATE clause on the parent post record blocks the INSERT on the post_comment child record. It’s all because of the FOR KEY SHARE lock request that the post_comment INSERT tries to take implicitly on the the post record. And, as clearly stated by the PostgreSQL documentation, the FOR UPDATE blocks the FOR KEY SHARE lock request.

PostgreSQL FOR UPDATE vs FOR NO KEY UPDATE

When investigating the PostgreSQL documentation, we can see that the FOR NO KEY UPDATE clause does not block the FOR KEY SHARE lock request, as illustrated by the following diagram:

Again, we can easily validate the behavior of the PostgreSQL FOR NO KEY UPDATE clause using a test case that looks like this:

AtomicBoolean prevented = new AtomicBoolean();
 
doInJPA(entityManager -> {
    final Post _post = (Post) entityManager.createNativeQuery("""
        SELECT id, title
        FROM post p
        WHERE id = :id
        FOR NO KEY UPDATE
        """, Post.class)
    .setParameter("id", 1L)
    .getSingleResult();
 
    executeSync(() -> {
        try {
            doInStatelessSession(session -> {
                session.doWork(this::setJdbcTimeout);
 
                session.insert(
                    new PostComment()
                        .setId(POST_COMMENT_ID.incrementAndGet())
                        .setReview(
                            String.format(
                                "Comment nr. %d", 
                                POST_COMMENT_ID.get()
                            )
                        )
                        .setPost(_post)
                );
            });
        } catch (Exception e) {
            prevented.set(ExceptionUtil.isLockTimeout(e));
        }
    });
});
 
assertFalse(prevented.get());
LOGGER.info("Insert was not prevented by the explicit parent lock");

When running the above test case, we can see that this time, the INSERT will pass through without being blocked:

[Alice]
SELECT id, title
FROM post p
WHERE id = 1
FOR NO KEY UPDATE

[Bob]
SET statement_timeout TO 1000

[Bob]
INSERT INTO post_comment 
  (post_id,review,id) 
VALUES
  (1, Comment nr. 1, 1)

[Alice]
-- Insert was not prevented by the explicit parent lock

Awesome, right?

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

Conclusion

While the FOR UPDATE is a very well-known clause that’s supported by many relational database systems, such as Oracle or MySQL 8, PostgreSQL supports a weaker version of it that can allow us to avoid blocking the child record INSERT statements.

For this purpose, PostgreSQL offers the FOR NO KEY UPDATE clause that does not block the implicit FOR KEY SHARE that’s acquired by INSERT statements.

Insert details about how the information is going to be processed

DOWNLOAD NOW