Obsługa równoczesnego dostępu do tabeli kluczy bez zakleszczeń w programie SQL Server

Mam tabelę, która jest używana przez starszą aplikację jako substytut IDENTITY pól w różnych innych tabelach.

Każdy wiersz w tabeli przechowuje ostatnio używany identyfikator LastIDdla pola o nazwie wIDName .

Czasami przechowywany proc dostaje impasu - wydaje mi się, że zbudowałem odpowiedni moduł obsługi błędów; jednak jestem zainteresowany, aby zobaczyć, czy ta metodologia działa tak, jak myślę, czy też szczekam tutaj niewłaściwe drzewo.

Jestem całkiem pewien, że powinien istnieć sposób na uzyskanie dostępu do tego stołu bez żadnych zakleszczeń.

Sama baza danych jest skonfigurowana za pomocą READ_COMMITTED_SNAPSHOT = 1.

Po pierwsze, oto tabela:

CREATE TABLE [dbo].[tblIDs](
    [IDListID] [int] NOT NULL 
        CONSTRAINT PK_tblIDs 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1) ,
    [IDName] [nvarchar](255) NULL,
    [LastID] [int] NULL,
);

I indeks nieklastrowany w IDNamepolu:

CREATE NONCLUSTERED INDEX [IX_tblIDs_IDName] 
ON [dbo].[tblIDs]
(
    [IDName] ASC
) 
WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
);

GO

Niektóre przykładowe dane:

INSERT INTO tblIDs (IDName, LastID) 
    VALUES ('SomeTestID', 1);
INSERT INTO tblIDs (IDName, LastID) 
    VALUES ('SomeOtherTestID', 1);
GO

Procedura przechowywana używana do aktualizacji wartości przechowywanych w tabeli i zwracania następnego identyfikatora:

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs
        for a given IDName
        Author:         Max Vernon
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            SET @NewID = COALESCE((SELECT LastID 
                FROM tblIDs 
                WHERE IDName = @IDName),0)+1;
            IF (SELECT COUNT(IDName) 
                FROM tblIDs 
                WHERE IDName = @IDName) = 0 
                    INSERT INTO tblIDs (IDName, LastID) 
                    VALUES (@IDName, @NewID)
            ELSE
                UPDATE tblIDs 
                SET LastID = @NewID 
                WHERE IDName = @IDName;
            COMMIT TRANSACTION;
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
            ROLLBACK TRANSACTION;
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

Przykładowe wykonania zapisanego proc:

EXEC GetNextID 'SomeTestID';

NewID
2

EXEC GetNextID 'SomeTestID';

NewID
3

EXEC GetNextID 'SomeOtherTestID';

NewID
2

EDYTOWAĆ:

Dodałem nowy indeks, ponieważ istniejący indeks IX_tblIDs_Name nie jest używany przez SP; Zakładam, że procesor zapytań korzysta z indeksu klastrowego, ponieważ potrzebuje wartości przechowywanej w LastID. W każdym razie ten indeks JEST używany przez rzeczywisty plan wykonania:

CREATE NONCLUSTERED INDEX IX_tblIDs_IDName_LastID 
ON dbo.tblIDs
(
    IDName ASC
) 
INCLUDE
(
    LastID
)
WITH (FILLFACTOR = 100
    , ONLINE=ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON);

EDYCJA 2:

Skorzystałem z porady udzielonej przez @AaronBertrand i nieco ją zmodyfikowałem. Ogólną ideą tutaj jest udoskonalenie instrukcji w celu wyeliminowania niepotrzebnego blokowania i ogólnie w celu zwiększenia wydajności SP.

Poniższy kod zastępuje powyższy kod od BEGIN TRANSACTIONdo END TRANSACTION:

BEGIN TRANSACTION;
SET @NewID = COALESCE((SELECT LastID 
        FROM dbo.tblIDs 
        WHERE IDName = @IDName), 0) + 1;

IF @NewID = 1
    INSERT INTO tblIDs (IDName, LastID) 
    VALUES (@IDName, @NewID);
ELSE
    UPDATE dbo.tblIDs 
    SET LastID = @NewID 
    WHERE IDName = @IDName;

COMMIT TRANSACTION;

Ponieważ nasz kod nigdy nie dodaje rekordu do tej tabeli z wartością 0 LastID, możemy założyć, że jeśli @NewID ma wartość 1, to intencją jest dodanie nowego identyfikatora do listy, w przeciwnym razie aktualizujemy istniejący wiersz na liście.

Po pierwsze, unikałbym podróży w obie strony do bazy danych dla każdej wartości. Na przykład, jeśli aplikacja wie, że potrzebuje 20 nowych identyfikatorów, nie rób 20 podróży w obie strony. Wykonaj tylko jedno wywołanie procedury składowanej i zwiększ licznik o 20. Również lepiej podzielić tabelę na wiele.

Możliwe jest całkowite uniknięcie impasu. W moim systemie nie ma żadnych blokad. Można to osiągnąć na kilka sposobów. Pokażę, jak użyłbym sp_getapplock do wyeliminowania zakleszczeń. Nie mam pojęcia, czy to zadziała, ponieważ SQL Server jest zamkniętym źródłem, więc nie widzę kodu źródłowego i jako taki nie wiem, czy przetestowałem wszystkie możliwe przypadki.

Poniżej opisano, co działa dla mnie. YMMV.

Po pierwsze, zacznijmy od scenariusza, w którym zawsze dostajemy znaczną liczbę impasów. Po drugie, użyjemy sp_getapplock, aby je wyeliminować. Najważniejszą kwestią jest przetestowanie rozwiązania w warunkach skrajnych. Twoje rozwiązanie może być inne, ale musisz narazić je na wysoką współbieżność, co pokażę później.

Wymagania wstępne

Ustawmy tabelę z niektórymi danymi testowymi:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); 
GO 

INSERT INTO dbo.Numbers 
    ( n ) 
        VALUES  ( 1 ); 
GO 
DECLARE @i INT; 
    SET @i=0; 
WHILE @i<21  
    BEGIN 
    INSERT INTO dbo.Numbers 
        ( n ) 
        SELECT n + POWER(2, @i) 
        FROM dbo.Numbers; 
    SET @i = @i + 1; 
    END;  
GO

SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO

Następujące dwie procedury prawdopodobnie przyjmą impas:

CREATE PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

CREATE PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

Odtwarzanie zakleszczeń

Następujące pętle powinny odtwarzać ponad 20 zakleszczeń za każdym razem, gdy je uruchamiasz. Jeśli otrzymasz mniej niż 20, zwiększ liczbę iteracji.

Na jednej karcie uruchom to;

DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;

WHILE @i<5000 BEGIN ;
  BEGIN TRY 
    EXEC dbo.UpdateCounter1 @Key1=123456;
  END TRY
  BEGIN CATCH
    SET @DeadlockCount = @DeadlockCount + 1;
    ROLLBACK;
  END CATCH ;
  SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;

Na innej karcie uruchom ten skrypt.

DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;

WHILE @i<5000 BEGIN ;
  BEGIN TRY 
    EXEC dbo.UpdateCounter2 @Key2=123456;
  END TRY
  BEGIN CATCH
    SET @DeadlockCount = @DeadlockCount + 1;
    ROLLBACK;
  END CATCH ;
  SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;

Upewnij się, że zaczniesz oba w ciągu kilku sekund.

Używanie sp_getapplock do eliminowania zakleszczeń

Zmień obie procedury, uruchom ponownie pętlę i przekonaj się, że nie masz już impasu:

ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

ALTER PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

Korzystanie ze stołu z jednym rzędem w celu wyeliminowania zakleszczeń

Zamiast wywoływać sp_getapplock, możemy zmodyfikować następującą tabelę:

CREATE TABLE dbo.DeadlockTestMutex(
ID INT NOT NULL,
CONSTRAINT PK_DeadlockTestMutex PRIMARY KEY(ID),
Toggle INT NOT NULL);
GO

INSERT INTO dbo.DeadlockTestMutex(ID, Toggle)
VALUES(1,0);

Po utworzeniu i wypełnieniu tej tabeli możemy zastąpić następujący wiersz

EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';

z tym, w obu procedurach:

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

Możesz ponownie wykonać test warunków skrajnych i przekonać się, że nie mamy impasu.

Wniosek

Jak widzieliśmy, sp_getapplock może służyć do szeregowania dostępu do innych zasobów. Jako taki może być stosowany do eliminowania zakleszczeń.

Oczywiście może to znacznie spowolnić modyfikacje. Aby temu zaradzić, musimy wybrać odpowiednią szczegółowość blokady wyłącznej i, o ile to możliwe, pracować z zestawami zamiast poszczególnych wierszy.

Przed użyciem tego podejścia musisz przetestować je samodzielnie. Po pierwsze, musisz upewnić się, że otrzymujesz co najmniej kilkadziesiąt impasów przy swoim oryginalnym podejściu. Po drugie, nie powinieneś mieć żadnych zakleszczeń po ponownym uruchomieniu tego samego skryptu repro przy użyciu zmodyfikowanej procedury składowanej.

Ogólnie rzecz biorąc, nie sądzę, że istnieje dobry sposób na ustalenie, czy Twój T-SQL jest bezpieczny przed zakleszczeniem, po prostu patrząc na niego lub patrząc na plan wykonania. IMO jedynym sposobem ustalenia, czy twój kod jest podatny na zakleszczenia, jest wystawienie go na wysoką współbieżność.

Powodzenia w eliminowaniu zakleszczeń! W naszym systemie nie ma żadnych blokad, co doskonale wpływa na równowagę między pracą a życiem prywatnym.

Użycie XLOCKpodpowiedzi zarówno do twojego SELECTpodejścia, jak i poniższych, UPDATEpowinno być odporne na tego rodzaju impas:

DECLARE @Output TABLE ([NewId] INT);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

UPDATE
    dbo.tblIDs WITH (XLOCK)
SET 
    LastID = LastID + 1
OUTPUT
    INSERTED.[LastId] INTO @Output
WHERE
    IDName = @IDName;

IF(@@ROWCOUNT = 1)
BEGIN
    SELECT @NewId = [NewId] FROM @Output;
END
ELSE
BEGIN
    SET @NewId = 1;

    INSERT dbo.tblIDs
        (IDName, LastID)
    VALUES
        (@IDName, @NewId);
END

SELECT [NewId] = @NewId ;

COMMIT TRANSACTION;

Powróci z kilkoma innymi wariantami (jeśli nie zostanie pobity!).

Mike Defehr pokazał mi elegancki sposób na osiągnięcie tego w bardzo lekki sposób:

ALTER PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19

    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            UPDATE dbo.tblIDs 
            SET @NewID = LastID = LastID + 1 
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

(Dla kompletności, oto tabela związana z przechowywanym proc)

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL,
    LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED 
    (
        [IDName] ASC
    ) WITH 
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    ) 
);
GO

Oto plan wykonania najnowszej wersji:

Oto plan wykonania oryginalnej wersji (podatny na zakleszczenie):

Oczywiście nowa wersja wygrywa!

Dla porównania, wersja pośrednia z (XLOCK)itp. Tworzy następujący plan:

Powiedziałbym, że to wygrana! Dzięki za pomoc wszystkich!

Nie kradnie grzmotu Marka Storey'ego-Smitha, ale jest na czymś, co ma wyżej postawiony post (który, nawiasem mówiąc, otrzymał najwięcej pozytywnych opinii). Porada, której udzieliłem Maxowi, była skoncentrowana wokół konstrukcji „UPDATE set @variable = column = column + value”, którą uważam za naprawdę fajną, ale myślę, że może być nieudokumentowana (musi być obsługiwana, chociaż dlatego, że jest specjalnie dla TCP testy porównawcze).

Oto odmiana odpowiedzi Marka - ponieważ zwracasz nową wartość identyfikatora jako zestaw rekordów, możesz całkowicie zrezygnować ze zmiennej skalarnej, żadna wyraźna transakcja również nie powinna być konieczna, i zgodziłbym się, że bałagan z poziomami izolacji jest niepotrzebny także. Rezultat jest bardzo czysty i bardzo zręczny ...

ALTER PROC [dbo].[GetNextID]
  @IDName nvarchar(255)
  AS
BEGIN
SET NOCOUNT ON;

DECLARE @Output TABLE ([NewID] INT);

UPDATE dbo.tblIDs SET LastID = LastID + 1
OUTPUT inserted.[LastId] INTO @Output
WHERE IDName = @IDName;

IF(@@ROWCOUNT = 1)
    SELECT [NewID] FROM @Output;
ELSE
    INSERT dbo.tblIDs (IDName, LastID)
    OUTPUT INSERTED.LastID AS [NewID]
    VALUES (@IDName,1);
END

Naprawiłem podobny impas w systemie w zeszłym roku, zmieniając to:

IF (SELECT COUNT(IDName) FROM tblIDs WHERE IDName = @IDName) = 0 
  INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID)
ELSE
  UPDATE tblIDs SET LastID = @NewID WHERE IDName = @IDName;

Do tego:

UPDATE tblIDs SET LastID = @NewID WHERE IDName = @IDName;
IF @@ROWCOUNT = 0
BEGIN
  INSERT ...
END

Ogólnie rzecz biorąc, wybranie COUNTtylko określenia obecności lub nieobecności jest dość marnotrawstwem. W tym przypadku, ponieważ jest to 0 lub 1, to nie jest tak, że to dużo pracy, ale (a) ten nawyk może wykrwawić się w innych przypadkach, w których będzie znacznie droższy (w takich przypadkach użyj IF NOT EXISTSzamiast IF COUNT() = 0), i (b) dodatkowe skanowanie jest całkowicie niepotrzebne. TheUPDATE wykonuje zasadniczo taki sam czek.

Poza tym wygląda mi to na poważny zapach kodu:

SET @NewID = COALESCE((SELECT LastID FROM tblIDs WHERE IDName = @IDName),0)+1;

O co tu chodzi? Dlaczego po prostu nie użyć kolumny tożsamości lub wyprowadzić tej sekwencji przy użyciu ROW_NUMBER()w czasie zapytania?