Параллельные запросы, deadlock при одновременном поиске и обновлении записей
Есть две хранимки, которые дёргаются из разных потоков в произвольные моменты времени. Это периодически приводит к дедлоку.
Вопрос: почему и как этого избежать?
Код:
CREATE PROCEDURE [dbo].[GetData]
@row_id uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[DataTable] INNER JOIN [dbo].[DataTableExt]
ON [dbo].[DataTable].[id] = [dbo].[DataTableExt].[id]
WHERE [dbo].[DataTable].[id] = @row_id
END
@row_id uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[DataTable] INNER JOIN [dbo].[DataTableExt]
ON [dbo].[DataTable].[id] = [dbo].[DataTableExt].[id]
WHERE [dbo].[DataTable].[id] = @row_id
END
Код:
CREATE PROCEDURE [dbo].[UpdateData]
@row_id uniqueidentifier,
@row_value1 int,
@row_value2 int,
@row_value3 int,
@row_value4 bigint,
@row_value5 bigint,
@row_value6 bigint,
@row_value7 bigint,
@row_value8 bigint,
@row_value9 bigint,
@row_value10 int,
@row_value11 int,
@row_value12 int,
@row_value13 int,
@row_value14 int,
@row_value15 int,
@row_value16 int
AS
BEGIN
SET NOCOUNT ON;
declare @counter bigint
exec [dbo].[IncrementCounter] @counter OUTPUT
UPDATE [dbo].[DataTableExt] SET
[value1] = @row_value1,
[value2] = @row_value2,
[value3] = @row_value3,
[value4] = @row_value4,
[value5] = @row_value5,
[value6] = @row_value6,
[value7] = @row_value7,
[value8] = @row_value8,
[value9] = @row_value9,
[value10] = @row_value10,
[value11] = @row_value11,
[value12] = @row_value12,
[value13] = @row_value13,
[value14] = @row_value14,
[value15] = @row_value15,
[counter] = @counter
WHERE [id] = @row_id
exec [dbo].[IncrementCounter] @counter OUTPUT
UPDATE [dbo].[DataTable] SET
[value16] = @row_value16,
[counter] = @counter
WHERE [id] = @row_id
END
@row_id uniqueidentifier,
@row_value1 int,
@row_value2 int,
@row_value3 int,
@row_value4 bigint,
@row_value5 bigint,
@row_value6 bigint,
@row_value7 bigint,
@row_value8 bigint,
@row_value9 bigint,
@row_value10 int,
@row_value11 int,
@row_value12 int,
@row_value13 int,
@row_value14 int,
@row_value15 int,
@row_value16 int
AS
BEGIN
SET NOCOUNT ON;
declare @counter bigint
exec [dbo].[IncrementCounter] @counter OUTPUT
UPDATE [dbo].[DataTableExt] SET
[value1] = @row_value1,
[value2] = @row_value2,
[value3] = @row_value3,
[value4] = @row_value4,
[value5] = @row_value5,
[value6] = @row_value6,
[value7] = @row_value7,
[value8] = @row_value8,
[value9] = @row_value9,
[value10] = @row_value10,
[value11] = @row_value11,
[value12] = @row_value12,
[value13] = @row_value13,
[value14] = @row_value14,
[value15] = @row_value15,
[counter] = @counter
WHERE [id] = @row_id
exec [dbo].[IncrementCounter] @counter OUTPUT
UPDATE [dbo].[DataTable] SET
[value16] = @row_value16,
[counter] = @counter
WHERE [id] = @row_id
END
Проблема разрешилась сама собой, когда вместо одной постоянной коннекции, я стал создавать новую на каждый запрос к базе (дёргаются из пула на основе строки подключения, так что существенных задержек это не создаёт). Ещё раз всем спасибо. Инфа по дедлокам и возможностям сервера также пригодилась!
Цитата: grgdvo
А Вы уверены, что дедлоки происходят именно на уровне СУБД?
Кстати, MS SQL какой версии? Это важно.
Если выше или равно 2005, то можно попробовать использовать какие-нибудь LOCK-и
см. http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.90%29.aspx
Кстати, MS SQL какой версии? Это важно.
Если выше или равно 2005, то можно попробовать использовать какие-нибудь LOCK-и
см. http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.90%29.aspx
Уверен. О дедлоке говорит сервер.
Версия Standart x64, v9.00.5057.00
Цитата: SomewherSomehow
Для выяснения причины, нужно смотреть граф дедлока. Тогда станет понятно кто кого ждет и почему дедлок.
Общие рекомендации по минимизированию дедлоков примерно следующие:
1. Делать максимально короткие транзакции
2. Создавать нужные индексы, чтобы при поиске строк для обновления было как можно меньше сканирований
3. Обращаться к объектам в одной последовательности, т.е: proc1: update a update b; proc2: update a update b, а не proc2: update b update a
4. Включить оптимистичные блокировки snapshot isolation, если версия сервера позволяет (может спасти не всегда).
5. Включить высокий уровень изоляции транзакции, либо высокий уровень блокировки на уровне таблиц, если понимаете какие таблицы следует блокировать (может сильно упасть производительность для одновременной работающих пользователей)
6. Если ничего не помогает, напишите модуль с retry логикой, который, в случае отвала по дедлоку, рестартовал бы процедуру еще раз.
Анализируйте граф, выбирайте воркараунд который больше вам подходит, экспериментируйте и тестируйте.
Общие рекомендации по минимизированию дедлоков примерно следующие:
1. Делать максимально короткие транзакции
2. Создавать нужные индексы, чтобы при поиске строк для обновления было как можно меньше сканирований
3. Обращаться к объектам в одной последовательности, т.е: proc1: update a update b; proc2: update a update b, а не proc2: update b update a
4. Включить оптимистичные блокировки snapshot isolation, если версия сервера позволяет (может спасти не всегда).
5. Включить высокий уровень изоляции транзакции, либо высокий уровень блокировки на уровне таблиц, если понимаете какие таблицы следует блокировать (может сильно упасть производительность для одновременной работающих пользователей)
6. Если ничего не помогает, напишите модуль с retry логикой, который, в случае отвала по дедлоку, рестартовал бы процедуру еще раз.
Анализируйте граф, выбирайте воркараунд который больше вам подходит, экспериментируйте и тестируйте.
1. Транзакции не используются (не спрашивай почему -__-)
2. Индексы созданы. А тут ещё и связывание и поиск по PK
3. Невозможно в принципе. о_О Это многопоточное, я бы даже сказал многопроцессорное ПО.
4. Покурю, спасибо.
5. Покурю, спасибо.
6. Из-за отсутствия транзакций, не вариант. =\
Кстати, MS SQL какой версии? Это важно.
Если выше или равно 2005, то можно попробовать использовать какие-нибудь LOCK-и
см. http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.90%29.aspx
Общие рекомендации по минимизированию дедлоков примерно следующие:
1. Делать максимально короткие транзакции
2. Создавать нужные индексы, чтобы при поиске строк для обновления было как можно меньше сканирований
3. Обращаться к объектам в одной последовательности, т.е: proc1: update a update b; proc2: update a update b, а не proc2: update b update a
4. Включить оптимистичные блокировки snapshot isolation, если версия сервера позволяет (может спасти не всегда).
5. Включить высокий уровень изоляции транзакции, либо высокий уровень блокировки на уровне таблиц, если понимаете какие таблицы следует блокировать (может сильно упасть производительность для одновременной работающих пользователей)
6. Если ничего не помогает, напишите модуль с retry логикой, который, в случае отвала по дедлоку, рестартовал бы процедуру еще раз.
Анализируйте граф, выбирайте воркараунд который больше вам подходит, экспериментируйте и тестируйте.