T-SQL Tuesday #98 – Your Technical Challenges Conquered

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:

1. 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).
2. 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')