Транзакция SQL была заблокирована

Question

Иногда я получаю такое исключение на не очень загруженном SQL-сервере:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Line number: 1
Error Number: 1205
Procedure: 
Server name: P01
Error Source: .Net SqlClient Data Provider
Error State: 47

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

Как это сделать, когда происходит исключение внутри процедуры, вызванной из триггера, который был вызван вставкой, сделанной какой-то процедурой (то есть: процедура01 -> вставка -> триггер -> процедура 02!)

Answer 1

Я бы предложил, чтобы вы подошли к проблеме с двух точек зрения.

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

2. Узнайте, что вызывает ваши тупиковые события. Это можно сделать одним из двух способов: либо запустить трассировку SQL Server Profiler для перехвата и записи события тупика, либо включить некоторые флаги трассировки SQL Server, которые будут записывать сведения о событии тупика в журнал ошибок SQL Server.

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

Для дальнейшего чтения взгляните на:

• Как отследить взаимные блокировки с помощью SQL Server Profiler
• Минимизация тупиков
• Обнаружение и устранение тупиков

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

Answer 2

Настройте трассировку SQL на стороне сервера, которая фиксирует события графа взаимоблокировки, чтобы вы могли просматривать файл.trc с помощью SQL Profiler. Таким образом, вы можете иметь что-то на месте, чтобы иметь возможность устранять любые тупики. Я предоставил код ниже. Вам придется изменить путь к файлу в зависимости от ситуации. Было бы неплохо настроить этот скрипт для запуска при запуске сервера SQL.

К вашему сведению - множество разных вещей может вызвать тупик, одна из которых - отсутствие индексов.

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 10 
declare @dtName nvarchar(50)
select @dtName=(N'I:\Trace_Logs\DeadLockTrace'+ convert(nvarchar(8),getdate(),112))


-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, @dtName, @maxfilesize, NULL ,365

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 51, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 60, @on
exec sp_trace_setevent @TraceID, 148, 64, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 41, @on
exec sp_trace_setevent @TraceID, 25, 7, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 55, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 56, @on
exec sp_trace_setevent @TraceID, 25, 64, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 25, @on
exec sp_trace_setevent @TraceID, 25, 41, @on
exec sp_trace_setevent @TraceID, 25, 49, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 26, @on
exec sp_trace_setevent @TraceID, 25, 58, @on
exec sp_trace_setevent @TraceID, 25, 3, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 51, @on
exec sp_trace_setevent @TraceID, 25, 4, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 52, @on
exec sp_trace_setevent @TraceID, 25, 60, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 55, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 56, @on
exec sp_trace_setevent @TraceID, 59, 64, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 21, @on
exec sp_trace_setevent @TraceID, 59, 25, @on
exec sp_trace_setevent @TraceID, 59, 41, @on
exec sp_trace_setevent @TraceID, 59, 49, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 26, @on
exec sp_trace_setevent @TraceID, 59, 58, @on
exec sp_trace_setevent @TraceID, 59, 3, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 51, @on
exec sp_trace_setevent @TraceID, 59, 4, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 59, 52, @on
exec sp_trace_setevent @TraceID, 59, 60, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Answer 3

Я решил проблему, используя уровень изоляции ReadCommited.

Answer 4

Я исправил эту ошибку:

1. Перезапустите SQL Server

2. Запустить запрос:

   ИСПОЛЬЗУЙТЕ [master] ALTER DATABASE name_database SET MULTI_USER WITH ROLLBACK IMMEDIATE

Удачи!