Нарушение ограничения UNIQUE KEY для INSERT WHERE COUNT(*) = 0 в SQL Server 2005
Я вставляю в базу данных SQL из нескольких процессов. Вполне вероятно, что процессы иногда будут пытаться вставить дубликаты данных в таблицу. Я пытался написать запрос таким образом, чтобы обрабатывать дубликаты, но я все еще получаю:
System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.
Мой запрос выглядит примерно так:
INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0
Ограничение "UK1_MyConstraint" говорит, что в MyTable комбинация из 3 полей должна быть уникальной.
Мои вопросы:
- Почему это не работает?
- Какие изменения мне нужно сделать, чтобы исключить вероятность исключения из-за нарушения ограничения?
Обратите внимание, что я знаю, что есть другие подходы к решению исходной проблемы "ВСТАВИТЬ, если не существует", такие как (в итоге):
- Использование TRY CATCH
- IF NOT EXIST INSERT (внутри транзакции с сериализуемой изоляцией)
Должен ли я использовать один из подходов?
Редактировать 1 SQL для создания таблицы:
CREATE TABLE [dbo].[MyTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[FieldA] [bigint] NOT NULL,
[FieldB] [int] NOT NULL,
[FieldC] [char](3) NULL,
[FieldD] [float] NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED
(
[FieldA] ASC,
[FieldB] ASC,
[FieldC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
Изменить 2 Решение:
Просто чтобы обновить это - я решил использовать реализацию "JFDI", предложенную в связанном вопросе ( ссылка). Хотя мне все еще интересно, почему оригинальная реализация не работает.
3 ответа
Почему это не работает?
Я считаю, что стандартное поведение SQL Server - освобождать общие блокировки, как только они больше не нужны. Ваш подзапрос приведет к кратковременной общей (S) блокировке таблицы, которая будет выпущена, как только подзапрос завершится.
На данный момент ничто не мешает одновременной транзакции вставить ту самую строку, которую вы только что проверили.
Какие изменения мне нужно сделать, чтобы исключить вероятность исключения из-за нарушения ограничения?
Добавление HOLDLOCK Подсказка к вашему подзапросу заставит SQL Server удерживать блокировку до завершения транзакции. (В вашем случае это неявная транзакция.) HOLDLOCK подсказка эквивалентна SERIALIZABLE подсказка, которая сама по себе эквивалентна уровню изоляции сериализуемой транзакции, который вы упоминаете в своем списке "других подходов".
HOLDLOCK Одной подсказки было бы достаточно, чтобы сохранить блокировку S и предотвратить одновременную транзакцию от вставки строки, от которой вы защищаете. Однако вы, скорее всего, обнаружите, что ошибка нарушения вашего уникального ключа заменена взаимоблокировками, возникающими с той же частотой.
Если вы сохраняете только S-блокировку на столе, рассмотрите гонку между двумя одновременными попытками вставить одну и ту же строку, продолжая в режиме блокировки - оба преуспели в получении S-блокировки на столе, но ни один не может преуспеть в получении Exclusive (X) блокировка, необходимая для выполнения вставки.
К счастью, для этого точного сценария существует другой тип блокировки, называемый блокировкой Update (U). U-блокировка идентична S-блокировке со следующим отличием: хотя несколько S-блокировок могут удерживаться одновременно на одном и том же ресурсе, одновременно может удерживаться только одна U-блокировка. (Говорят иначе, хотя S-блокировки совместимы друг с другом (то есть могут сосуществовать без конфликта), U-блокировки не совместимы друг с другом, но могут сосуществовать вместе с S-блокировками; и далее по всему спектру эксклюзивные (X) блокировки не являются совместим с замками S или U)
Вы можете обновить неявную S-блокировку вашего подзапроса до U-блокировки, используя UPDLOCK намек.
Две одновременные попытки вставить одну и ту же строку в таблицу теперь будут сериализованы в начальном операторе выбора, поскольку это получает (и удерживает) блокировку U, которая несовместима с другой блокировкой U из попытки одновременной вставки.
Значения NULL
Отдельная проблема может возникнуть из-за того, что FieldC допускает значения NULL.
Если ANSI_NULLS включен (по умолчанию), то проверка на равенство FieldC=NULL вернет false, даже в случае, когда FieldC имеет значение NULL (вы должны использовать IS NULL оператор для проверки на ноль, когда ANSI_NULLS включен). Поскольку FieldC имеет значение null, ваша дублирующая проверка не будет работать при вставке значения NULL.
Чтобы правильно обрабатывать пустые значения, вам нужно изменить подзапрос EXISTS, чтобы использовать IS NULL оператор, а не = когда значение NULL вставляется. (Или вы можете изменить таблицу, чтобы запретить значения NULL во всех соответствующих столбцах.)
Электронная документация по SQL Server
RE: "Мне все еще интересно, почему оригинальная реализация не работает".
Почему это работает?
Что нужно для предотвращения чередования двух одновременных транзакций следующим образом?
Tran A Tran B
---------------------------------------------
SELECT COUNT(*)...
SELECT COUNT(*)...
INSERT ....
INSERT... (duplicate key violation).
Единственный раз, когда будут приняты конфликтующие блокировки, находится на Insert этап.
Чтобы увидеть это в SQL Profiler
Создать табличный скрипт
create table MyTable
(
FieldA int NOT NULL,
FieldB int NOT NULL,
FieldC int NOT NULL
)
create unique nonclustered index ix on MyTable(FieldA, FieldB, FieldC)
Затем вставьте ниже в два разных окна SSMS. Запишите спиды соединений (x и y) и настройте трассировку SQL Profiler, фиксирующую события блокировки и сообщения об ошибках пользователя. Примените фильтры spid=x или y и severity = 0, а затем выполните оба сценария.
Вставить скрипт
DECLARE @FieldA INT, @FieldB INT, @FieldC INT
SET NOCOUNT ON
SET CONTEXT_INFO 0x696E736572742074657374
BEGIN TRY
WHILE 1=1
BEGIN
SET @FieldA=( (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 24 * 60 * 60 * 300)
SET @FieldB = @FieldA
SET @FieldC = @FieldA
RAISERROR('beginning insert',0,1) WITH NOWAIT
INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA=@FieldA, FieldB=@FieldB, FieldC=@FieldC
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA=@FieldA AND FieldB=@FieldB AND FieldC=@FieldC ) = 0
END
END TRY
BEGIN CATCH
DECLARE @message VARCHAR(500)
SELECT @message = 'in catch block ' + ERROR_MESSAGE()
RAISERROR(@message,0,1) WITH NOWAIT
DECLARE @killspid VARCHAR(10)
SELECT @killspid = 'kill ' +CAST(SPID AS VARCHAR(4)) FROM sys.sysprocesses WHERE SPID!=@@SPID AND CONTEXT_INFO = (SELECT CONTEXT_INFO FROM sys.sysprocesses WHERE SPID=@@SPID)
EXEC ( @killspid )
END CATCH
Сверху головы у меня такое чувство, что один или несколько из этих столбцов принимают нули. Я хотел бы видеть оператор create для таблицы, включая ограничение.