Подтвердить что ты не робот

Обнаружение грязных записей из хранимой процедуры

У меня есть 100 потоков, каждый из которых вызывает хранимую процедуру, как указано ниже.

Как предотвратить загрязнение чтения?

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity
    (@NextKey int output, @TableID int)
AS
    declare @RowCount int, @Err int

    set nocount on

    select  
        @NextKey = 0

    begin transaction

Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber 
    SET CerealNumber = CerealNumber + 1  
    WHERE CerealNumberID = @TableID

    SELECT 
        @RowCount = @@RowCount, 
        @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0            /* If Error gets here then exit         */
    BEGIN
        RAISERROR ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        ROLLBACK TRANSACTION

        set nocount off
        return 1
    END

    IF @RowCount = 0                /* No Record then assume table is not   */
                                /* been initialized for TableID Supplied*/
    BEGIN
        RAISERROR('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16, 1, @TableID)
        set nocount off
        Rollback Transaction
        return 1
    END

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
    FROM CfgCerealNumber 
    WHERE CerealNumberID = @TableID

    SELECT @Err = @@Error                       /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0                            /* If Error gets here then exit         */
    BEGIN
        RAISERROR('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        Rollback Transaction    
        set nocount off
        return 1
    END

    commit transaction
    set nocount off
    return 0
GO

Похоже, что эта часть хранимой процедуры возвращает то же значение около 0,01% времени при параллельном запуске:

SELECT @NextKey = CerealNumber 
FROM CfgCerealNumber 
WHERE CerealNumberID = @TableID

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

Как предотвратить загрязнение чтения?

4b9b3361

ОТВЕТЫ

Ответ 1

Если вам нужно обновить и вернуть то, что вы обновили, я бы просто использовал предложение OUTPUT:

UPDATE CfgCerealNumber 
SET CerealNumber = CerealNumber + 1 
OUTPUT INSERTED.CerealNumber
WHERE CerealNumberID = @TableID;

Если вам нужна дополнительная проверка, вы можете OUTPUT в объявленную переменную таблицы перед возвратом набора результатов из хранимой процедуры.

Другой альтернативой было бы сначала создать блокировку блокировки в таблице, а затем обновить:

SELECT @CerealNumber = CerealNumber + 1 
FROM CfgCerealNumber WITH (HOLDLOCK, UPDLOCK) 
WHERE CerealNumberID = @TableID;

UPDATE CfgCerealNumber
SET CerealNumber = @CerealNumber
WHERE CerealNumberID = @TableID;

Но я бы пожертвовал деньги, я видел, что это все еще вызывает проблемы. Я доверяю ему гораздо меньше.

Ответ 2

Вы можете избежать проблемы, используя синтаксис @variable = column = expression, как описано в Books Online. Кроме того, поскольку оператор выполняется в транзакции с одним оператором, вы можете избежать явной транзакции.

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE PROCEDURE GetNextSerialIdentity
      @NextKey int output
    , @TableID int
AS
SET NOCOUNT ON;

UPDATE dbo.CfgSerialNumber
SET @NextKey = SerialNumber = SerialNumber + 1
WHERE SerialNumberID = @TableID;

IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                  16,1, @TableID);
END
GO

Ответ 3

Вам нужно заменить это утверждение

UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  
WHERE CerealNumberID = @TableID

:

declare @CerealNumber int

SELECT @CerealNumber = CerealNumber  + 1
FROM CfgCerealNumber WITH (READCOMMITTED, READPAST, ROWLOCK) 
WHERE CerealNumberID = @TableID

if @CerealNumber is not null
    UPDATE CfgCerealNumber Set CerealNumber = @CerealNumber
    WHERE CerealNumberID = @TableID
else
    raiserror ('Row was locked by another update (no dirty read and no deadlock happen) or no Table Record Exists in CfgCerealNumber for ID:%d   ', 
              16,1, @TableID)

эти табличные подсказки READCOMMITTED, READPAST, ROWLOCK будут следить за тем, чтобы у вас не было грязного чтения и без тупика

он также позволит вам решить, хотите ли вы продолжать обновление

READCOMMITTED
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED, используя либо блокировку, либо свертку строк. Если параметр базы данных READ_COMMITTED_SNAPSHOT выключен, то модуль Database Engine получает общие блокировки при чтении данных и освобождает эти блокировки при завершении операции чтения. Если параметр базы данных READ_COMMITTED_SNAPSHOT включен, механизм Database Engine не получает блокировок и использует управление версиями строк.

READPAST
Указывает, что Database Engine не читает строки, заблокированные другими транзакциями. Когда задан READPAST, блокировки на уровне строк пропускаются. То есть, Database Engine пропускает строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут освобождены. Например, предположим, что таблица T1 содержит один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменяет значение от 3 до 8, но еще не зафиксирована, SELECT * FROM T1 (READPAST) дает значения 1, 2, 4, 5. READPAST в первую очередь используется для уменьшения конкуренции за блокировку при реализации рабочей очереди, использующей таблицу SQL Server. Считыватель очередей, который использует READPAST, пропускает предыдущие записи в очереди, заблокированные другими транзакциями, в следующую доступную запись в очереди, не дожидаясь, пока другие транзакции освободят свои блокировки.

ROWLOCK
Указывает, что блокировки строк берутся, когда фиксируются блокировки страниц или таблиц. Если указано в транзакциях, работающих на уровне изоляции SNAPSHOT, блокировки строк не выполняются, если ROWLOCK не объединен с другими табличными подсказками, требующими блокировок, таких как UPDLOCK и HOLDLOCK.

Исходные подсказки таблицы MSDN (Transact-SQL)

Вам также может потребоваться использовать UPDLOCK и/или HOLDLOCK

Ответ 4

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

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

    ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
    AS
    declare @RowCount int, @Err int
    set nocount on
    select  @NextKey = 0
    begin transaction
   --ADDED CODE
    EXEC sp_getapplock @Resource='MyLock', @LockMode='Exclusive'
                , @LockOwner='Transaction', @LockTimeout = 15000
    Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  WHERE CerealNumberID = @TableID
    select  @RowCount = @@RowCount, @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
        raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                      16,1, @TableID)
        set nocount off
                Rollback Transaction
        return 1
        end

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
     From CfgCerealNumber WHERE CerealNumberID = @TableID

    select   @Err = @@Error                     /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    commit transaction
    set nocount off
    return 0

Ответ 5

Операция транзакции начать транзакцию/фиксацию гарантирует, что у вас нет грязных чтений.

Существует недостаток производительности, если процедура выполняется из другой транзакции, блокировка записи не будет выпущена до тех пор, пока не будет зафиксирована самая внешняя транзакция. Это приведет к сериализации всех потоков и блоков concurrency.

См. этот пример (предположим, что требуется много времени):

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the write lock is established
    ...
commit tran -- the write lock is released

Можно удалить блокировку до конца транзакции, но вы должны создать блокировку приложения с помощью процедур sp_getAppLock и sp_releaseAppLock внутри процедуры GetNextCerealIdentity.

Это может быть довольно сложно, вы должны обратить внимание или у вас может быть как тупик, так и некоторые грязные чтения.

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

Не забудьте снять блокировку и в случае ошибок. Блокировка будет выпущена в конце транзакции, но вы хотите отпустить ее в конце процедуры!: -)

Вы должны быть уверены, что ваша блокировка приложения является единственной, удерживающей в таблице счетчики (CfgCerealNumber).

Обычно SQL Server помещает блокировку записи в таблицу и будет мешать вашей блокировке, потому что блокировка записи будет выпущена в конце транзакции, а не в конце вашей процедуры.

Вы должны изменить процедуру на уровень транзакции READ UNCOMMITED, чтобы UPDATE в вашем коде не генерировал блокировки записи. не забудьте вернуться к COMMITTED в тот же момент, когда вы отпустите блокировку приложения.

Если вы приобрели блокировку в эксклюзивном режиме, вы будете уверены, что только одно соединение сможет выполнить обновление/выбор в таблице CfgCerealNumber.

Вы можете предоставить блокировку любому имени, которое вы хотите. Я использовал то же имя, что и таблица (CfgCerealNumber), но это не важно. Самое главное, что вы должны использовать одно и то же имя для первоначальной версии get и для всех, которую вы ввели в свой код.

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
declare @RowCount int, @Err int
set nocount on
select  @NextKey = 0

-- replace begin tran with:    
EXEC sp_getapplock @Resource = 'CfgCerealNumber', @LockMode = 'Exclusive';  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*Update CfgCerealNumber Table */
UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  
WHERE CerealNumberID = @TableID
select  @RowCount = @@RowCount, @Err = @@Error  /*Obtain updated Cereal number previously incremented*/

if @Err <> 0   /* If Error gets here then exit         */
    begin                        
    raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
           16,1, @Err, @TableID)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  
    set nocount off
    return 1
    end

if @RowCount = 0 /* No Record then assume table is not   */
                 /* been initialized for TableID Supplied*/
    begin
    raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                  16,1, @TableID)
    set nocount off

    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  

    return 1
    end

/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = CerealNumber 
 From CfgCerealNumber WHERE CerealNumberID = @TableID

select   @Err = @@Error /*Obtain updated Cereal number previously incremented*/

if @Err <> 0  /* If Error gets here then exit         */
    begin                        
    raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 
           16,1, @Err, @TableID)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  
    set nocount off
    return 1
    end

-- replace commit transaction with:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  

set nocount off
return 0
GO

Если вы измените эту процедуру, мой предыдущий пример не даст проблем с concurrency:

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the lock is established AND released
    ...
commit tran -- common "write locks" are released

Возможным дополнением является использование конструкции BEGIN/END TRY.. BEGIN/END CATCH, так что вы также отпустите блокировку в случае неожиданных исключений (это даст другой про: у вас будет одна точка выхода из процедура, поэтому у вас будет одна точка, где вы должны поместить инструкции для освобождения блокировки и вернуть предыдущий уровень изоляции транзакции.

См. следующие ссылки: (sp_getAppLock) https://msdn.microsoft.com/en-us/library/ms189823.aspx и (sp_releaseAppLock) https://technet.microsoft.com/en-us/library/ms178602.aspx

Ответ 6

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

CREATE PROC MyCriticalWork(@MyParam INT)      
AS
    DECLARE @LockRequestResult INT
    SET @LockRequestResult=0

    DECLARE @MyTimeoutMiliseconds INT
    SET @MyTimeoutMiliseconds=5000--Wait only five seconds max then timeouit

    BEGIN TRAN

    EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN

        /*
        DO YOUR CRITICAL READS AND WRITES HERE
        */

        --Release the lock
        COMMIT TRAN
    END ELSE
        ROLLBACK TRAN

Ответ 7

Bacon Bits избили меня, но использование предложения OUTPUT будет самым простым способом обойти вашу гоночную проблему. Конечно, блокировка курса также является вариантом, хотя я думаю, что она будет иметь несколько более высокие накладные расходы. Тем не менее, использование столбца IDENTITY или SEQUENCE намного проще, чем пытаться реализовать эту функциональность вручную.

Я взял на себя смелость ответить на ваш код и добавить несколько замечаний:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
set nocount on

DECLARE @RowCount int, @Err int
DECLARE @output TABLE (NextKey int)

begin transaction

    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber WITH (UPDLOCK) 
       Set CerealNumber = CerealNumber + 1
    OUTPUT inserted.CerealNumber INTO @output (NextKey)
     WHERE CerealNumberID = @TableID

    select @RowCount = @@RowCount, /*Obtain updated Cereal number previously incremented*/ 
           @Err = @@Error      

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
            Rollback Transaction    
            raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16,1, @Err, @TableID)
            return -1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
            Rollback Transaction
            raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16,1, @TableID)
            return -1
        end

COMMIT TRANSACTION


/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = NextKey 
 From @output

return 0
GO

Примечания:

  • Перед тем, как выйти из хранимой процедуры, не нужно делать SET NOCOUNT OFF. Когда вы выходите из области видимости, этот параметр вернется к тому, что было до того, как вы ввели хранимую процедуру.
  • Я не уверен, что вам нужен WITH (UPDLOCK), но это, безусловно, не повредит.
  • Я сохранил транзакцию как можно короче, нет причины извлекать значение из таблицы-переменной внутри транзакции.
  • Я думаю, что сначала безопаснее сначала сделать ROLLBACK, а затем сделать RaisError() просто потому, что последнее может привести к тому, что соединение будет удалено каким-то клиентским программным обеспечением и/или вы можете быть внутри TRY...CATCH. Оба будут разорвать поток команд, и вы закончите несоответствие количества транзакций.
  • YMMV, но мне всегда говорили использовать отрицательные коды возврата в случае ошибки. Положительные коды возврата могут использоваться для указания количества строк. Хотя я никогда не видел, чтобы это использовалось на практике.

Ответ 8

Как уже упоминалось, вы можете использовать встроенные функции автоматического прироста, такие как столбцы идентификации или последовательность.

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

Например, вы можете добавить подсказки к FIRST доступу к таблице (в транзакции), как показано ниже:

UPDATE CfgCerealNumber
Set CerealNumber = CerealNumber + 1
FROM CfgCerealNumber with (tablockx, holdlock)
WHERE CerealNumberID = @TableID

Это гарантирует последовательный доступ к таблице во всех параллельных потоках.