Search code examples
sqlsql-serversqltransaction

Ensuring single add into SQL table

I have a DocumentJob table that contains jobs to be performed against a given document. Each row in the table has jobId, documentId and jobstatus.

Multiple threads / processes would be attempting to add to this table at any given time (using the code given below)

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;

For a given document Id - I would like to add a new job only if there is no other job for the document that is either running or new. Does the following code snippet take care of the above requirement or would there be some conditions where the above condition can be violated?

My objective is to understand if the table would be properly locked etc. when the above proc is called simultaneously.

Solution

  • [1] The first thing I would do is to create an unique filtered index thus:

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

    This unique index guarantees that dbo.DocumentJob table'll have an unique DocumentIds having one of following status: 'Running' or 'New'.

    This is enough in order to prevent duplicated dbo.DocumentJobs when status is 'Running' or 'New'.

    [2] After [1], the source code included in current question could be replaced by a simple

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

    For any {@DocumentId value and Running or New status} only the first INSERT (or UPDATE) execution'll succeed and next executions will fail.

    Note: I would encapsulate this code within transaction (see SET XACT_ABORT ON) and also within TRY ... CATCH block (this topic is not presented in this answer).

    [3] In order to test I would use ostress.exe tool from Microsoft (http://sqlmag.com/t-sql/2-tools-keep-sql-server-tuned) and / or following approach

    [3.1] Within SSMS open a new query ([New query]) window and execute following code

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

    [3.2] Within SSMS open another query ([New query]) window and execute following code

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

    [3.3] First INSERT will succeed and the second will fail.

    Note: If you have any questions (regarding this answer) please ask. Note 2: Don't forget to COMMIT (or ROLLBACK first Tx).