Как имитировать тупик в SQL Server в одном процессе?

Ответ 1

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

Запустите script ниже в окне Studio SQL Server Management Studio. (Проверено только на 2008 R2.) Вы можете оставить его в рабочем состоянии до тех пор, пока это необходимо.

В месте, где вы хотите смоделировать тупик, вставьте вызов sp_simulatedeadlock. Запустите процесс, и произойдет тупик.

При завершении тестирования остановите запрос SSMS и запустите код очистки внизу.

/*
This script helps simulate deadlocks.  Run the entire script in a SQL query window.  It will continue running until stopped.
In the target script, insert a call to sp_simulatedeadlock where you want the deadlock to occur.
This stored procedure, also created below, causes the deadlock.
When you are done, stop the execution of this window and run the code in the cleanup section at the bottom.
*/
set nocount on

if object_id('DeadlockTest') is not null
    drop table DeadlockTest

create table DeadlockTest
(
    Deadlock_Key int primary key clustered,
    Deadlock_Count int
)
go

if exists (select * from sysobjects where id = object_id(N'sp_simulatedeadlock')
           AND objectproperty(id, N'IsProcedure') = 1)
drop procedure sp_simulatedeadlock
GO

create procedure sp_simulatedeadlock
(
    @MaxDeadlocks int = -1 -- specify the number of deadlocks you want; -1 = constant deadlocking
)
as begin

    set nocount on

    if object_id('DeadlockTest') is null
        return

    -- Volunteer to be a deadlock victim.
    set deadlock_priority low

    declare @DeadlockCount int

    select @DeadlockCount = Deadlock_Count -- this starts at 0
    from DeadlockTest
    where Deadlock_Key = 2

    -- Trace the start of each deadlock event.
    -- To listen to the trace event, setup a SQL Server Profiler trace with event class "UserConfigurable:0".
    -- Note that the user running this proc must have ALTER TRACE permission.
    -- Also note that there are only 128 characters allowed in the trace text.
    declare @trace nvarchar(128)

    if @MaxDeadlocks > 0 AND @DeadlockCount > @MaxDeadlocks
    begin

        set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Resetting deadlock count.  Will not cause deadlock.'
        exec sp_trace_generateevent
            @eventid = 82,  -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
            @userinfo = @trace

        -- Reset the number of deadlocks.
        -- Hopefully if there is an outer transaction, it will complete and persist this change.
        update DeadlockTest
        set Deadlock_Count = 0
        where Deadlock_Key = 2
        return
    end

    set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Simulating deadlock.'
    exec sp_trace_generateevent
        @eventid = 82,  -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
        @userinfo = @trace

    declare @StartedTransaction bit
    set @StartedTransaction = 0
    if @@trancount = 0
    begin
        set @StartedTransaction = 1
        begin transaction
    end

    -- lock 2nd record
    update DeadlockTest
    set Deadlock_Count = Deadlock_Count
    from DeadlockTest
    where Deadlock_Key = 2

    -- lock 1st record to cause deadlock
    update DeadlockTest
    set Deadlock_Count = Deadlock_Count
    from DeadlockTest
    where Deadlock_Key = 1

    if @StartedTransaction = 1
        rollback    
end
go

insert into DeadlockTest(Deadlock_Key, Deadlock_Count)
select 1, 0
union select 2, 0

-- Force other processes to be the deadlock victim.
set deadlock_priority high

begin transaction

while 1 = 1
begin

    begin try

        begin transaction

        -- lock 1st record
        update DeadlockTest
        set Deadlock_Count = Deadlock_Count
        from DeadlockTest
        where Deadlock_Key = 1

        waitfor delay '00:00:10'

        -- lock 2nd record (which will be locked when the target proc calls sp_simulatedeadlock)
        update DeadlockTest
        set Deadlock_Count = Deadlock_Count
        from DeadlockTest
        where Deadlock_Key = 2

        rollback

    end try
    begin catch
        print 'Error ' + convert(varchar(20), ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
        goto cleanup
    end catch

end

cleanup:

if @@trancount > 0
    rollback

drop procedure sp_simulatedeadlock
drop table DeadlockTest

Ответ 2

Вы можете использовать ошибку, которую Microsoft, похоже, не спешит исправлять, запустив

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

Этот SQL вызывает сам тупик. Подробнее читайте в блоге Aaron Bertand http://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

Ответ 3

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

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

Однако вы можете имитировать тупик на двух процессах, выполняющих один и тот же запрос (или sp). Некоторые идеи здесь

Ответ 4

Пол, благодарю вас за ваш вопрос и последующий ответ. Ваше сообщение вдохновило меня присоединиться к Qaru в первый раз.

Мне было сложно получить ответ на работу, и я просто хочу поделиться небольшими изменениями, которые я сделал, чтобы заставить его работать. Если это спасает кого-то в день их жизни, тогда это того стоит. Ключом является начало и откат транзакции sp_simulatedeadlock в самой процедуре. Я не внес изменения в вашу процедуру, упомянутую в вашем ответе.

DECLARE @DeadlockCounter INT = NULL

SELECT @DeadlockCounter = 0

WHILE @DeadlockCounter < 10
BEGIN
    BEGIN TRY
    /* The procedure was leaving uncommitted transactions, I rollback the transaction in the catch block */
        BEGIN tran simulate
            Exec sp_simulatedeadlock

        /* Code you want to deadlock */

        SELECT @DeadlockCounter = 10
    END TRY
    BEGIN CATCH
        Rollback tran simulate

        PRINT ERROR_MESSAGE()

        IF (ERROR_MESSAGE() LIKE '%deadlock%' OR ERROR_NUMBER() = 1205) AND @DeadlockCounter < 10
            BEGIN
                SELECT @DeadlockCounter +=1
                PRINT @DeadlockCounter

                IF @DeadlockCounter = 10
                BEGIN
                    RAISERROR('Deadlock limit exceeded or error raised', 16, 10);
                END
            END
    END CATCH
END

Ответ 5

Самый простой способ воспроизвести в С# с параллельным например.

var List =... (добавьте некоторые элементы с одинаковыми идентификаторами)

    Parallel.ForEach(List, 
        (item) =>
    {

        ReportsDataContext erdc = null;
        try
        {
            using (TransactionScope scope = new TransactionScope())
            {
                erdc = new ReportsDataContext("....connection....");
                var report = erdc.Report.Where(x => x.id == item.id).Select(x => x);
                report.Count++
                erdc.SubmitChanges();

                scope.Complete();
            }

            if (erdc != null)
                erdc.Dispose();
        }
        catch (Exception ex)
        {
            if (erdc != null)
                erdc.Dispose();
            ErrorLog.LogEx("multi thread victim", ex);
        }

больше интересует, как предотвратить эту ошибку в реальной ситуации с перекрестными потоками?