Key-Range Locks During Cascade Updates and Deletes Despite a Transaction Isolation Level of Read Committed

We were seeing some unexpected locking in some of our SQL statements and couldn’t find any documentation explaining the behavior.  After some back-and-forth with someone on Microsoft’s support team, we got an explanation from a member of the SQL Server team.

http://blogs.msdn.com/conor_cunningham_msft/archive/2009/03/13/conor-vs-isolation-level-upgrade-on-update-delete-cascading-ri.aspx 

In short, we were seeing key-range locks on cascade updates and deletes despite the fact that the isolation level for our transactions was set to read committed – not serializable, which is when you would expect to see key-range locks.  For example, consider the following:

CREATE TABLE Foo (
  FooId INT NOT NULL PRIMARY KEY
)

CREATE TABLE Bar (
  FooId INT NOT NULL,
  BarId INT NOT NULL
)

ALTER TABLE Bar
ADD CONSTRAINT PK_Bar
PRIMARY KEY (FooId, BarId)

ALTER TABLE Bar
ADD CONSTRAINT FK_Bar_Foo
FOREIGN KEY (FooId) REFERENCES Foo(FooId)
ON DELETE CASCADE

INSERT INTO Foo VALUES (1)
INSERT INTO Bar VALUES (1, 1)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
  DELETE FROM Foo WHERE FooId = 1
COMMIT TRAN

When the delete from the Foo table occurs and triggers the cascade delete from the Bar table, key-range locking occurs on the PK_Bar primary key, even when the delete statement is within a transaction having an isolation level explicitly set to read committed.

The reason the key-range locks occur is because SQL Server implicitly escalates some of the locks in the cascade delete to key-range locks in order to block any other operation that might violate the foreign key property – for instance, it prevents another operation from inserting a foreign key value while the delete is occurring.  Unlike typical key-range locks, these locks are only held for the duration of the statement, not until the completion of the entire transaction.

Flattening Table Rows Into A Column (Aggregate Concatenation)

I was trying to flatten rows from a table into a single column, with each value separated by a comma – something known as aggregate concatenation.  The way I’ve done stuff like this in the past is something like the following:

SELECT
  m.PersonGuid,
  m.ColleagueGuid,
  MAX(CASE WHEN m.Seq = 1 THEN m.Source ELSE '' END) + 
    MAX(CASE WHEN m.Seq = 2 THEN ',' + m.Source ELSE '' END) + 
    MAX(CASE WHEN m.Seq = 3 THEN ',' + m.Source ELSE '' END)
FROM
  (SELECT
    s1.PersonGuid,
    s1.ColleagueGuid,
    s1.Source,
    COUNT(s2.Source) Seq
  FROM
    (SELECT 
      PersonGuid,
      ColleagueGuid,
      Source
    FROM 
      Contact_Person_Colleague_Source
    ) s1,
    (SELECT 
      PersonGuid,
      ColleagueGuid,
      Source
    FROM 
      Contact_Person_Colleague_Source
    ) s2
  WHERE
    s1.PersonGuid = s2.PersonGuid
  AND
    s2.ColleagueGuid = s2.ColleagueGuid
  AND
    s1.Source >= s2.Source
  GROUP BY
    s1.PersonGuid,
    s1.ColleagueGuid,
    s1.Source
  ) m
GROUP BY
  m.PersonGuid,
  m.ColleagueGuid

However, I’ve only used the above statement when doing a quick, on-the-fly query; I’ve never used it in production code.  Thus I wanted to find some other ways that were more appropriate for production code – ways that were possibly cleaner, easier to read, and more performant. 

One thing I tried was moving the flattening into the following function:

CREATE FUNCTION [dbo].[FlattenColleagueSources] ()
RETURNS NVARCHAR(500)
AS
BEGIN
  DECLARE @sources NVARCHAR(500)
  SET @sources = NULL
  
  SELECT @sources = COALESCE(@sources + ',','') + s.[Source]
  FROM 
    (SELECT [Source] 
    FROM [dbo].[Contact_Person_Colleague_Source]) AS s

  RETURN @sources
END

I saw this and this, but it doesn’t look like they apply because I don’t use ORDER BY (yet), let alone have any expressions or operators in the ORDER BY.

I next tried the following query, which uses the FOR XML clause to do the flattening (and a STUFF function to remove the leading comma):

SELECT
  cpc.[PersonGuid],
  cpc.[ColleagueGuid],
  STUFF(
    (SELECT 
      ',' + cpcs.[Source] 
    FROM 
      [dbo].[Contact_Person_Colleague_Source] cpcs
    WHERE
      cpcs.[PersonGuid] = cpc.[PersonGuid]
    AND
      cpcs.[ColleagueGuid] = cpc.[ColleagueGuid]
    FOR XML PATH('')), 1, 1, ''
  ) AS [Sources]
FROM
  [Contact_Person_Colleague] AS cpc 

Then I found an interesting article that lists a bunch of ways to concatenate table rows.  Whoa.

This may change after I’ve done some performance testing, but for now I’m using the query that utilizes the FOR XML clause.  It’s always fascinating, though, to see the myriad of ways to pull the same information from a database.  And of course, let me know of any other ways to do aggregate concatenation.