Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Проблема: при параллельном вызове хранимой процедуры периодически проскакивает дедлок. Граф дедлока прикладываю.
Пытался решить проблему поднятием уровня блокировки в запросе:
UPDATE dbo.Basket WITH(UPDLOCK TABLOCK)
SET StatusCode = @StatusCode
, StatusComments = @StatusComments
, StatusUserId = @StatusUserId
, StatusDate = GetDate()
WHERE BasketId = @BasketId

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

Подскажите, как можно вылечить этот дедлок, или хотя бы объясните неофиту, в чем причина дедлока.

К сообщению приложен файл (XMLFile2.xml - 4Kb) cкачать
23 апр 14, 10:24    [15920609]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
mittagswind
Member

Откуда:
Сообщений: 117
Oblom,

приложите скрипт по созданию таблицы и всех индексов на ней - нужна структура таблицы.
23 апр 14, 10:40    [15920713]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Oblom
хотя бы объясните неофиту, в чем причина дедлока.
У вас в одной из транзакций до update dbo.Basket есть вычитка из dbo.Basket с тем же @BasketId. Т.к. TIL у вас repeatable read, то S-блокировка остается висеть, что впоследствии приводит к дедлоку.
Oblom
как можно вылечить этот дедлок
Вычитку делать с хинтом updlock, либо отказаться от repeatable read.
23 апр 14, 10:50    [15920783]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
mittagswind
Oblom,

приложите скрипт по созданию таблицы и всех индексов на ней - нужна структура таблицы.


CREATE TABLE [dbo].[Basket](
[BasketId] [bigint] IDENTITY(1,1) NOT NULL,
[StatusCode] [tinyint] NOT NULL,
[StatusDate] [datetime] NOT NULL,
[StatusComments] [nvarchar](1000) NOT NULL,
[StatusUserId] [bigint] NOT NULL,
CONSTRAINT [PK_Basket] PRIMARY KEY CLUSTERED
(
[BasketId] ASC
)
)

Вот собственно и всё.
23 апр 14, 11:59    [15921365]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm
Oblom
хотя бы объясните неофиту, в чем причина дедлока.
У вас в одной из транзакций до update dbo.Basket есть вычитка из dbo.Basket с тем же @BasketId. Т.к. TIL у вас repeatable read, то S-блокировка остается висеть, что впоследствии приводит к дедлоку.
Oblom
как можно вылечить этот дедлок
Вычитку делать с хинтом updlock, либо отказаться от repeatable read.


Действительно, код процедуры выглядит примерно так:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION BasketUpdate_Status

SELECT  @StatusCodeOld = StatusCode, 
FROM dbo.Basket
WHERE BasketId = @BasketId

-- Много-много букв

UPDATE dbo.Basket
SET StatusCode = @StatusCode
, StatusComments = @StatusComments
, StatusUserId = @StatusUserId
, StatusDate = GetDate()
WHERE BasketId = @BasketId

-- Немного букв

COMMIT TRANSACTION BasketUpdate_Status


Кто и зачем поставил такой TIL я не в курсе. Поднятие селекта до UPDLOCK неприемлимо, резко вырастает время блокировки таблицы Basket. Скорее всего снижу TIL до READ COMMITED.

А можете на пальцах объяснить, почему TIL Repetable Read на такой последовательности запросов дает дедлок? Я в дедлоках новичок, а хочется понимать, что и зачем делаешь.
23 апр 14, 12:06    [15921437]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Daba
Member

Откуда:
Сообщений: 129
Oblom,

Не поможет READ COMMITTED. Дэдлоки останутся.
UPDLOCK + HOLDLOCK на выборку должны спасти отца русской демократии.
Иначе, делайте механизм retry.
23 апр 14, 12:58    [15921925]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Oblom
Поднятие селекта до UPDLOCK неприемлимо, резко вырастает время блокировки таблицы Basket.
Потому что не нужно совмещать с tablock. Достаточно будет
SELECT  @StatusCodeOld = StatusCode, 
FROM dbo.Basket with (updlock)
WHERE BasketId = @BasketId
Oblom
Скорее всего снижу TIL до READ COMMITED.
Без тщательного предварительного тестирования не стоит.
Oblom
А можете на пальцах объяснить, почему TIL Repetable Read на такой последовательности запросов дает дедлок?
В вашей процедуре, для значения ключа @BasketId
1. select накладывает S. После завершения select, S остается висеть из-за TIL RR.
2. Далее где-то S конвертируется в U
3. update конвертирует U в X

Когда это выполняется параллельно двумя процессами, то
1. Оба накладывают S.
2. Первый конвертирует S в U, второй не может и ждет, т.к. U не совместимы.
3. Первый хочет конвертировать U в X, Но не может и ждет, т.к. второй удерживает S.
4. Дедлок.

На мой взгляд, у вас вообще ошибка логическая - для одного значения BasketId процедура не должна вызываться параллельно.
Поэтому еще один возможный способ лечения: с помощью sp_getapplock сериализовать вызовы для одинаковых BasketId.
23 апр 14, 13:14    [15922117]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Crimean
Member

Откуда:
Сообщений: 13148
вы читаете, чтобы модифицировать. стандартные варианты:
1.при чтении ставьте сразу XLOCK - пусть все (!) ждут уже тут
2.при чтении оставляйте SHARED (и даже без HOLD), но работайте по timestamp + обрабатывайте ситуацию, когда он сменится к моменту UPDATE

p.s.
версия сервера?RCSI?

p.p.s.
UPDLOCK *не нужен* :)
23 апр 14, 13:33    [15922327]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
aleks2
Guest
Oblom
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION BasketUpdate_Status

SELECT  @StatusCodeOld = StatusCode, 
FROM dbo.Basket
WHERE BasketId = @BasketId

-- Много-много букв

UPDATE dbo.Basket
SET StatusCode = @StatusCode
, StatusComments = @StatusComments
, StatusUserId = @StatusUserId
, StatusDate = GetDate()
WHERE BasketId = @BasketId

-- Немного букв

COMMIT TRANSACTION BasketUpdate_Status


Что за бред? SELECT не нужен.


UPDATE dbo.Basket
SET StatusCode = @StatusCode
,  @StatusCodeOld = StatusCode
, StatusComments = @StatusComments
, StatusUserId = @StatusUserId
, StatusDate = GetDate()
WHERE BasketId = @BasketId


И, скорее всего, там еще дофига ненужного.
23 апр 14, 13:37    [15922366]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
aleks2,

"-- Много-много букв" использует результат этого селекта для кучи проверок, получения других данных и прочего. И @StatusCode в апдейте - это не всегда @StatusCode из селекта, и в один апдейт эту логику наверно можно запихнуть, но делать этого очень сильно не хочется. Приоритетно - попытаться убрать дедлок не ломая общую схему.

Crimean,
select @@VERSION
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Пока поставил TIL RC, жду результатов, хорошо(точнее плохо), что этот дедлок очень часто воспроизводится.
23 апр 14, 13:53    [15922542]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 Oblom

а переход на 2008 невозможен? там бы как раз XLOCK в вычитку + RCSI для базы и щасте
и писатели поделены и читатели не в обиде
23 апр 14, 14:04    [15922622]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Crimean
2 Oblom

а переход на 2008 невозможен? там бы как раз XLOCK в вычитку + RCSI для базы и щасте
и писатели поделены и читатели не в обиде


Сколько раз я задавал этот вопрос руководству... Увы.
23 апр 14, 14:09    [15922650]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Oblom
Приоритетно - попытаться убрать дедлок не ломая общую схему.
Тогда в процедуре сразу после
BEGIN TRANSACTION BasketUpdate_Status
Дописать
declare @result int, @res nvarchar(255) = object_name(@@procid) + N'_' + cast(@BasketId as nvarchar(10));
exec @result = sp_getapplock @res, N'Exclusive', N'Transaction';
if @result < 0
 begin
  raiserror(...);
  return;
 end;
И можно вернуть RR.
23 апр 14, 14:35    [15922848]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
invm
Oblom
Приоритетно - попытаться убрать дедлок не ломая общую схему.
Тогда в процедуре сразу после
BEGIN TRANSACTION BasketUpdate_Status
Дописать
declare @result int, @res nvarchar(255) = object_name(@@procid) + N'_' + cast(@BasketId as nvarchar(10));
exec @result = sp_getapplock @res, N'Exclusive', N'Transaction';
if @result < 0
 begin
  raiserror(...);
  return;
 end;
И можно вернуть RR.


Просто код процедуры содержит в себе и проверку timestamp по BasketId, так что по всем признакам TIL RR здесь избыточен. Пока полтора часа полет нормальный, дедлоков больше нет, до этого их было до десятка в час.
По sp_getapplock: есть сильные опасения, что в случае какого-либо сбоя до инструкции sp_releaseapplock не дойдет, что приведет к бесконечной блокировке конкретного BasketId.
23 апр 14, 15:37    [15923370]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Oblom,

так что в итоге-то сделали? просто понизили TIL до RC и убрали хинты?
23 апр 14, 15:41    [15923408]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Oblom
По sp_getapplock: есть сильные опасения, что в случае какого-либо сбоя до инструкции sp_releaseapplock не дойдет, что приведет к бесконечной блокировке конкретного BasketId.
Обратите внимание, что владелец блокировки транзакция, а не сессия. Вызывать sp_releaseapplock не нужно.
23 апр 14, 16:09    [15923642]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с дедлоком  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Crimean
Oblom,

так что в итоге-то сделали? просто понизили TIL до RC и убрали хинты?


Да, только хинты я ещё вчера убрал, когда они поставили блокировки в очередь и удвоили количество дедлоков. А сегодня понизил TIL до RC. Полет всё ещё нормальный, дедлоков после этого действия не было.
23 апр 14, 16:26    [15923810]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить