IS Lock in RCSI Enabled Database

Foreword 🔗︎

Because I like to help people with SQL problems, I’ll be starting a new series of posts where I document some of the more interesting questions. But this time I’ve helped myself. This is the problem I have been investigating.

The problem 🔗︎

I was analyzing a deadlock graph and there was a mystery lock of type IS (Intent Shared). That was weird by itself because the database has Read Committed Snapshot Isolation (RCSI) enabled, which is the Optimistic Concurrency model that shouldn’t take shared locks. All the statements were contained in this database. Also, the locked table was seemingly unrelated to anything that has been going on in the deadlock report.

The statement which took this block was a simple UPDATE query. So I have checked the usual suspects:

• Is it a table or a View?
• Does it have any Triggers?
• Is there a Scalar function (in a computed column or constraint)?
• Is there a Foreign key to the unrelated table?

It was none of those things. Then I’ve looked at what do those two tables have in common and the only thing I came up with was an Indexed View which turned out to be the problem.

Repro 🔗︎

Let’s create a brand new database.

CREATE DATABASE TestLock

Inside of this database, we’ll create two tables and fill them with sample data

CREATE TABLE dbo.MainTable
(
      Id INT PRIMARY KEY
    , PrivateColumn CHAR(1) NOT NULL
    , ColumnInView CHAR(1) NOT NULL
)

CREATE TABLE dbo.UnrelatedTable
(
      Id INT PRIMARY KEY
    , RandomColumn CHAR(1) NOT NULL
)

INSERT INTO dbo.MainTable
(Id, PrivateColumn, ColumnInView)
VALUES
  (1, 'P', 'V')
, (2, 'P', 'V')

INSERT INTO dbo.UnrelatedTable
(Id, RandomColumn)

VALUES
  (1, 'R')
, (2, 'R')

And the last object will be an Indexed View that combines two tables, but only exposes the ColumnInView from the MainTable.

SET NUMERIC_ROUNDABORT OFF;

SET
    ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL
    , ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE OR ALTER VIEW dbo.IndexedView
WITH SCHEMABINDING
AS
SELECT
    ut.Id
  , ut.RandomColumn
  , mt.ColumnInView
  , COUNT_BIG(*) AS cnt /* indexed view requirement */
FROM
    dbo.UnrelatedTable AS ut
    JOIN dbo.MainTable AS mt
        ON mt.Id = ut.Id
GROUP BY
    ut.Id
    , ut.RandomColumn
    , mt.ColumnInView
GO

CREATE UNIQUE CLUSTERED INDEX CX_IndexedView ON dbo.IndexedView (Id)

Then we open a new session - let’s call it a monitoring session that we’ll watch with the Extended Events (XE). This session will be reused so take note of the session Id (SPID) and do not close.

You can find the session id either by running this command SELECT @@SPID, in the session tab info or the status bar.

We confirm that the database doesn’t have an RCSI enabled.

SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TestLock'

Then we create the Extended Events to monitor the locks. This is the scripted out definition. Change the SPID to match the SPID of your monitoring session.

CREATE EVENT SESSION [LockAcquired] ON SERVER
ADD EVENT sqlserver.lock_acquired
(
    SET collect_resource_description = 1
    WHERE
    (
        [package0].[equal_uint64]([sqlserver].[session_id],(52) /* <--- change to your SPID */)
        AND [resource_type] = 'OBJECT'
        AND [mode] <> 'SCH_S'
    )
)
GO
ALTER EVENT SESSION [LockAcquired] ON SERVER STATE = start;

I’m only interested in the Object level locks. I also filter out the Schema stability locks. No target is required, Watch Live data will do.

It’s also useful to take note of the object Ids of our tables and the Indexed View. These are mine:

SELECT o.object_id, o.name, o.type
FROM sys.objects AS o
WHERE
    o.is_ms_shipped = 0
    AND o.type IN ('U', 'V')

Now, let’s run a simple select from the MainTable in our monitoring session while we have the XE session opened in another window.

SELECT * FROM dbo.MainTable

My XE output shows this:

There are two objects with IS locks - the 581577110 matches the MainTable. You might be wondering what is the other lock according to SELECT OBJECT_NAME(245575913)- it’s a plan_persist_context_settings.

We can clear the data from the XE Live Data (via the Extended Event\Clear Data menu), enable RCSI and repeat our experiment.

USE [master]
ALTER DATABASE TestLock SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestLock SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE TestLock SET MULTI_USER;

Check again the RCSI status with the query we used previously. Run again the select from the MainTable in our monitoring session - the plan_persist_context_settings remains, but the MainTable IS lock is gone.

Enter the Indexed view 🔗︎

First I’ll update the PrivateColumn of the MainTable and again watch the locks in the XE session.

UPDATE dbo.MainTable
SET MainTable.PrivateColumn = 'A'

I’ll disregard the plan_persist_context_settings. Then we have an X lock on the IndexedView and IX on the MainTable.

Now let’s update the ColumnInView which is also referenced in the IndexedView.

UPDATE dbo.MainTable
SET MainTable.ColumnInView = 'B'

Apart from the same locks we got last time, there is also an IX lock on the IndexedView but more interestingly an IS lock on the UnrelatedTable.

This confirms my theory that the Indexed View is the culprit of the deadlock graph from earlier. Indexed Views might add performance for reading but they hurt the concurrency and not even the RCSI can save it.

Update (2021/12/10) 🔗︎

It has been brought to my attention by Paul White that this post might seem like I’m describing a bug or unintended behaviour.

To amend, I’m passing on this great post by Erik Darling that covers it better and more in-depth: Locks Taken During Indexed View Modifications
Be sure to click the linked references on that post.

The point I was trying to make is that usually, other “hidden” logic (Triggers, Scalar functions in a table definition, Foreign keys) are tied directly to the table.
But the Indexed view is truly hidden on the side-lines.

So here’s a quick query to find an Indexed view and which tables/columns it’s referencing:

SELECT
    v.object_id
    , SCHEMA_NAME(v.schema_id) AS schemaName
    , v.name AS viewName
    , i.name AS indexName
    , dsre.referenced_schema_name AS refSchema
    , dsre.referenced_entity_name AS refObject
    , dsre.referenced_minor_name AS refColumn
FROM sys.views AS v
JOIN sys.indexes AS i
    ON i.object_id = v.object_id
    AND i.index_id = 1 /* Clustered */
CROSS APPLY sys.dm_sql_referenced_entities
(
    CONCAT
    (
        OBJECT_SCHEMA_NAME(v.object_id)
        , '.'
        , v.name
    )
    , 'OBJECT'
) AS dsre
--WHERE v.name LIKE '%%' /* Filter a specific view */