Máme historickou tabulku, která má umělé id, ale nemá na něm IDENTITY. Nechceme procházet očistcem a převádět ji na tabulku s IDENTITY, ale chceme zakládat ID tak, aby jsme se vyhnuli chybě „Violation of PRIMARY KEY constraint“ při souběhu ze dvou stran.

Ta historická tabulka může vypadat třeba takto:

CREATE TABLE dbo.tabulka(
	id_tabulka INT CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED,
	data VARCHAR(4000)
)

A špatný přistup, jak zakládat záznamy třeba takto:

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Toto když spustíme ve dvou oknech, tak nám to ve druhém upadne na „Violation of PRIMARY KEY constraint“, protože se pokusíme založit podruhé stejný primární klíč.

Možnosti řešení jsou dvě. Pokud to máme v transakci, tak můžeme použít UPDLOCK. Ten nám uvalí U zámek na poslední záznam v tabulce a druhá transakce ho tak nebude moct přečíst a zůstane čekat do doby, než první transakce doběhne do konce.

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Druhou možností je, že v případě chyby zkusíme to maximální id vypočíst znova. Z opatrnosti tomu dáme jen 5 pokusů a pak toho případně necháme a chybu vypropagujem.

DECLARE @counter INT = 0;
DECLARE @max_counter INT = 5;
DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))
WHILE @counter < @max_counter
BEGIN
  BEGIN TRY
    SET @id = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

    --jen pro zvýraznění problému
    WAITFOR DELAY '0:0:10'

    INSERT INTO dbo.tabulka
    (
      id_tabulka,
      data
    )
    VALUES
    (	
      @id,
      'data'
    )

    SET @counter = @max_counter; --vypadnout z cyklu
  END TRY
  BEGIN CATCH
    SET @counter += 1;
    IF ERROR_NUMBER() <> 2627 --Violation of PRIMARY KEY constraint
      OR @counter = @max_counter
        THROW;
  END CATCH;
END;

První řešení mi přijde čistší, ale zase tam druhá transakce musí čekat na dokončení první.