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

Откуда:
Сообщений: 12310
Совсем упрощенная ситуация выглядит так:
create table t1 (

col1 uniqueidentifier not null unique nonclustered,
col2 varchar(10),
col3 datetime default getdate()
)
go
create trigger trg_upd_t1 on t1 for update
as
begin
update t1 set col3 = getdate() from t1, inserted where t1.col1 = inserted.col1
end
go

То есть в чистом виде простенькое отслеживание время последнего изменения записи.
Вставляем одну запись:
insert into t1 (col1, col2) values ('B45013A5-529D-4033-ADFA-C6E27200DDC2', 'a')

Затем с ДВУХ коннектов запускаем запрос:
while 1 = 1

update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'

Один из коннектов отваливается по дедлоку.

Запуск с трейсом дает следующее описание дедлока:
Wait-for graph

Node:1
RID: 7:1:120:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x42bdf400 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 4
Input Buf: Language Event: while 1 = 1
update t1 set col2 = 'c' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'


Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x42D49518) Value:0x42be33a0 Cost:(0/0)
Node:2
KEY: 7:2107154552:2 (61021e30bc5c) CleanCnt:1 Mode: U Flags: 0x0
Grant List 0::
Owner:0x42be3280 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0
SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: while 1 = 1
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'

Если я правильно понимаю накладывание блокировок в этом случае, то происходит следующее - при апдейте накладывается update lock, что по сути является shared lock'ом на время отбора записей для изменения и последующей эскалации до exclusive, когда уже идет собственно изменение. Поскольку индекс некластерный, то логичен лок строки (RID: 7:1:120:0) для первого процесса и лок индекса (KEY: 7:2107154552:2 (61021e30bc5c)) для второго процесса.

Собственно, наличие именно update lock'ов должно в данном случае гарантировать отсутствие дедлоков. Более того, при отсутствии триггера его и не происходит.

Вопрос - почему наличие триггера с апдейтом ТОЙ ЖЕ САМОЙ СТРОКИ, что в исходном апдейте, приводит к дедлоку? Откуда берутся дополнительные блокировки ресурсов? бОльшие, чем в оригинальном апдейте?

Вопрос второй. В момент выполнения триггера на строку уже наложен exclusive lock, что легко проверяется добавлением строки exec sp_lock первой строчкой триггера. Логично - у нас after триггер, поэтому данные уже изменены, а значит, эксклюзивно залочены. Так почему же возникает дедлок?
30 мар 04, 19:37    [604650]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Сейчас у меня это исправлено на update t1 with (XLOCK), что спасает положение. Является ли этот способ правильным и есть ли другие (правильные ;-))?
30 мар 04, 20:00    [604665]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
ПО моему скромному размению - XLOCK тут правильно. Почему же возникает дедлок - не понимаю... Триггер же считается частью транзакции, поэтому не вижу проблем которые приводят к дедлоку. Посмотрим что более грамотные товарисчи скажут...
30 мар 04, 20:27    [604681]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Об этом уже вскользь говорили, когда поднималась тема изменения "своей" таблицы в триггере. Тогда кто-то (простите) упомянул, что такие дедлоки являются следствием изменения уровня блокировки.

Позволю себе:

2004-03-30 19:47:52.97 spid58    DBCC TRACEON 1204, server process ID (SPID) 58.

2004-03-30 19:47:57.05 spid4
Deadlock encountered .... Printing deadlock information
2004-03-30 19:47:57.05 spid4
2004-03-30 19:47:57.05 spid4 Wait-for graph
2004-03-30 19:47:57.05 spid4
2004-03-30 19:47:57.05 spid4 Node:1
2004-03-30 19:47:57.05 spid4 RID: 9:1:60148:0 CleanCnt:1 Mode: X Flags: 0x2
2004-03-30 19:47:57.05 spid4 Grant List 0::
2004-03-30 19:47:57.05 spid4 Owner:0x450cb980 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2004-03-30 19:47:57.05 spid4 SPID: 62 ECID: 0 Statement Type: UPDATE Line #: 4
2004-03-30 19:47:57.05 spid4 Input Buf: Language Event: while 1 = 1
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'




2004-03-30 19:47:57.05 spid4 Requested By:
2004-03-30 19:47:57.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:59 ECID:0 Ec:(0x4862BA60) Value:0x445f31a0 Cost:(0/0)
2004-03-30 19:47:57.05 spid4
2004-03-30 19:47:57.05 spid4 Node:2
2004-03-30 19:47:57.05 spid4 KEY: 9:1997522120:2 (61021e30bc5c) CleanCnt:1 Mode: U Flags: 0x0
2004-03-30 19:47:57.05 spid4 Grant List 0::
2004-03-30 19:47:57.05 spid4 Owner:0x4d63c6e0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:59 ECID:0
2004-03-30 19:47:57.05 spid4 SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
2004-03-30 19:47:57.05 spid4 Input Buf: Language Event: while 1 = 1
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'




2004-03-30 19:47:57.05 spid4 Requested By:
2004-03-30 19:47:57.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x473A9A60) Value:0x51d9efa0 Cost:(0/F4)
2004-03-30 19:47:57.05 spid4 Victim Resource Owner:
2004-03-30 19:47:57.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:59 ECID:0 Ec:(0x4862BA60) Value:0x445f31a0 Cost:(0/0)


Фактически, у нас на один ресурс две U блокировки:

2004-03-30 19:47:57.05 spid4 Requested By:
2004-03-30 19:47:57.05 spid4 ResType:LockOwner Stype:'OR' Mode: U

2004-03-30 19:47:57.05 spid4 Requested By:
2004-03-30 19:47:57.05 spid4 ResType:LockOwner Stype:'OR' Mode: U

Оба держат "U" , но Node 1 просит "X" блокировку, что делает ситуацию патовой, так как Node 2 думает, что Node 1 снимет "U" блокировку :) Выход - сразу ставить более высокий isolation level или указывать хинт, достаточно, вроде как, with (updlock)

Фух! Похоже, я попутно победил и свою проблему с дедлоками!
30 мар 04, 21:07    [604698]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
На самом деле я там немного напутал с описанием , но , похоже , угадал со следствием :)
В любом случае огромный мерси за пример!
Теперь будет болеть голова, как переписать пару десятков процедур...
Бо пример сильно показательный...

P.S.Нашел упоминание о конвертации блокировок:
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=74999&pg=-1#558015
30 мар 04, 21:15    [604703]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Кстати, а со вставкой аналогично не происходит?
30 мар 04, 21:22    [604704]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Слон
Member

Откуда:
Сообщений: 717
Причина тут проста. Так как нет индекса на Col1, то во время UPDATE происходит перебор всех записей. В момент перебора, второй процесс блокирует таблицу, отсюда и дедлок. Лечится добавлением индекса
CREATE UNIQUE NONCLUSTERED INDEX IX_t1 ON t1 (col1)


-- Слон
30 мар 04, 23:20    [604764]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Слон
Member

Откуда:
Сообщений: 717
Пардон, в момент UPDATE из триггера

-- Слон
30 мар 04, 23:21    [604766]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Лечится добавлением индекса
См. описание таблицы:
col1 uniqueidentifier not null unique nonclustered
31 мар 04, 10:31    [605203]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Фактически, у нас на один ресурс две U блокировки:
Правильно! Я об этом и говорю с самого начала. Именно наличие update lock'ов по словам Kalen Delaney должно спасать от возможности возникновения дедлоков. Пардоньте за длинную цитату, но я не знаю ссылки на онлайн этой книги:

Update locks

Update locks are not really a separate kind of lock, but rather are a hybrid of shared and exclusive locks. A transaction acquires this kind of lock when SQL Server executes a data-modification operation but first needs to perform a search to find the resource to modify. While SQL Server is searching, it doesn’t need to acquire an exclusive lock, it only needs the exclusive lock when the data to be changed is found. As SQL Server is searching, it acquires a shared lock on each resource it encounters and then determines whether it has found the data it is searching for. However, if SQL Server started out with a shared lock while searching, there are potential problems. A situation could occur where two processes were both searching for the same resource to modify (for example, the same customer row in the Customers table), using different access paths, and they could both reach the desired resource at the same time. If they both were acquiring shared locks on the data they were examining, they could both lock the resource they wanted to change, but before they made the modification they would have to change (or escalate) their lock to an exclusive lock. Since the other process would have a shared lock, no exclusive lock could be granted. Each process would have a shared lock, and each would try to change it to an exclusive lock, but neither could proceed because of the presence of the other. This is a deadlock situation, and I’ll talk a lot more about deadlock in a later chapter. In this case, because of update locks, a deadlock will NOT occur. If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires an update lock until it finds the data to modify. Update locks are compatible with share locks, but are not compatible with exclusive locks or other update locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an update lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could escalate its update lock to an exclusive lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change. In the output of the sp_lock procedure, a lock mode of ‘U’ indicates an update lock.

Так вот, насчет методов борьбы. Crimean, вы совершенно правы, можно добавлять менее страшный хинт updlock вместо xlock и это тоже помогает. Теперь я вообще в растерянности - что же, по умолчанию при выполнении update накладывается НЕ updlock????? Как тогда это согласуется с приведенными словами - "If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires an update lock until it finds the data to modify."?

со вставкой сейчас попробую.
31 мар 04, 10:59    [605288]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Снимаем триггер на таблицу t1 , делаем:

begin tran

update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'


Получаем sp_lock:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 

------ ------ ----------- ------ ---- ---------------- -------- ------

103 9 1114042975 0 PAG 1:662987 IX GRANT
103 9 1114042975 0 RID 1:662987:0 X GRANT
103 9 1114042975 0 TAB IX GRANT


Делаем

begin tran

select * from t1 with (updlock) where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'


Получаем

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 

------ ------ ----------- ------ ---- ---------------- -------- ------

103 9 1114042975 2 KEY (61021e30bc5c) U GRANT
103 9 1114042975 2 PAG 1:662989 IU GRANT
103 9 1114042975 0 PAG 1:662987 IU GRANT
103 9 1114042975 0 RID 1:662987:0 U GRANT
103 9 1114042975 0 TAB IX GRANT


Еще раз перечитываем Kalen Delaney , еще раз пересматриваем репорт про DeadLock и ... получается , что update where (и вобще update) таки сначала shared локи накладывает? Только это никому таки не показывается? Как иначе объяснить, что введение хинта with (updlock) в исходный пример убирает дедлоки? Особенно в свете приведенной цитаты? Или цитата таки не совсем корректна и сначала U , а потом X приводит к дедлоку?
31 мар 04, 12:04    [605492]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Еще интереснее!
Работаем без триггеров (считаем триггер процедурой и просто имитируем его сами)
Меняем тест на вот такой:

declare @i int


while 1 = 1 begin

begin tran
select @i = count(*) from t1 (updlock) where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
commit

end


Дедлоков нет!
"Возвращаем" триггер:

declare @i int


while 1 = 1 begin

begin tran
-- select @i = count(*) from t1 (updlock) where col1 = %af_src_str_0

update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
commit

end


Дедлоки! Как интересно!
Меняем пример вот так:

declare @i int


while 1 = 1 begin

begin tran
-- select @i = count(*) from t1 (updlock) where col1 = %af_src_str_0

update t1 with (updlock) set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
commit

end

-- rollback


Дедлоков нет? Нет!
31 мар 04, 12:12    [605517]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Слон
Member

Откуда:
Сообщений: 717
См. описание таблицы:
col1 uniqueidentifier not null unique nonclustered


Так создается UNIQUE CONSTRAINT, а не UNIQUE INDEX.

-- Слон
31 мар 04, 12:16    [605531]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Идем дальше! Сравниваем update t1 vs update t1 with (updlock) получаем:


Без хинта
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------

103 9 1114042975 0 PAG 1:662987 IX GRANT
103 9 1114042975 0 RID 1:662987:0 X GRANT
103 9 1114042975 0 TAB IX GRANT

С хинтом
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------

103 9 1114042975 0 PAG 1:662987 IX GRANT
103 9 1114042975 0 RID 1:662987:0 X GRANT
103 9 1114042975 0 TAB IX GRANT
(плюс добавка!)
103 9 1114042975 2 KEY (61021e30bc5c) U GRANT
103 9 1114042975 2 PAG 1:662989 IU GRANT


Вот в добавке, похоже, и сидит причина дедлока или его отсутствия.
31 мар 04, 12:16    [605536]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

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

sp_helpindex t1 выдает

index_name                                                                                                                       index_description                                                                                                                                                                                                  index_keys                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UQ__t1__435B1698 nonclustered, unique, unique key located on PRIMARY col1
31 мар 04, 12:17    [605539]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Glory, ау! Help! :)
31 мар 04, 12:23    [605558]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Glory
Member

Откуда:
Сообщений: 104764
Тут я. Только пока сказать ничего не могу :(. Тоже экспериментирую.
31 мар 04, 12:26    [605564]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Вот это вот меня убило и в землю закопало:
while 1 = 1 begin

begin tran
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
update t1 set col2 = 'b' where col1 = 'B45013A5-529D-4033-ADFA-C6E27200DDC2'
commit
end

ЭТО! БЕЗ ТРИГГЕРА! ДАЕТ! ДЕДЛОКИ!

Таки что, начинать теперь в ЛЮБОМ апдейте ставить with (updlock), чтобы проимитировать задекларированное поведение сиквела?

Кстати, где в официальной документации написано, что updlock ставится? Книжка - енто хорошо, но хочется BOL или MSDN...

Насчет любого вопрос, может и спорный. Но в данном случае имхо лучше перестраховаться, чем потом искать проблему в готовой здоровенной системе. Я ж не предлагаю exclusive lock на всю базу бабахать ;-)))
31 мар 04, 12:51    [605658]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Об описании поведения в документации: Hints
Default setting - Take shared locks.
Я не догоняю - это дефолтные опции для ВСЕХ операций? Если для всех, тогда вопрос о различии ожидаемого и реального поведения снимается, но возникает другой - о наличии здравого смысла разработчиков.
31 мар 04, 13:11    [605716]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Кстати, прошу прощения за неправильное использование термина "эскалация". shared->exclusive и т.д. В контексте данного вопроса имелось в виду конвертирование, конечно. Эскалация - вопрос отдельный и к данной теме в чистом виде отношения не имеет.
31 мар 04, 13:40    [605799]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
RatTail
Member [заблокирован]

Откуда: Z
Сообщений: 4517
Я не догоняю - это дефолтные опции для ВСЕХ операций?

READ COMMITTED
This option is the SQL Server default.

Да, для всех операций..... и BOL этого не скрывает......
31 мар 04, 14:15    [605934]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
МуМу
Member

Откуда:
Сообщений: 1120
То GreenSunrise .

Сделай индекс кластерным запусти свои примеры и увидишь что деадлоки исчезнут.

Да и кстати а какой размер таблицы(больше 1 записи?) в которой ты апдейтишь записи?
31 мар 04, 14:22    [605955]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 MyMy
А если есть и кластерный и некластерный индексы, тада чо?
С только кластерным , возможно , проблема и уйдет.
Пример хорош такой , какой он есть.
И сколько записей в таблице, на самом деле, неважно.
31 мар 04, 14:27    [605976]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
МуМу
Member

Откуда:
Сообщений: 1120
К сожалению плохо знаю английский поэтому редко обращаюсь к БОЛу. Все проверяю обычно экспериментальным путем... Могу сказать лишь одно - если есть на таблице PK или хотя бы один кластерный индекс то в этом случае при апдейте записи накладывается эксклюзивная блокировка.
31 мар 04, 14:38    [606027]     Ответить | Цитировать Сообщить модератору
 Re: Причины deadlock'а  [new]
Crimean
Member

Откуда:
Сообщений: 13148
У нас в системе нет таблиц без кластерных индексов , тем не менее описанная проблема присуща .
31 мар 04, 14:43    [606043]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить