SQL Server и случай с параллельным изменением данных.

Один мой коллега подошел с вопросом, суть которого такова :
Есть таблица, в которую один процесс производит вставку записей. В то же время из этой таблицы второй процесс удаляет данные. И пока не завершится вставка записей, удаление не происходит, т.е. второй процесс ждет окончания первого. Записи не перекрываются; удаление происходит по условию, под которое не попадают вставляемые записи. Коллега задает вопрос, почему так происходит и как с этим бороться.

Индекса по условию удаления на таблице нет.

Смоделируем пример :
USE [AdventureWorks]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE TestTable
GO

CREATE TABLE dbo.TestTable
(
Name VARCHAR(100),
CreatedDateTime DATETIME
)
GO

-- эти записи будут удаляться из другой сессии
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 1', '20110401'), ('Record 2', '20110401'), ('Record 3', '20110401')
GO

-- Запрос 1. Вставка записей в первой сессии
BEGIN TRAN
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 4', '20110402'), ('Record 5', '20110402'), ('Record 6', '20110402')

--COMMIT TRAN

--Запрос 2. Удаление записей во второй сессии
DELETE TestTable WHERE CreatedDateTime = '20110401'

Пока не закоммитится инсерт из первого запроса, второй запрос на удаление будет ждать освобождения залоченной страницы.
На рассматриваемой таблице нет индекса по условию удаления, поэтому было рекомендовано создать индекс. Это решило проблему блокировки, т.к. теперь движок базы данных точно знает, где находятся удаляемые записи, а не производит скан таблицы, натыкаясь на залоченные ресурсы. Однако, моего привередливого коллегу это решение удовлетворило лишь частично, т.к. в его планы не входило создание индекса на таблице.

Проблему решает режим версионности ALLOW_SNAPSHOT_ISOLATION.

-- удалим все из тестовой таблицы
DELETE TestTable
GO

-- включаем SNAPSHOT
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- эти записи будут удаляться из другой сессии
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 1', '20110401'), ('Record 2', '20110401'), ('Record 3', '20110401')
GO

-- Запрос 1. Вставка записей в первой сессии
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 4', '20110402'), ('Record 5', '20110402'), ('Record 6', '20110402')

--COMMIT TRAN

--Запрос 2. Удаление записей во второй сессии
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DELETE TestTable WHERE CreatedDateTime = '20110401'

Записи будут удалены из снимка предыдущей версии данных, сохраненных до начала транзакции первого запроса. После этого можно закоммитить первый запрос. В таблице будут находится записи, вставленные в первом запросе. Записи «Record 1», «Record 2», «Record 3» будут удалены.
Надо заметить что это работает только при режиме версионности ALLOW_SNAPSHOT_ISOLATION, но не READ_COMMITTED_SNAPSHOT.