Sql-server – READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

isolation-levellockingsnapshot-isolationsql serversql-server-2008-r2

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem:

USE [sor]
GO

ALTER DATABASE [sor] SET ALLOW_SNAPSHOT_ISOLATION on;
ALTER DATABASE [sor] SET READ_COMMITTED_SNAPSHOT on;

CREATE TABLE [dbo].[test] 
(
    [name] [nvarchar](50) NOT NULL,
    [id] [int] NOT NULL,

    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED  ([id] ASC) 
)
GO

CREATE NONCLUSTERED INDEX [IX_test] 
   ON [dbo].[test] ([name] ASC)
GO

ALTER TABLE [dbo].[test] 
  ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([id] ASC)
GO

CREATE UNIQUE NONCLUSTERED INDEX [pk_key] 
   ON [dbo].[test] ([id] ASC) 
GO

My problem is that when I execute two or more sessions (transactions) concurrently I'm being locked by some database indexes. To simulate it I start one sqlcmd:

-- sqlcmd 1

begin tran s1
insert into dbo.test (id, name) values(1, 'bob');
go

and start another sqlcmd to be the next concurrent transaction:

-- sqlcmd 2
begin tran s2
insert into dbo.test (id, name) values(2, 'john');
go -- locking here!!!!

When I run the go inside the transaction named s2 I'm being locked.

In order to discover what is going on I run

SELECT * 
FROM sys.dm_exec_requests 
WHERE DB_NAME(database_id) = 'sor' AND blocking_session_id <> 0

Output:

session_id request_id  start_time              status         command          sql_handle                                                      statement_start_offset statement_end_offset plan_handle                                         database_id user_id     connection_id                        blocking_session_id wait_type   wait_time   last_wait_type   wait_resource                              open_transaction_count open_resultset_count transaction_id       context_info   percent_complete estimated_completion_time cpu_time    total_elapsed_time scheduler_id task_address       reads                writes   logical_reads  text_size   date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count            prev_error  nest_level  granted_query_memory executing_managed_code group_id    query_hash         query_plan_hash
---------- ----------- ----------------------- -------------- ---------------- --------------------------------------------------------------- ---------------------- -------------------- --------------------------------------------------- ----------- ----------- ------------------------------------ ------------------- ----------- ----------- ---------------- ------------------------------------------ ---------------------- -------------------- -------------------- -------------- ---------------- ------------------------- ----------- ------------------ ------------ ------------------ -------------------- -------- -------------- ----------- ---------- ----------------- ---------- ----------------- ------------- ------------- ------------ ---------- ----------------------- --------------------------- ------------ ----------------- -------------------- ----------- ----------- -------------------- ---------------------- ----------- ------------------ ------------------
74         0           2014-10-01 10:56:54.143 suspended      INSERT           0x0200000098A4BD0B49707895A7295A343EF775E7CF23BCF2              50                     -1                   0x0600170098A4BD0B4001C69D000000000000000000000000  23          1           E24BB9F3-D12A-44EC-944E-FDA206590705 73                  LCK_M_X     1762594     LCK_M_X          KEY: 23:72057594038910976 (020068e8b274)   3                      1                    243137590            0x             0                0                         0           1762596            1            0x000000018281E2C8 0                    0        4              4096        7          0                 0          1                 0             1             1            1          1                       2                           -1           0                 0                    0           0           0                    0                      2           0xF22D2FE93F4C59F0 0x354B4D3AA833139F

and running

SELECT 
   t1.resource_type, t1.resource_database_id, t1.resource_associated_entity_id, 
   t1.request_mode, t1.request_session_id, t2.blocking_session_id, 
   o1.name 'object name', o1.type_desc 'object descr', 
   p1.partition_id 'partition id', p1.rows 'partition/page rows', 
   a1.type_desc 'index descr', a1.container_id 'index/page container_id' 
FROM 
   sys.dm_tran_locks as t1 
INNER JOIN 
   sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address 
LEFT OUTER JOIN 
   sys.objects o1 on o1.object_id = t1.resource_associated_entity_id 
LEFT OUTER JOIN 
   sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id 
LEFT OUTER JOIN 
   sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

I have the following

resource_type    resource_database_id resource_associated_entity_id request_mode   request_session_id blocking_session_id object name   object descr  partition id  partition/page rows  index descr    index/page container_id
---------------- -------------------- ----------------------------- -------------- ------------------ ------------------- ------------- ------------- ------------- -------------------- -------------- -----------------------
KEY              23                   72057594038910976             X              74                 73                  NULL          NULL          NULL          NULL                 IN_ROW_DATA    72057594038255616

Once I use READ COMMITTED SNAPSHOT I ask you why SQL Server is locking the index? What can I do? I cannot delete the indexes! I need them.

Best Answer

RCSI applies only to reads. Writes take normal locks. See Understanding Row Versioning-Based Isolation Levels:

[RCSI] uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection.

So your expectation that under RCSI you cannot block is wrong. However, you have a valid point on why would those two inserts block each other? And the answer is... they don't. If you take the repro DDL from your post exactly as is, it errors:

ALTER TABLE [dbo].[test] 
  ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([id] ASC)
GO

Msg 1779, Level 16, State 0, Line 3
Table 'test' already has a primary key defined on it.

Not surprising, considering that the create table already adds a primary key. If I remove the offending ALTER TABLE, then I can successfully insert. SQL Server 2012 SP1. So please revise the post and make sure your code reproes the problem you describe.