I recently came across at situation in SQL Server where one process was blocking another and there seemed to be absolutely no link between the two tables in question. It took a little while to work out what was causing the blocking but I’ve managed to recreate what I found with a more simple example.

We’d identified an issue where a MERGE statement into a table was now taking a very long time to complete but the part I couldn’t immediately understand was why this long running merge statement was blocking an update on another seemingly unrelated table.

Here’s something similar to what I found using sp_whoisactive

The blocking query, session_id = 54, (this is equivalent to the MERGE statement I was seeing on my live environment) is as follows

UPDATE dbo.Address
SET PostCode = 'AA11AA'
WHERE AddressID = 1;

and the query being blocked, session_id = 56, is

UPDATE dbo.Person
SET Name = 'Simon'
WHERE PersonID = 1;

In my database there is no direct link between the Person and Address tables, e.g. no direct foreign keys from one table to another, and there are no explicit transactions being used — so there isn’t an update to the Person table in the same transaction as the update to the Address table, each will just commit once completed.

The block was caused because there was an indexed view using both the tables in the two transactions.

The issue in our code has since been fixed so the MERGE statement runs very quickly now so the block doesn’t last long but it’s certainly something I will be more wary of when using indexed views. For example, if you need to run a large update on a table within an indexed view, such as a backfill of data, you might inadvertently block writing of data to one or more other tables within that same indexed view.

If it’s not obvious why SQL Server has to do this, below are details of what is actually happening during the block.

To replicate the issue I ran the following code to create a few tables and put an indexed view over the top of them. This is just something simple that links people to addresses in a many-to-many relationship.

CREATE TABLE dbo.Person
( 
  PersonID INT CONSTRAINT Person_PersonID PRIMARY KEY NOT NULL IDENTITY(1,1), 
  Name VARCHAR(100), 
  DOB DATETIME2(0)
);
GO
CREATE TABLE dbo.AddressType
( 
  AddressTypeID INT CONSTRAINT AddressType_AddressTypeID PRIMARY KEY NOT NULL IDENTITY(1,1), 
  AddressType VARCHAR(100)
);
GO
CREATE TABLE dbo.Address
(
  AddressID INT CONSTRAINT Address_AddressID PRIMARY KEY NOT NULL IDENTITY(1,1), 
  AddressLine1 VARCHAR(100), 
  AddressLine2 VARCHAR(100), 
  PostCode VARCHAR(10), 
  AddressTypeID INT CONSTRAINT FK_Address_AddressTypeID FOREIGN KEY REFERENCES dbo.AddressType (AddressTypeID)
);
GO
CREATE TABLE dbo.PersonAddress
(
  PersonID INT CONSTRAINT FK_PersonAddress_PersonID FOREIGN KEY REFERENCES dbo.Person (PersonID), 
  AddressID INT CONSTRAINT FK_PersonAddress_AddressID FOREIGN KEY REFERENCES dbo.Address (AddressID)
);
GO
CREATE VIEW dbo.PeopleAddresses
WITH SCHEMABINDING
AS
SELECT P.PersonID, P.Name, P.DOB, A.AddressLine1, A.PostCode, T.AddressType
FROM dbo.PersonAddress PA
JOIN dbo.Person P ON P.PersonID = PA.PersonID
JOIN dbo.Address A ON A.AddressID = PA.AddressID
JOIN dbo.AddressType T ON T.AddressTypeID = A.AddressTypeID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_PeopleAddresses ON dbo.PeopleAddresses (PersonID, Name, DOB, AddressLine1, PostCode, AddressType);
GO

I’ve then put one row in each of the three main tables as follows

INSERT INTO dbo.AddressType (AddressType)
VALUES ('Home'), ('Work');
GO
INSERT INTO dbo.Address (AddressLine1, AddressLine2, PostCode, AddressTypeID)
VALUES ('AddressLine1', 'AddressLine2', 'PostCode', 1);
GO
INSERT INTO dbo.Person (Name, DOB)
VALUES ('My Name', '20000101');
GO
INSERT INTO dbo.PersonAddress (AddressID, PersonID)
SELECT PersonID, PersonID
FROM dbo.Person;
GO

Next I’m going to update the PostCode in the Address table for our single row. To mimic a long running update I am actually explicitly beginning a transaction.

BEGIN TRAN
UPDATE dbo.Address
SET PostCode = 'AA11AA'
WHERE AddressID = 1;

As this transaction is still active we can check which locks have been taken using the following query which gives the following results (the SPID of my query is 54).

SELECT
  ISNULL(OBJECT_NAME(p.object_id),o.name) AS ObjectName, 
  resource_type, 
  resource_subtype, 
  resource_description, 
  resource_associated_entity_id, 
  request_mode, 
  request_type, 
  request_status
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
LEFT JOIN sys.objects o ON o.object_id = l.resource_associated_entity_id
WHERE request_session_id = 54
ORDER BY resource_type, ObjectName

As expected we’ve got a shared lock (S) on the database and the second row is the exclusive lock (X) on the row we’re updating in the Address table. This lock also means we get intent exclusive locks (IX) on the page and the table. SQL Server takes these intent locks for two reasons: firstly, in case the amount of locks taken on individual rows breaches the threshold and the lock gets escalated to the table or page — the intent lock stops the escalation being blocked; and secondly, for performance reasons — if another process tries to update the table it would have to check every row for existing locks, but the intent lock tells this process there is a lock somewhere below, so it has to wait. For more details on locking, blocking and isolation levels see the links below.

As well as the expected locks on the Address table we’ve also got the X and IX locks on the PeopleAddresses indexed view which means that we would be blocked reading from PeopleAddresses (unless using the read uncommitted isolation level).

So if we now try and update the Person table in a different process as follows

UPDATE dbo.Person
SET Name = 'Simon'
WHERE PersonID = 1;

this query just hangs and we see the same blocking using whoisactive shown at the top of this post.

This is the locks that are held for the hanging query for the update on the Person table

An exclusive lock (X) has been granted on the row in the Person table but the process is also requesting a shared lock (S) on the row in the Address table. This row already has the X lock from SPID 54 so the Person update is blocked.

In this example we had one row in Address and one in Person and they were connected using the PersonAddress table. But what would happen if we tried to update a Person record that isn’t linked to the Address?

Let’s add a new Person, a new Address and link them together.

INSERT INTO dbo.Address (AddressLine1, AddressLine2, PostCode, AddressTypeID)
VALUES ('AddressLine1', 'AddressLine2', 'PostCode', 2);
GO
INSERT INTO dbo.Person (Name, DOB)
VALUES ('My Name', '20000101');
GO
INSERT INTO dbo.PersonAddress (AddressID, PersonID)
VALUES (2,2);
GO

So now let’s mimic the long running lock on the Address table for the first address again

UPDATE dbo.Address
SET PostCode = 'AA11AA'
WHERE AddressID = 1;

and then try and update the Person record for the second person row

UPDATE dbo.Person
SET Name = 'Simon'
WHERE PersonID = 2;

This works without any blocking because there is no lock directly on the Person table and the corresponding row in the indexed view is not locked.

If we run both UPDATE statements within explicit transactions we can see which locks are taken. SPID 54 is the Address update and 56 is the Person

As you can see there are no two same resources being locked across the two SPIDs with the exception of the IX on the PeopleAddresses PAGE (1:309096). However, this does not block as IX locks are compatible with each other.

This is just showing what happens when row level locks are taken but how does lock escalation affect things? Let’s insert lots more data and see

INSERT INTO dbo.Address (AddressLine1, AddressLine2, PostCode, AddressTypeID)
SELECT AddressLine1, AddressLine2, PostCode, AddressTypeID
FROM dbo.Address;
GO 20
INSERT INTO dbo.Person (Name, DOB)
SELECT Name, DOB
FROM dbo.Person;
GO 20
INSERT INTO dbo.PersonAddress (AddressID, PersonID)
SELECT PersonID, PersonID
FROM dbo.Person
WHERE PersonID > 2

This means we’ve now got 2,097,152 rows in each table

Now let’s try similar queries to before but updating 100,000 rows in the Address table in the first query

UPDATE dbo.Address
SET PostCode = 'AA11AA'
WHERE AddressID <= 100000;

So many row level locks were taken for this update that SQL Server has decided it’s better to escalate the lock to the whole table. So rather than exclusive (X) locks on the rows (KEY) we now have exclusive locks on both the Address table and PeopleAddresses indexed view. So now an update on any single row in the Person table, even one not linked to one of the 100,000 Address rows being updated, is blocked. For example, the following would be blocked.

UPDATE dbo.Person
SET Name = 'Simon'
WHERE PersonID = 2000000;

So finally, what happens if the first query is updating a column that is not included in the select list in the indexed view but is part of one of the tables in the view.

I purposely left out the Address2 column from the Address table in the PeopleAddresses indexed view.

If the first update just updates this Address2 column

UPDATE dbo.Address
SET AddressLine2 = 'New Address 1'
WHERE AddressID = 1;

no lock is placed on the PeopleAddresses indexed view at all

This means there are only locks on the Address table itself and any updates to the Person table would not be blocked.