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

Откуда:
Сообщений: 77
Есть таблица

CREATE TABLE t_Value (
  id int, // Primary key, autoincrement
  foreign_id int, // Foreign key
  value varchar(max)
)


И запрос

UPDATE t_Value set value = '0' where foreign_id = @someId


На котором довольно часто воспроизводится дедлок. Вычитал в сети, что это обычное дело, когда делается UPDATE с предикатом по некластеризованному индексу. Но так и не врубился в механику возникновения этого дедлока. Здесь http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/ вроде не совсем та ситуация, а тут http://www.intertech.com/Blog/sql-server-deadlocks-caused-by-clustered-index-scan/ причина вообще толком не разъяснена.

Может кто-нибудь объяснить ?
22 июл 15, 13:06    [17920987]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
Glory
Member

Откуда:
Сообщений: 104751
elwood
Может кто-нибудь объяснить ?

Для этого берут граф дедлока, а не гадают на кофейной гуще
22 июл 15, 13:15    [17921040]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
elwood
Member

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

могу его предоставить. Там ничего интересного: написано, что оба апдейта ожидают PK (причём оба же и захватили PK в exclusive):

<resource-list>
   <keylock hobtid="72057599055691776" dbid="7" objectname="dbo.t_tag" indexname="PK_t_tag" id="lock6e73f80" mode="X" associatedObjectId="72057599055691776">
    <owner-list>
     <owner id="process6ca5b88" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6a28088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057599055691776" dbid="7" objectname="dbo.t_tag" indexname="PK_t_tag" id="lock6837d00" mode="X" associatedObjectId="72057599055691776">
    <owner-list>
     <owner id="process6a28088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6ca5b88" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
22 июл 15, 13:38    [17921189]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
elwood
могу его предоставить
Вот и предоставьте. Только полностью.
22 июл 15, 13:43    [17921220]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
elwood
Member

Откуда:
Сообщений: 77
invm, вот полный

<deadlock-list>
 <deadlock victim="process6a28088">
  <process-list>
   <process id="process6a28088" taskpriority="0" logused="2968" waitresource="KEY: 7:72057599055691776 (9f79b19815cb)" waittime="5469" ownerId="6078872" transactionname="implicit_transaction" lasttranstarted="2015-07-21T16:11:54.680" XDES="0x992863b0" lockMode="U" schedulerid="2" kpid="1924" status="suspended" spid="78" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-07-21T16:12:04.920" lastbatchcompleted="2015-07-21T16:11:54.863" clientapp="jTDS" hostname="123" hostpid="123" loginname="root" isolationlevel="read committed (2)" xactid="6078872" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="dbo.sp_SetTags" line="57" stmtstart="4146" stmtend="4362" sqlhandle="0x03000700bdfa4a5325220601dca400000100000000000000">
update dbo.t_tag set archive = 1 where foreign_nmb = @foreign_nmb and tag_type_nmb = 3     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x01000700b2622e25f03d26f3000000000000000000000000">
EXECUTE dbo.sp_SetTags @foreign_nmb= 239794 ,@value= N&apos;Some text&apos;</frame>
    </executionStack>
    <inputbuf>
EXECUTE dbo.sp_SetTags @foreign_nmb= 239794 ,@value= N&apos;Some text&apos;</inputbuf>
   </process>
   <process id="process6ca5b88" taskpriority="0" logused="3612" waitresource="KEY: 7:72057599055691776 (6635da0ff0c7)" waittime="5469" ownerId="6078914" transactionname="implicit_transaction" lasttranstarted="2015-07-21T16:11:54.690" XDES="0x835f7600" lockMode="U" schedulerid="1" kpid="5588" status="suspended" spid="84" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-07-21T16:12:04.920" lastbatchcompleted="2015-07-21T16:11:54.850" clientapp="jTDS" hostname="123" hostpid="123" loginname="root" isolationlevel="read committed (2)" xactid="6078914" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
    <executionStack>
     <frame procname="dbo.sp_SetTags" line="57" stmtstart="4146" stmtend="4362" sqlhandle="0x03000700bdfa4a5325220601dca400000100000000000000">
update dbo.t_tag set archive = 1 where foreign_nmb = @foreign_nmb and tag_type_nmb = 3     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x01000700ae570b3a50036c9f000000000000000000000000">
EXECUTE dbo.sp_SetTags @foreign_nmb= 239795 ,@value= N&apos;Some text&apos;</frame>
    </executionStack>
    <inputbuf>
EXECUTE dbo.sp_SetTags @foreign_nmb= 239795 ,@value= N&apos;Some text&apos;</inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057599055691776" dbid="7" objectname="dbo.t_tag" indexname="PK_t_tag" id="lock6e73f80" mode="X" associatedObjectId="72057599055691776">
    <owner-list>
     <owner id="process6ca5b88" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6a28088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057599055691776" dbid="7" objectname="dbo.t_tag" indexname="PK_t_tag" id="lock6837d00" mode="X" associatedObjectId="72057599055691776">
    <owner-list>
     <owner id="process6a28088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6ca5b88" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
22 июл 15, 13:54    [17921296]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
У вас поди в транзакции апдейт нескольких ключей в рандомном порядке.
22 июл 15, 13:59    [17921333]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
текст реально то какой ?
dbo.sp_SetTags
Да и запрос в примере не такой же как в графе. И ипсходя из количества парметров процедуры она еще чет делает,ане только флаг проставляет
22 июл 15, 14:25    [17921518]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
В процедуре sp_SetTags до проблемного update'а есть еще какие-либо манипуляции с t_tag?
22 июл 15, 14:30    [17921545]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
elwood
Member

Откуда:
Сообщений: 77
Может быть я чересчур упростил. На самом деле хранимка принимает 3 значения и каждое из них кладёт в строчку по координатам {foreign_nmb, tag_type_nmb}. tag_type_nmb может быть от 1 до 3. Каждое значение инсертится вот так:

if @link is null begin
    update dbo.t_tag set archive = 1 where foreign_nmb = @foreign_nmb and tag_type_nmb = 1
  end else begin
    if exists(select 1 from dbo.t_tag (nolock) where foreign_nmb = @foreign_nmb and tag_type_nmb = 1) begin
      update dbo.t_tag set value = @link, archive = 0
      where foreign_nmb = @foreign_nmb and tag_type_nmb = 1
    end else begin
      insert into dbo.t_tag (foreign_nmb, tag_type_nmb, value, archive)
      values (@foreign_nmb , 1, @link, 0)
    end
  end


Соответственно, tag_type_nmb для других значений другой - 2 и 3.

А дедлок возникает на первом апдейте который после if @link is null. Один процесс пытается обработать один foreign_nmb, второй - другой foreign_nmb.

Других участников нет

Поэтому и решил упростить до одного апдейта и одного значения
22 июл 15, 15:06    [17921721]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
elwood
Member

Откуда:
Сообщений: 77
У вас поди в транзакции апдейт нескольких ключей в рандомном порядке.

Возможно, вы правы. Но апдейтящиеся строчки всегда разные

Да и запрос в примере не такой же как в графе. И ипсходя из количества парметров процедуры она еще чет делает,ане только флаг проставляет


Да, пример не такой, я его пытался сделать упрощённым

invm
В процедуре sp_SetTags до проблемного update'а есть еще какие-либо манипуляции с t_tag?


Нет, есть после, но до этого дело не доходит.
22 июл 15, 15:11    [17921753]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
elwood,

1. Сделайте индекс по foreign_nmb и убедитесь, что update будет его использовать для поиска строк.
2. Показанный фрагмент перепишите так (это просто оптимизация, к дедлоку не относится):
update dbo.t_tag
 set
  value = case when @link is null then value else @link end,
  archive = case when @link is null then  1 else 0 end
where foreign_nmb = @foreign_nmb and tag_type_nmb = 1

if @@rowcount = 0 and @link is not null
 insert into dbo.t_tag (foreign_nmb, tag_type_nmb, value, archive)
 values (@foreign_nmb , 1, @link, 0)
22 июл 15, 15:38    [17921916]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при апдейте с WHERE  [new]
elwood
Member

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

Интересный подход, спасибо )
22 июл 15, 15:56    [17922025]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить