PostgreSQL FOR UPDATE vs FOR NO KEY UPDATE
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!
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?
I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.
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.