SQL Server Foreign Key Locking
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 see how SQL Server Foreign Key constraints are locking the parent record when executing a child record UPDATE.
This situation is specific to SQL Server and happens even when using the Read Committed Snapshot Isolation level.
Database tables
Let’s consider we have the following Post
and PostComment
tables that form a one-to-many table relationship:
The PostID
column in the PostComment
table has a Foreign Key relationship with the PostID
column in the Post
table.
SQL Server Concurrency Control
By default, SQL Server uses 2PL (Two-Phase Locking), meaning that a Read operation will acquire a Shared Lock while a Write operation will acquire an Exclusive lock.
However, SQL Server also supports MVCC (Multi-Version Concurrency Control) via the following two isolation levels:
Read Committed Snapshot Isolation
, which allows an SQL statement to find the database as of the beginning of the current running querySnapshot Isolation
, which allows an SQL statement to find the database as of the beginning of the current running transaction
To switch from the default 2PL-based Read Committed
isolation level to the MVCC-based Read Committed Snapshop Isolation
, you need to enable the READ_COMMITTED_SNAPSHOT
configuration property, like this:
ALTER DATABASE [high_performance_java_persistence] SET READ_COMMITTED_SNAPSHOT ON
SQL Server Foreign Key Locking
Assuming we are using the Read Committed Snapshot Isolation
level, let’s try to update a Post
record in Alice’s transaction:
LOGGER.info( "Alice session id: {}", entityManager.createNativeQuery( "SELECT @@SPID" ).getSingleResult() ); LOGGER.info("Alice updates the Post entity"); Post post = entityManager.find(Post.class, 1L); post.setTitle("ACID"); entityManager.flush();
And, Bob updates a PostComment
child row that’s associated with the Post
record modified by Alice:
LOGGER.info( "Bob session id: {}", _entityManager.createNativeQuery( "SELECT @@SPID" ).getSingleResult() ); LOGGER.info("Bob updates the PostComment entity"); PostComment _comment = _entityManager.find(PostComment.class, 1L); _comment.setReview("Great!"); _entityManager.flush();
Normally, you’d expect both transactions to complete successfully, but, in reality, Bob’s transaction is blocked by Alice’s transaction, as illustrated by the following diagram:
When inspecting the log, we can see that, indeed, Bob’s UPDATE is blocked and waiting for Alice to release the Exclusive Lock she acquired on the Post
record:
-- Alice session id: 58 -- Alice updates the Post entity UPDATE Post SET Title = 'ACID' WHERE PostID = 1 -- Bob session id: 60 -- Bob updates the PostComment entity UPDATE PostComment SET PostID = 1, Review = 'Great!' WHERE PostCommentID = 1 | table_name | blocking_session_id | wait_type | resource_type | request_status | request_mode | request_session_id | |------------|---------------------|-----------|---------------|----------------|--------------|--------------------| | dbo.Post| 58| LCK_M_S| KEY| WAIT| S| 60|
The reason why Bob’s UPDATE requests a Shared lock on the Post
record is because the UPDATE statement includes the PostID
Foreign Key column.
In SQL Server, when the Foreign Key is updated, if the associated Primary Key is clustered, then the database engine attempts to acquire a Shared Lock on the clustered index record to ensure that the parent row is not going to be modified prior to committing the child record modification.
By default, Hibernate includes all entity columns when executing an UPDATE statement, and this can increase the likelihood of blocking.
If you’re using Hibernate with SQL Server, you should use the
@DynamicUpdate
annotation on entities that contain@ManyToOne
or@OneToOne
associations.
Therefore, when adding the @DynamicUpdate
annotation to the PostComment
entity:
@Entity(name = "PostComment") @Table( name = "PostComment", indexes = @Index( name = "FK_PostComment_PostID", columnList = "PostID" ) ) @DynamicUpdate public class PostComment { @Id @Column(name = "PostCommentID") private Long id; @Column(name = "Review") private String review; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "PostID") private Post post; //Getters and setters omitted for brevity }
And rerunning our test case, we can see that there’s no blocking, and both statements complete right away:
-- Alice session id: 51 -- Alice updates the Post entity UPDATE Post SET Title = 'ACID' WHERE PostID = 1 -- Bob session id: 53 -- Bob updates the PostComment entity UPDATE PostComment SET Review = 'Great!' WHERE PostCommentID = 1
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Understanding how the underlying relational database system implements its concurrency control mechanism is very important if you want to develop a high-performance data access layer.
In SQL Server, an UPDATE statement that includes the Foreign Key column tries to acquire a Shared Lock on the associated parent record, and, for this reason, the UPDATE can block if a concurrent transaction holds an Exclusive Lock on the associated parent record.
For Hibernate applications, it’s good practice to use the @DynamicUpdate
annotations for entities that contain @ManyToOne
or @OneToOne
associations to reduce contention on parent records whenever a child entity gets updated.
