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 query
• Snapshot 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?

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

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW