This is the first time I post for this fantastic initiative called T-SQL Tuesday, created by Adam Mechanic (b|t), and in this occasion hosted by Arun Sirpal (b|t) on this post. I postponed my participation on this blog party, mostly because my blog is mostly targeted to spanish speaking readers, however I did not want to let it pass this time, because I have a really good story to share.
Not long ago I entered into a new company where they had several database related problems, however the most urgent was the infamous blocking sessions. These blocking problems were created by a poorly programmed application, but since it was so old and so critical, nobody had the curage or knowledge to change a bit of the code. The duration of each blocked session could reach up to 2 minutes, yes you read that right, 2 minutes.
Digging into the issue I found that the most common blocking pattern was as follow:
- Session 1: updated an auditing column on the parent table. (Blocker Session)
- Session 2: inserted a record on the child table for the same record updated on session 1. (Blocked Session)
Off the top of my head I could no find a reason why session 2 got blocked by session 1, as it was not touching any column from parent table. Reviewing the blocking resources I found that since both tables were joined by a foreign key constraint, when the insert in the child table occurred, SQL Server had to check on the parent table if the value for the foreign key was valid, so it had to do a cluster index seek on the parent table. I thougth I found the cause and then I decided that since there were so much blocking in this database I would enable the “Read Commited Snapshot” isolation level, allowing readers not to be blocked by writers and viceversa, that would allow the insert statement to complete without getting blocked, right? However when I reviewed the database configuration it was already enabled.
Turns out that Foreign Key validation is always done under the “Read Commited” isolation level regardless of the isolation level you configured on your user database. So I spent several hours banging my head on the wall trying to come up with a workaround to solve the issue or at least reduce the impact, and then it hit me like a lightning, I have to avoid validating against the Cluster index, so I came up with two possible work arounds:
- Convert the Cluster Primary Key into a Non-cluster primary key, which would require to lock the table during the whole process, rebuild indexes, and re-create foreign key constraints, plus the possibility of ending up with a “Heap” table or choosing a new Cluster index (major change).
- Create a Unique Index on the same primary key, meaning having a duplicate index, but much faster to implement.
I chose to do the later, so after creating the unique key, I rebuild only foreign key constraint on the child table and the problem was gone, SQL Server was smart enough to choose the unique index instead of the Cluster index to build the foreign key. After that change all blocking problems between both tables were gone, we had to struggle with other patters of blocking processes but those were much simpler and easier to solve.
I hope you enjoied this experience that challenge my technical skills to the limit. Here is a script to reproduce the issue,
use master GO -- Create the test database and configure it as read commited snapshot IF (DB_ID('WeirdLockIssue') IS NOT NULL) BEGIN ALTER DATABASE WeirdLockIssue SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE WeirdLockIssue; END CREATE DATABASE WeirdLockIssue GO CREATE DATABASE WeirdLockIssue GO ALTER DATABASE WeirdLockIssue SET READ_COMMITTED_SNAPSHOT ON; GO USE WeirdLockIssue GO -- create test tables CREATE TABLE dbo.ParentTable (ParentTableId int not null identity(1,1) ,DateCreated datetime not null ,DateUpdated datetime null ,CONSTRAINT pk_ParentTable PRIMARY KEY (ParentTableId)) GO CREATE TABLE dbo.ChildTable (ChildTableId int not null identity(1,1) ,ParentTableId int not null ,DateCreated datetime not null ,DateUpdated datetime null ,CONSTRAINT pk_ChildTable PRIMARY KEY (ChildTableId) ,CONSTRAINT fk_ChildTable_ParentTableId FOREIGN KEY (ParentTableId) REFERENCES dbo.ParentTable (ParentTableId)) GO -- insert test record on parent INSERT INTO ParentTable (DateCreated) VALUES (GETDATE()) /* -- Open new session and execute the following (session 1) USE WeirdLockIssue GO BEGIN TRANSACTION UPDATE ParentTable SET DateUpdated = GETDATE() WHERE ParentTableId=1 */ /* -- Open new session and execute the following (session 2) use WeirdLockIssue go INSERT INTO dbo.ChildTable (ParentTableId ,DateCreated) VALUES (1,GETDATE()) */ -- to solve the issue, create a unique index on parent table CREATE UNIQUE INDEX uix_ParentTable_ParentTableId ON dbo.ParentTable (ParentTableId) -- rebuild foreign key constraint ALTER TABLE dbo.ChildTable DROP CONSTRAINT fk_ChildTable_ParentTableId GO ALTER TABLE dbo.ChildTable ADD CONSTRAINT fk_ChildTable_ParentTableId FOREIGN KEY (ParentTableId) REFERENCES dbo.ParentTable (ParentTableId) GO -- Query to confirm that the child table is aiming the unique key index select fk.name as ForeignKeyName, obj.name as ReferencedTable, ix.name as ReferencedIndex from sys.foreign_keys fk inner join sys.objects obj on fk.referenced_object_id = obj.object_id inner join sys.indexes ix on obj.object_id = ix.object_id and fk.key_index_id = ix.index_id where fk.parent_object_id = OBJECT_ID('dbo.ChildTable')