IS Lock in RCSI Enabled Database

Page content

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.

Deadlock graph

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 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')

Object names and their respective IDs

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:

Locks taken on MainTable and plan_persist_context_settings

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'

Only relevant locks are taken

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'

IS lock is taken on an UnrelatedTable

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.