У меня есть таблица ImportSourceMetadata, которую я использую для управления пакетным процессом импорта. Он содержит столбец PK SourceId и столбец данных LastCheckpoint. Пакетный процесс импорта считывает LastCheckpoint для данного SourceId, выполняет некоторую логику (в других таблицах), затем обновляет LastCheckpoint для этого SourceId или вставляет его, если он еще не существует.
Несколько экземпляров процесса запускаются одновременно, обычно с дизъюнктным SourceIds, и для таких случаев мне нужна высокая степень параллелизма. Однако может случиться так, что два процесса будут запущены для одного и того же SourceId; в этом случае мне нужно, чтобы экземпляры блокировали друг друга.
Поэтому мой код выглядит следующим образом:
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId = 'Source'
-- Perform some processing
-- UPSERT: if the SELECT above yielded no value, then
INSERT INTO ImportSourceMetadata(SourceId, LastCheckpoint) VALUES ('Source', '2013-12-21')
-- otherwise, we'd do this: UPDATE ImportSourceMetadata SET LastCheckpoint = '2013-12-21' WHERE SourceId = 'Source'
COMMIT TRAN
Я использую транзакцию для достижения атомарности, но могу использовать только уровень изоляции READ COMMITTED (из-за требований параллелизма в блоке «Выполнить некоторую обработку»). Поэтому (и чтобы избежать взаимоблокировок) я включаю подсказку UPDLOCK с оператором SELECT, чтобы получить «критическую секцию», параметризованную для SourceIdvalue.
В большинстве случаев это работает довольно хорошо, но мне удалось вызвать ошибки нарушения первичного ключа с помощью оператора INSERT при запуске множества параллельных процессов для одного и того же SourceId с пустой базой данных. Однако я не могу достоверно воспроизвести это и не понимаю, почему это не работает.
Я нашел подсказки в Интернете (например, здесь и здесь, в комментарии), что мне нужно указать WITH (UPDLOCK,HOLDLOCK) (соответственно WITH (UPDLOCK,SERIALIZABLE)), а не просто взяв UPDLOCK на SELECT, но я действительно не понимаю, почему это так. Документы MSDN говорят,
UPDLOCK
Указывает, что блокировки обновления должны быть установлены и удерживаться до завершения транзакции.
Блокировки обновления, которая устанавливается и удерживается до завершения транзакции, должно быть достаточно, чтобы заблокировать последующую вставку, и фактически, когда я пробую ее в SQL Server Management Studio, она действительно блокирует мою вставку. Однако в некоторых редких случаях он внезапно перестает работать.
Итак, почему именно UPDLOCK недостаточно, и почему его достаточно в 99% моих тестовых прогонов (и при их симуляции в SQL Server Management Studio)?
Обновление: теперь я обнаружил, что могу надежно воспроизвести неблокирующее поведение, выполнив приведенный выше код в двух разных окнах SQL Server Management Studio одновременно вплоть до непосредственно перед INSERT, но только в первый раз после создания базы данных. После этого (даже несмотря на то, что я удалил содержимое таблицы ImportSourceMetadata), SELECT WITH (UPDLOCK) действительно заблокируется, и код больше не дает сбоев. Действительно, в sys.dm_tran_locks я вижу взятие U-блокировки, хотя строки не существует при последующих запусках теста, но не при первом запуске после создания таблицы.
Это полный пример, показывающий разницу в блокировках между «ново созданной таблицей» и «старой таблицей»:
DROP TABLE ImportSourceMetadata
CREATE TABLE ImportSourceMetadata(SourceId nvarchar(50) PRIMARY KEY, LastCheckpoint datetime)
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId='Source'
SELECT *
FROM sys.dm_tran_locks l
JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id JOIN sys.objects o
ON p.object_id = o.object_id
INSERT INTO ImportSourceMetadata VALUES('Source', '2013-12-21')
ROLLBACK TRAN
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId='Source'
SELECT *
FROM sys.dm_tran_locks l
JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id JOIN sys.objects o
ON p.object_id = o.object_id
ROLLBACK TRAN
В моей системе (с SQL Server 2012) первый запрос не показывает блокировки на ImportSourceMetadata, а второй запрос показывает блокировку KEY на ImportSourceMetadata.
Другими словами, HOLDLOCK действительно требуется, но только если таблица была только что создана. Почему это?
