Обеспечение однократного добавления в таблицу SQL

Question

У меня есть таблица DocumentJob, которая содержит задания, которые нужно выполнить с данным документом. У каждой строки в таблице есть jobId, documentId и jobstatus.

Несколько потоков / процессов будут пытаться добавить в эту таблицу в любой момент времени (используя код, приведенный ниже)

begin tran
if exists 
(
    select 1 from DocumentJob 
    where DocumentId = @inDocumentId 
    and Status in ('Running', 'New')
)
    throw 50001, 'New or Active Job for Document is already present', 1

insert into DocumentJob (DocumentId, Status) values(DocumentId, 'New')
select @JobId = scope_identity();
commit;

Для данного идентификатора документа - я хотел бы добавить новую работу, только если нет другой работы для документа, который работает или новый. Придерживается ли следующий фрагмент кода вышеупомянутого требования или будут некоторые условия, когда вышеуказанное условие может быть нарушено?

Моя цель - понять, будет ли таблица должным образом заблокирована и т. Д. При одновременном вызове вышеупомянутой процедуры.

Answer 1

[1] Первое, что я хотел бы сделать, это создать уникальный отфильтрованный индекс следующим образом:

CREATE UNIQUE NONCLUSTERED INDEX IUF_DocumentJob_DocumentId
ON dbo.DocumentJob (DocumentId)
--INCLUDE (...)
WHERE Status in ('Running', 'New')

Этот уникальный индекс гарантирует, что dbo.DocumentJob стол будет иметь уникальный DocumentIdимеет один из следующих статусов: 'Running' или же 'New',

Этого достаточно для того, чтобы избежать дублирования dbo.DocumentJobs когда статус 'Running' или же 'New',

[2] После [1] исходный код, включенный в текущий вопрос, можно заменить простым

INSERT INTO dbo.DocumentJob (DocumentId, Status) VALUES (@DocumentId, 'New')

Для любого {@DocumentId значение и Running или же New статус} только первый INSERT (или же UPDATE) выполнение будет успешным, а следующее выполнение будет неудачным.

Примечание: я бы инкапсулировал этот код в транзакции (см. SET XACT_ABORT ON) а также в пределах TRY ... CATCH блок (эта тема не представлена ​​в этом ответе).

[3] Для тестирования я бы использовал инструмент ostress.exe от Microsoft ( http://sqlmag.com/t-sql/2-tools-keep-sql-server-tuned) и / или следующий подход

[3.1] В SSMS откройте окно нового запроса ([Новый запрос]) и выполните следующий код

BEGIN TRAN
    -- Replace 1234 with a new DocumentId
    INSERT INTO dbo.DocumentJob (DocumentId, Status) VALUES (1234, 'New')
-- COMMIT

[3.2] В SSMS откройте другое окно запроса ([Новый запрос]) и выполните следующий код

BEGIN TRAN
    -- Use the same DocumentId
    INSERT INTO dbo.DocumentJob (DocumentId, Status) VALUES (1234, 'New')
COMMIT

[3.3] Первый INSERT удастся, а второй не получится.

Примечание: если у вас есть какие-либо вопросы (относительно этого ответа), пожалуйста, задавайте. Примечание 2: не забудьте выполнить COMMIT (или ROLLBACK сначала Tx).