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:
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:
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.