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

Откуда: Москва
Сообщений: 1319
Блог
У кого с какой частотой возникают?
30 мар 05, 15:30    [1427096]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Вообще не возникают :-)
30 мар 05, 15:56    [1427292]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
Я сейчас с такими дедлоками борюсь. Таких дедлоков в сутки происходит немало, недавно еще один тип появился - между другим селектом и update-ами. Я исправление этих дедлоков уже похоже написал, скоро буду тестировать. Но все равно - у кого такие дедлоки были, как их исправляли?
30 мар 05, 16:26    [1427508]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Alex M
Member

Откуда:
Сообщений: 74
У меня были - см. ветки со словом deadlock в заглавии.

Но у меня в селектах был хинт with (UPDLOCK).

Я его убрал - пока новых дедлоков не было.

Мне эта проблема интересна - приведите тексты селекта и update.
Поясните - находятся ли эти операторы внутри транзакций или выполняются самостоятельно.

Включите флаг 1204 и приведите текст того что пишется в лог файл.
30 мар 05, 16:39    [1427589]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Угу, вот хороший пример сомнительного обращения с хинтами в селектах: https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=170465&hl=

Присоединяюсь к вопросам Alex M насчет уточнения деталей ситуации.
30 мар 05, 16:52    [1427658]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
У меня UPDLOCK не стоит, селект выполняется при read committed. Проблема заключается в том, что и селект и update (вообще-то это ХП) работают с таблицей которая много где используется. Когда смотрю профайлером на события приводящие к дедлокам, времена вызовов ХП для селекта и для update совпадают с точностью до миллисекунды - это максимальная точность для профайлера. Идет селект из таблицы. В это время делается update на одну из записей этой же таблицы которая удовлетворяет условию селекта, но до которой селект еще не успел дойти. Селект доходит до блокированной записи, ждет. В это время ХП с update-ом пытается изменить запись на которую селект положил S-блокировку. Дедлок. Лечу исправлением порядка update-ов. Селект находится не в транзакции, update выполняются в транзакции.
30 мар 05, 17:06    [1427735]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Возможно это бага. Если селект идет с букмарком, а в апдейте участвуют поля кластерного индекса.
30 мар 05, 23:23    [1428476]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Граф дедлока в студию.
31 мар 05, 11:11    [1429170]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
Deadlock:
Deadlock encountered .... Printing deadlock information
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Wait-for graph
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Node:1
2005-03-10 07:40:47.63 spid4     KEY: 5:2002106173:1 (47008b4a43cb) CleanCnt:1 Mode: U Flags: 0x0
2005-03-10 07:40:47.63 spid4      Grant List 0::
2005-03-10 07:40:47.63 spid4      Grant List 1::
2005-03-10 07:40:47.63 spid4        Owner:0x52bcebe0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:179 ECID:0
2005-03-10 07:40:47.63 spid4        SPID: 179 ECID: 0 Statement Type: SELECT Line #: 116
2005-03-10 07:40:47.63 spid4        Input Buf: RPC Event: sp_executesql;1
2005-03-10 07:40:47.63 spid4      Requested By: 
2005-03-10 07:40:47.63 spid4        ResType:LockOwner Stype:'OR' Mode: X SPID:135 ECID:0 Ec:(0x5BC35510) Value:0x6125d440 Cost:(0/8B8)
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Node:2
2005-03-10 07:40:47.63 spid4     KEY: 5:2002106173:1 (4a0056922939) CleanCnt:1 Mode: X Flags: 0x0
2005-03-10 07:40:47.63 spid4      Grant List 0::
2005-03-10 07:40:47.63 spid4        Owner:0x6eca4d40 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:135 ECID:0
2005-03-10 07:40:47.63 spid4        SPID: 135 ECID: 0 Statement Type: UPDATE Line #: 128
2005-03-10 07:40:47.63 spid4        Input Buf: Language Event: exec dbo.DoCancel @OrderNumber=1515082
2005-03-10 07:40:47.63 spid4      Requested By: 
2005-03-10 07:40:47.63 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x63683550) Value:0x6125c040 Cost:(0/0)
2005-03-10 07:40:47.63 spid4     Victim Resource Owner:
2005-03-10 07:40:47.63 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x63683550) Value:0x6125c040 Cost:(0/0)
В sp_executesql вызывается ХП в которой все что есть - это select. Никаких хинтов, кроме NOLOCK на нескольких таблицах из запроса, не стоит.
31 мар 05, 12:20    [1429560]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Приведите действия обоих конфликтующих процессов: что выполняется в sp_executesql - целиком, не одну-единственную строку 116, и текст DoCancel тоже целиком.

Также посмотрите, какой объект выдаст запрос
select object_name(2002106173)
в базе, имя которой можно узнать запросом
select db_name(5)

Приведите скрипт этой таблицы со всеми подробностями: структура, индексы, констрейнты, триггера.
31 мар 05, 12:42    [1429672]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
KOLCHOZ_POSTEVENT
Guest
Отчего-ж Update не может закрыть кран селекту?
Если он долбит по почти всей табле,даже не по всей,выставится иксклюсивный лок номер 257 и всё,веники,никто к табле не подойдёт.
А Update,ух это зверь!
Я-то,по простоте душевной,до позавчера,полагал,что самый страшный зверь-это делит.Хрена!Пришлось позавчера заменить массивный апдейт на делит-инсерт,так два массивных инсерта(один во времянку,второй из времянки-после делита)и один массивный делит взяли времени в 4 раза меньше,чем один апдейт.
А локи-локи это из-за медленности.
31 мар 05, 12:53    [1429744]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
KOLCHOZ_POSTEVENT
Я-то,по простоте душевной,до позавчера,полагал,что самый страшный зверь-это делит.Хрена!Пришлось позавчера заменить массивный апдейт на делит-инсерт,так два массивных инсерта(один во времянку,второй из времянки-после делита)и один массивный делит взяли времени в 4 раза меньше,чем один апдейт.
Это скорее всего из-за расщепления страниц при высоком филфакторе... Кстати, для прояснения картины, какой он на таблицах, где пришлось менять update на delete/insert ?
31 мар 05, 13:03    [1429803]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
Update меняет 2-4 записи из примерно полумиллиона записей в таблице. Здесь проблема проявляется из-за большого количества обращений к таблице - выбрал в профайлере произвольную секунду, и за секунду насчитал 45 вызовов ХП, часть из них меняет эту таблицу, другая часть читает из этой таблицы.

2 GreenSunrise
Это я уже давно сделал, и проблема с дедлоками, как показывают сегодняшние тесты, решена. Просто хочется понять кто как часто встречается с такой проблемой и как решает.
31 мар 05, 13:29    [1429925]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
KOLCHOZ_POSTEVENT
Guest
Я помню Фила Эспозито-он играл под номером 77,после его удара-шайба просто исчезала.Как их Третьяк потом ловил-до сих пор загадка.А к воротам он шёл без обводки-его итак никто не мог остановить...а вот Фил Фактор-не было такого.
Ну попало под раздачу 65000 записей,каждая...ой страх,ну в страницу памяти,не меньше,юзер звонит-"спасай",ну,как говорится-чем богаты.
31 мар 05, 13:34    [1429961]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
andsm
Просто хочется понять кто как часто встречается с такой проблемой и как решает.
Практически не встречаюсь (за исключением аудита чужих проектов), ну, а решаю в основном согласно рекомендациям BOL и статьи Что такое взаимоблокировки и как с ними бороться.
31 мар 05, 13:36    [1429973]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Alex M
Member

Откуда:
Сообщений: 74
To Crimean.

Вы пишете:
Возможно это бага.

Прошу пояснить подробнее что это за баг, при каких условиях он проявляется, признан ли он Майкрософт и есть ли от него лекарство.

Спасибо.
31 мар 05, 13:40    [1430003]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Crimean
Member

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

Я описал проявление. В результате или дедлок или не могу читать с нолоком во время перемещения данных. Заявить пытались. В МС сказали "бай дизайн", что обычно означает "пнх". Мы переписали в итоге немного кода. Чуть-чуть попустило.
31 мар 05, 16:42    [1431015]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Alex M
Member

Откуда:
Сообщений: 74
to Crimean.

Вот так всегда - покажут конфетку, а попробовать не дадут :-(

ПРОШУ (если это не секрет) пояснить какие идеи были положены в основу переписывания кода.

Спасибо.
4 апр 05, 11:14    [1437979]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
gipa
Guest
Знакомая ситуация. Bug by design. ;-)
У коллег по работе возникало в тестовой фазе достаточно часто. В итоговом решении используется select с хинтом nolock и при возникновении ошибки "Could not continue scan with NOLOCK due to data movement" запрос повторяется еще раз.
Появляется в случае, если select использует индекс для выборки, но индекс содержит не все поля, возвращаемые запросом (т.е. select c bookmark). В свою очередь, update должен обновлять поля, входящие в индекс, используемый селектом.
В инете была хорошая статья, где такая ситуация описывалась, но не могу ее найти. Если кратко, то деадлок возникает, когда (надеюсь, вспомню правильно):
П1 - select, П2 - update
П1 находит ключ по индексу и накладывает на этот ключ "S" lock.
П2 находит запись по кластерному индексу и обновляет данные, накладывая при этом "X" lock
П1 пытается прочитать недостающие данные и наложить "S" lock, что ему не удается посколько П2 использовал уже "X" lock.
П2 пытается изменить данные индекса и наложить "X" lock на ключ, что невозможно, так как ключ уже имеет блокировку "S"
П1 и П2 взаимно блокировали друг друга на одной таблице и без использования транзакция

Еще один из способов борьбы с такими deadlock - устранение bookmark при select, т.е. расширение индекса, используемого selectом.
4 апр 05, 23:02    [1440172]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock-и между select и update  [new]
Alex M
Member

Откуда:
Сообщений: 74
Спасибо, gipa.

Самое противное в этой ситуации, что НЕТ совокупности формальных правил, соблюдая которые, разработчик мог бы гарантировать себя от возникновения (таких) дедлоков.

Или таки есть?
- Одно правило (устранение букмарков в селектах) gipa уже нащупал.

Может быть корифеи напрягуться и на зависть BOLу родят дополнительный список правил для избежания таких дедлоков.

Пока (насколько я могу судить) корифеи в таких случаях применяют паллитивные средства типа try-except в вызывающей программе и повторного вызова отстреленного процесса.
5 апр 05, 08:29    [1440368]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить