Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 Как избежать Deadlock?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4431
 		Несколько клиентов вызывают одну и ту же процедуру, котрая читает и удаляет из таблицы т1, читает и вставляет в т2

Получаю дедлоки типа такого

Date,Source,Severity,Message
10/13/2017 16:17:29,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000009E9CD60598 Mode: S SPID:190 BatchID:0 ECID:0 TaskProxy:(0x0000009C9B206960) Value:0xfd125ac0 Cost:(0/5960)
10/13/2017 16:17:29,spid7s,Unknown,Victim Resource Owner:
10/13/2017 16:17:29,spid7s,Unknown,
10/13/2017 16:17:29,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000009CD6B14408 Mode: S SPID:67 BatchID:0 ECID:0 TaskProxy:(0x0000009C7151E960) Value:0x9b80bb00 Cost:(0/5984)
10/13/2017 16:17:29,spid7s,Unknown,Requested by:
10/13/2017 16:17:29,spid7s,Unknown,Grant List 1:
10/13/2017 16:17:29,spid7s,Unknown,Input Buf: Language Event: set nocount on<nl/>/*<nl/>dbcc traceon (1204<c/> 3605<c/> -1)<nl/>go<nl/>dbcc tracestatus(-1)<nl/>go<nl/>*/<nl/>DECLARE @s NVARCHAR(2048)<nl/>DECLARE @msg NVARCHAR(2048)<nl/>declare @hid hierarchyid;<nl/><nl/>declare @n int = 50<nl/>declare @i int = 1<nl/>declare @int_id int = 0<nl/>declare @top int =
10/13/2017 16:17:29,spid7s,Unknown,SPID: 190 ECID: 0 Statement Type: SELECT Line #: 67
10/13/2017 16:17:29,spid7s,Unknown,Owner:0x0000009CF7452780 Mode: IX       Flg:0x40 Ref:0 Life:02000000 SPID:190 ECID:0 XactLockInfo: 0x0000009E9CD605D0
10/13/2017 16:17:29,spid7s,Unknown,Grant List 0:
10/13/2017 16:17:29,spid7s,Unknown,PAGE: 16:1:172451              CleanCnt:4 Mode:IX Flags: 0x3
10/13/2017 16:17:29,spid7s,Unknown,Node:2
10/13/2017 16:17:29,spid7s,Unknown,
10/13/2017 16:17:29,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000009E9CD60598 Mode: S SPID:190 BatchID:0 ECID:0 TaskProxy:(0x0000009C9B206960) Value:0xfd125ac0 Cost:(0/5960)
10/13/2017 16:17:29,spid7s,Unknown,Requested by:
10/13/2017 16:17:29,spid7s,Unknown,Input Buf: Language Event: set nocount on<nl/>DECLARE @s NVARCHAR(2048)<nl/>DECLARE @msg NVARCHAR(2048)<nl/>declare @hid hierarchyid;<nl/><nl/>declare @n int = 50<nl/>declare @i int = 1<nl/>declare @int_id int = 0<nl/>declare @top int = 50<nl/><nl/>declare @nodes table (node_id uniqueidentifier<c/> int_id int)<nl/>de
10/13/2017 16:17:29,spid7s,Unknown,SPID: 67 ECID: 0 Statement Type: SELECT Line #: 67
10/13/2017 16:17:29,spid7s,Unknown,Owner:0x0000009E4F43DB00 Mode: IX       Flg:0x40 Ref:0 Life:02000000 SPID:67 ECID:0 XactLockInfo: 0x0000009CD6B14440
10/13/2017 16:17:29,spid7s,Unknown,Grant List 1:
10/13/2017 16:17:29,spid7s,Unknown,Grant List 0:
10/13/2017 16:17:29,spid7s,Unknown,PAGE: 16:1:172451              CleanCnt:4 Mode:IX Flags: 0x3
10/13/2017 16:17:29,spid7s,Unknown,Node:1
10/13/2017 16:17:29,spid7s,Unknown,
10/13/2017 16:17:29,spid7s,Unknown,Wait-for graph
10/13/2017 16:17:29,spid7s,Unknown,Deadlock encountered .... Printing deadlock information
10/13/2017 16:16:45,spid190,Unknown,DBCC TRACEON 3605<c/> server process ID (SPID) 190. This is an informational message only; no user action is required.
10/13/2017 16:16:45,spid190,Unknown,DBCC TRACEON 1204<c/> server process ID (SPID) 190. This is an informational message only; no user action is required



куда бежать?
13 окт 17, 18:09    [20868432]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Колян Козлов
Member

Откуда:
Сообщений: 98
 		можно одному из пользователей повысить приоритет дедлоков, тогда жертвой всегда будет выбираться кто-то другой.


А вообще смотреть что за запросы и оптимизировать их.
13 окт 17, 18:21    [20868453]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
 		Код роцедуры покажите. И граф дедлока здорового человека тоже не помешал бы.
13 окт 17, 18:29    [20868468]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Ролг Хупин
куда бежать?


Самое простое - замутить крит. секцию с помощью sp_getapplock.

ЗЫ. Проще тока выполнять операции с (tablockx, holdlock). Но это слишком банально.
13 окт 17, 19:36    [20868532]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
iii2
Member

Откуда:
Сообщений: 202
 		Может там просто индексов не хватает...
14 окт 17, 10:51    [20869323]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
 		Ну и план выполнения
14 окт 17, 12:02    [20869386]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Mr. X
Guest 		Ролг Хупин,

Решение проблемы по предотвращению deadlocks вытекает из природы их возникновения, которая априори известна. Поэтому теоретический посыл один, ИМХО, - "обходить" ресурсы, нужные всем запросам (не только тем, которые участвуют в данном deadlock), в одном направлении.
Практическое решение указанного посыла состоит в корректировке запросов и, возможно, индексов. Реже требуются архитектурные переделки в виде, например, большей нормализации.
Для того, что бы понять как подобный обход протекает в каждом конкретном случае нужны, как минимум, тексты запросов и актуальные планы их выполнения в момент возникновения deadlock (requested above).

Для того, что бы как-то уживаться с deadlocks можно действовать от обратного - не можем предотвратить, давайте контролировать. Не лучший посыл, ИМХО, но приемлемый в том случае, если ни что из выше перечисленного не помогло или не приемлемо в силу ряда причин - например, архитектурные переделки слишком затратны по сравнению с теми проблемами, которые доставляют deadlocks.
Практическое решение указанного посыла состоит в обработке ошибки 1205 и перезапуске транзакции оказавшейся deadlock victim.
Тут есть возможность регулирования того, какая транзакция будет выбрана в качестве жертвы: предоставить этот вопрос решать соответствующему функционалу MS SQL или использовать настройку сессии DEADLOCK_PRIORITY.

PS: данный пост не претендует на полноту раскрытия темы: what is a deadlock and what to do with it.
14 окт 17, 16:08    [20869639]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

Откуда:
Сообщений: 3589
 		Ролг Хупин,

У вас там читатели с писателями... как насчет включить RCSI ?
14 окт 17, 17:34    [20869706]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Eleanor
Ролг Хупин,

У вас там читатели с писателями... как насчет включить RCSI ?


Ролг Хупин
Несколько клиентов вызывают одну и ту же процедуру, котрая читает и удаляет из таблицы т1, читает и вставляет в т2


Для вас, Козлов, русский язык придумали.
15 окт 17, 10:22    [20870291]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

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

Для вас, Козлов, в дедлоке фигурируют IX, S, IX, S
15 окт 17, 12:53    [20870428]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

Откуда:
Сообщений: 3589
 		PAGE: 16:1:172451
Owner: Mode: IX SPID:67
Requested by: Mode: S SPID:190

PAGE: 16:1:172451
Owner: Mode: IX SPID:190
Requested by: Mode: S SPID:67

2 процесса мучают одну страницу в одной таблице. Сначала они решили помучать её на уровне разных строк, потом на уровне целой страницы.
В зависимости от реальных запросов:
- S, возможно, не конвертируется впоследствии в X, тогда RCSI подходит.
- S, возможно, можно спустить с уровня страницы на уровень разных строк с помощью индексов, rowlock
15 окт 17, 13:52    [20870524]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
LoopN
Guest
Eleanor
PAGE: 16:1:172451
Owner: Mode: IX SPID:67
Requested by: Mode: S SPID:190

PAGE: 16:1:172451
Owner: Mode: IX SPID:190
Requested by: Mode: S SPID:67

2 процесса мучают одну страницу в одной таблице. Сначала они решили помучать её на уровне разных строк, потом на уровне целой страницы.
В зависимости от реальных запросов:
- S, возможно, не конвертируется впоследствии в X, тогда RCSI подходит.
- S, возможно, можно спустить с уровня страницы на уровень разных строк с помощью индексов, rowlock

S совместимы между собой, перед X ставится не S а U блокировка. RCSI это вообще ппц - провисание производительности при изменениях данных (притом при delete в несколько миллионов может быть увеличение времени выполнения с 20сек до 3часов) , и это ради обхода одной блокировки?

Тут нужен нормальный файл xdl чтобы разобраться что происходит.
15 окт 17, 14:24    [20870601]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4431
LoopN
Eleanor
PAGE: 16:1:172451
Owner: Mode: IX SPID:67
Requested by: Mode: S SPID:190

PAGE: 16:1:172451
Owner: Mode: IX SPID:190
Requested by: Mode: S SPID:67

2 процесса мучают одну страницу в одной таблице. Сначала они решили помучать её на уровне разных строк, потом на уровне целой страницы.
В зависимости от реальных запросов:
- S, возможно, не конвертируется впоследствии в X, тогда RCSI подходит.
- S, возможно, можно спустить с уровня страницы на уровень разных строк с помощью индексов, rowlock

S совместимы между собой, перед X ставится не S а U блокировка. RCSI это вообще ппц - провисание производительности при изменениях данных (притом при delete в несколько миллионов может быть увеличение времени выполнения с 20сек до 3часов) , и это ради обхода одной блокировки?

Тут нужен нормальный файл xdl чтобы разобраться что происходит.


Для меня это стрёмно, одна из баз, в которой надо это использовать - большая, там сотни млн записей в этих таблицах.
15 окт 17, 17:09    [20870817]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4431
 		"Несколько клиентов вызывают одну и ту же процедуру, котрая читает и удаляет из таблицы т1, читает и вставляет в т2"

Я для чего это замутил:
есть такая структура:

main->1:N-> t1
t1->t1_text (varchar(MAX))
t1->t1_value(sql_variant)

в t1 ПК id identity int, и чтобы не упереться в переполнение решил сделать второй набор таких же таблиц мастер t2 с подчиненными, там identity bigint, и добавил логику в процедуры и триггеры, чтобы новые записи писались и апдейтились в новом наборе, а читались из нового и старого.
Т.е. в общем случае подчиненные записи для main находятся в одном из наборов t1 или t2 с подчиненными

main->1:N-> t1
t1->t1_text (varchar(MAX))
t1->t1_value(sql_variant)
main->1:N-> t2
t2->t2_text (varchar(MAX))
t2->t2_value(sql_variant)

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

Вот при такой логике и клинит
15 окт 17, 17:19    [20870826]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ролг Хупин
"Несколько клиентов вызывают одну и ту же процедуру, котрая читает и удаляет из таблицы т1, читает и вставляет в т2"

Я для чего это замутил:
есть такая структура:

main->1:N-> t1
t1->t1_text (varchar(MAX))
t1->t1_value(sql_variant)

в t1 ПК id identity int, и чтобы не упереться в переполнение решил сделать второй набор таких же таблиц мастер t2 с подчиненными, там identity bigint, и добавил логику в процедуры и триггеры, чтобы новые записи писались и апдейтились в новом наборе, а читались из нового и старого.
Т.е. в общем случае подчиненные записи для main находятся в одном из наборов t1 или t2 с подчиненными

main->1:N-> t1
t1->t1_text (varchar(MAX))
t1->t1_value(sql_variant)
main->1:N-> t2
t2->t2_text (varchar(MAX))
t2->t2_value(sql_variant)

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

Вот при такой логике и клинит
Партизан 80го уровня? Не лень было сочинение писать вместо того, чтобы код привести?
15 окт 17, 17:57    [20870858]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

Откуда:
Сообщений: 3589
LoopN
RCSI это вообще ппц - провисание производительности при изменениях данных (притом при delete в несколько миллионов может быть увеличение времени выполнения с 20сек до 3часов)

Этот ппц - это уже дефолтный уровень изоляции в Azure. Думаю, дефолтный RCSI - это будущее Sql Server.
15 окт 17, 23:18    [20871262]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Eleanor
LoopN
RCSI это вообще ппц - провисание производительности при изменениях данных (притом при delete в несколько миллионов может быть увеличение времени выполнения с 20сек до 3часов)

Этот ппц - это уже дефолтный уровень изоляции в Azure. Думаю, дефолтный RCSI - это будущее Sql Server.
Ага, там такой минорный оверхед всего в 14 байт на запись. Подумаешь, какая мелочь.
16 окт 17, 00:56    [20871362]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Eleanor
LoopN
RCSI это вообще ппц - провисание производительности при изменениях данных (притом при delete в несколько миллионов может быть увеличение времени выполнения с 20сек до 3часов)

Этот ппц - это уже дефолтный уровень изоляции в Azure. Думаю, дефолтный RCSI - это будущее Sql Server.


Т.е. "читатель" будет принимать решение "об удалении/вставке" на основе снэпшота?
Да вы бредите, Козлов.
16 окт 17, 05:33    [20871426]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4431
 		Гавриленко Сергей Алексеевич,

вы правы, попробую минимизировать и привести код, иначе в оригинале там слишком много кода, в т.ч. и лишнего, мало кто будет читать 3 км текстов
16 окт 17, 09:19    [20871613]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

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

Козлов, вы не в состоянии даже в дедлоке прочитать IX-S...
Код ТС так и не привел, и дедлок явно происходит не том, о чем он думает и описывает. А вы продолжаете цепляться за текстовое описание.
16 окт 17, 10:59    [20872022]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

Откуда:
Сообщений: 3589
Гавриленко Сергей Алексеевич
Ага, там такой минорный оверхед всего в 14 байт на запись. Подумаешь, какая мелочь.

Это разговор с заранее известными ответами:
- А Oracle, Postgres - это версионники, и нормально работают
- Ну они же исходно так проектировались...
В любом случае у вас наверняка есть БД, где включен RCSI.

В крайнем случае, если ТС не разберется с дедлоками, он легко сам в фоне перенесет все записи в т2 и удалит т1.
16 окт 17, 11:17    [20872111]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Eleanor
Гавриленко Сергей Алексеевич
Ага, там такой минорный оверхед всего в 14 байт на запись. Подумаешь, какая мелочь.

Это разговор с заранее известными ответами:
- А Oracle, Postgres - это версионники, и нормально работают
- Ну они же исходно так проектировались...
В любом случае у вас наверняка есть БД, где включен RCSI.

В крайнем случае, если ТС не разберется с дедлоками, он легко сам в фоне перенесет все записи в т2 и удалит т1.


Т.е. а оракале для вас, Козлов, логику отменили?
А вместе с ней и deadlock?

ЗЫ.
https://oracle-base.com/articles/misc/deadlocks
16 окт 17, 11:30    [20872164]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
Eleanor
Member

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

Козлов, вам когда-нибудь надоест игнорировать, что дедлок возник на S блокировках?
Зачем-то прислали ссылку на известный факт, что в версионниках тоже бывают дедлоки, на писателях.

PS: По квалификации вы, откровенно говоря, не о-о, чтобы словом "Козлов" разбрасываться.
16 окт 17, 11:52    [20872250]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Ролг Хупин
Гавриленко Сергей Алексеевич,

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


Ваще то, в 100500% случаев deadlock возникает на 1 (одном) и том же стейтменте из 100500км кода.

Дык, лучше не упрощать.
16 окт 17, 12:06    [20872317]     Ответить | Цитировать Сообщить модератору
 Re: Как избежать Deadlock?  [new]
aleks222
Guest
Eleanor
aleks222,

Козлов, вам когда-нибудь надоест игнорировать, что дедлок возник на S блокировках?
Зачем-то прислали ссылку на известный факт, что в версионниках тоже бывают дедлоки, на писателях.

PS: По квалификации вы, откровенно говоря, не о-о, чтобы словом "Козлов" разбрасываться.


Дарагой Козлов, только для вас: deadlock "только на S" невозможен.

ЗЫ. Я ознакомился с вашим списком сообщений на форумах - вы типичный пустобрех.
16 окт 17, 12:09    [20872331]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить