Предотвращение состояний гонки в нескольких строках

Я сталкивался с этой точной ситуацией в прошлом и в конечном итоге использовал SP_GetAppLock для создания семафора на ключе, чтобы предотвратить состояние гонки. Несколько лет назад я написал статью, в которой обсуждались различные методы. Статья здесь:

http://www.sqlservercentral.com/articles/Miscellaneous/2649/

Основная идея состоит в том, что вы получаете блокировку на сконструированный ключ, отдельный от таблицы. Таким образом, вы можете быть очень точными и только блокировать spid-пакеты, которые потенциально могут создать состояние гонки, а не блокировать других потребителей таблицы.

Я оставил основную часть статьи ниже, но я бы применил эту технику, установив блокировку на сконструированный ключ, такой как

@Key = 'GiftCardTransaction' + GiftCardId 

Получение блокировки для этого ключа (и обеспечение последовательного применения этого подхода) предотвратит любое потенциальное состояние гонки, поскольку первый, кто получит блокировку, будет работать со всеми другими запросами, ожидающими освобождения блокировки (или тайм-аута, в зависимости от как вы хотите, чтобы ваше приложение работало.)

Суть статьи здесь:

SP_getapplock - это оболочка для расширенной процедуры XP_USERLOCK. Он позволяет использовать механизм блокировки SQL SERVER для управления параллелизмом за пределами таблиц и строк. Его можно использовать для маршалинга вызовов PROC таким же образом, как и вышеупомянутые решения, с некоторыми дополнительными функциями.

Sp_getapplock добавляет блокировки непосредственно в память сервера, что снижает ваши накладные расходы.

Во-вторых, вы можете указать тайм-аут блокировки без необходимости изменять настройки сеанса. В тех случаях, когда вам нужен только один вызов для выполнения определенного ключа, быстрый тайм-аут гарантирует, что процедура не задержит выполнение приложения на очень долгое время.

В-третьих, sp_getapplock возвращает статус, который может быть полезен при определении, следует ли вообще запускать код. Опять же, в случаях, когда вам нужен только один вызов для определенного ключа, код возврата 1 сообщит вам, что блокировка была успешно предоставлена ​​после ожидания снятия других несовместимых блокировок, поэтому вы можете выйти, не выполняя больше кода (например, проверка наличия, например). Синаксис выглядит следующим образом:

   sp_getapplock [ @Resource = ] 'resource_name',
      [ @LockMode = ] 'lock_mode'
      [ , [ @LockOwner = ] 'lock_owner' ]
      [ , [ @LockTimeout = ] 'value' ]

Пример использования sp_getapplock

/************** Proc Code **************/
CREATE PROC dbo.GetAppLockTest
AS

BEGIN TRAN
    EXEC sp_getapplock @Resource = @key, @Lockmode = 'Exclusive'

    /*Code goes here*/

    EXEC sp_releaseapplock @Resource = @key
COMMIT

Я знаю, что это само собой разумеется, но поскольку область блокировок sp_getapplock является явной транзакцией, обязательно SET XACT_ABORT ON или включите проверки в код, чтобы гарантировать, что ОТКАТ произойдет там, где это необходимо.

Мой T-SQL немного заржавел, но вот мой шанс на решение. Хитрость заключается в том, чтобы установить блокировку обновления для всех транзакций для этой подарочной карты в начале транзакции, чтобы, пока все процедуры не считывают незафиксированные данные (что является поведением по умолчанию), это эффективно блокирует транзакции только целевую подарочную карту.

CREATE PROC dbo.AddGiftCardTransaction
    (@GiftCardID int,
    @TransactionAmount float,
    @id int out)
AS
BEGIN
    BEGIN TRANS
    DECLARE @TotalPriorTransAmount float;
    SET @TotalPriorTransAmount = SELECT SUM(Amount) 
    FROM dbo.GiftCardTransactions WTIH UPDLOCK 
    WHERE GiftCardId = @GiftCardID;

    IF @TotalPriorTransAmount + @TransactionAmount > SELECT TOP 1 OriginalAmout 
    FROM GiftCards WHERE GiftCardID = @GiftCardID;
    BEGIN
        PRINT 'Transaction would exceed GiftCard Value'
        set @id = null
        RETURN
    END
    ELSE
    BEGIN
        INSERT INTO dbo.GiftCardTransactions (GiftCardId, Amount) 
        VALUES (@GiftCardID, @TransactionAmount);
        set @id = @@identity
        RETURN
    END
    COMMIT TRANS
END

Хотя это очень ясно, я думаю, что было бы более эффективно и более дружественно к T-SQL использовать такой оператор отката, как:

BEGIN
    BEGIN TRANS
    INSERT INTO dbo.GiftCardTransactions (GiftCardId, Amount) 
    VALUES (@GiftCardID, @TransactionAmount);
    IF (SELECT SUM(Amount) 
        FROM dbo.GiftCardTransactions WTIH UPDLOCK 
        WHERE GiftCardId = @GiftCardID) 
        > 
        (SELECT TOP 1 OriginalAmout FROM GiftCards 
        WHERE GiftCardID = @GiftCardID)
    BEGIN
        PRINT 'Transaction would exceed GiftCard Value'
        set @id = null
        ROLLBACK TRANS
    END
    ELSE
    BEGIN
        set @id = @@identity
        COMMIT TRANS
    END
END