sqlsql-serverthread-safety

Inserting lines with common identifier in a thread-safe way

I created an application that dynamically creates SQL code.
This application is supposed to group some production orders that are identified by their barcode and that are consolidated into one pick order.

To achieve that I need to write to a SQL Server database:

  1. A common identifier
  2. Each corresponding barcode
  3. The amount of items to be produced

Usually I would create an identity column in SQL Server and query the newly inserted identity with select scope_identity() afterwards but I need to use my identifier multiple times.

This is the SQL code my application currenty generates:

set xact_abort on 
BEGIN TRAN 

declare @MultiOrderReference as integer
set @MultiOrderReference = (select max(Reference) from PickReference ) + 1
insert into PickReference (Reference, Barcode, Amount) values (@MultiOrderReference, '0068518000', 1)
insert into PickReference (Reference, Barcode, Amount) values (@MultiOrderReference, '0068548000', 4)
insert into PickReference (Reference, Barcode, Amount) values (@MultiOrderReference, '0068550000', 8)

select @MultiOrderReference as Reference
commit tran

This inserts the lines with a common identifier and gives the identifier back to the application afterwards.

This will work most of the times and considering these are very small transactions (fast) it will probably always work but to my understanding it is not Thread-Safe. So if another user runs the application at the exact same time I guess two application instances can use the same common identifier in theory.
Is there a way to prevent that? Or am I wrong and this is in fact Thread-Safe since I'm using "begin tran -> commit tran"?

Solution

  • Looks like you should just use a SEQUENCE.

    CREATE SEQUENCE dbo.PickId AS int
  START WITH 1;

    Then get the value for it.

    SET XACT_ABORT, NOCOUNT ON;
BEGIN TRAN;

DECLARE @MultiOrderReference int = NEXT VALUE FOR dbo.PickId;

INSERT INTO PickReference (Reference, Barcode, Amount)
VALUES
(@MultiOrderReference, '0068518000', 1),
(@MultiOrderReference, '0068548000', 4),
(@MultiOrderReference, '0068550000', 8);

COMMIT;

SELECT @MultiOrderReference AS Reference;

    If you really want to stick with your existing code, then you need to add locking hints: both SERIALIZABLE and UPDLOCK. You should also add an index on Reference.

    set @MultiOrderReference = isnull((
    select max(Reference)
    from PickReference with (serializable, updlock)
), 0) + 1;