I'm in a big trouble with one database instance of a MS SQL Server 2008 R2! I have the structure bellow 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) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_test] ON [dbo].[test] (
[name] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [pk_key] ON [dbo].[test] (
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
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
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 COMMITED SNAPSHOT I ask you why the sql server is locking the index? what can I do? I cannot delete the indexes! I need them.
