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

Откуда:
Сообщений: 1809
Есть таблицы А и В.

В одной сессии выполняем

update A set ... where...


У таблицы А есть триггер, где модифицируется таблица В:

update t
  from inserted i, deleted d, B t...


таким образом, сперва накладывается X-блокировка на таблицу А, а затем IX на таблицу В. Пока понятно.

В другой сессии выполяется запрос

select..
  from B join A


так вот, оказывается, что этот запрос накладывает свои S-блокировки сперва на В, а затем пытается на А.
И получается классический deadlock - доступ к двум ресурсам в разном порядке.

Самое простое, что советуют в этом случае - сделать порядок доступа одинаковым. Но как это сделать для SELECT с JOIN ?

Есть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

MS SQL 2014. Обе транзакции READ COMMITTED.

PS. Про READ_COMMITTED_SNAPSHOT знаю, но он выключен, включать его не в моей компетенции. Хотелось бы попробовать обойтись управлением блокировками.
10 июл 18, 15:47    [21559800]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Cane Cat Fisher,

слова вроде из той песни, но смысл...

а как вы достигли такого понимания вопроса?

по вашему прядок shared lock в одном запросе О_о имеет значение, а не то что же они блокируют.
10 июл 18, 15:53    [21559823]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
komrad
Member

Откуда:
Сообщений: 5244
Cane Cat Fisher
Есть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

PS. Про READ_COMMITTED_SNAPSHOT знаю, но он выключен, включать его не в моей компетенции. Хотелось бы попробовать обойтись управлением блокировками.


посмотрите сюда :

FORCE ORDER hint
10 июл 18, 16:01    [21559849]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
komrad
Cane Cat Fisher
Есть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

PS. Про READ_COMMITTED_SNAPSHOT знаю, но он выключен, включать его не в моей компетенции. Хотелось бы попробовать обойтись управлением блокировками.


посмотрите сюда :

FORCE ORDER hint

действительно, именно "очередность наложения блокировок", пусть и запрос заодно перепишет тогда. Но как друзья вы не садитесь...
10 июл 18, 16:03    [21559858]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
Cane Cat Fisher
Member

Откуда:
Сообщений: 1809
TaPaK
а как вы достигли такого понимания вопроса?


+

<deadlock-list>
 <deadlock victim="processadea42b848">
  <process-list>
   <process id="processadea42b848" waitresource="KEY: 5:72057606279528448 (44d2e67127ed)" transactionname="INSERT" lockMode="S" status="suspended" isolationlevel="read committed (2)" >
    <executionStack>
     <frame procname="Ex" line="177">
insert #t
  select 
    from B t join A doc on t.ID = doc.ID
    ...
    </frame>
    </executionStack>
   </process>
   <process id="process81e52b4ca8" waitresource="OBJECT: 5:994310802:13 " transactionname="implicit_transaction" lockMode="IX" status="suspended" isolationlevel="read committed (2)" >
    <executionStack>
     <frame procname="tU_A" line="280">
update t set
        FB = ...
    from inserted i, deleted d, B t
   where i.ID = d.ID
     and t.ID = i.ID
     ...
     </frame>
    </executionStack>
    <inputbuf>
update A
set FA
where ID=...
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock objectname="A" indexname="PK_A" mode="X">
    <owner-list>
     <owner id="process81e52b4ca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processadea42b848" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <objectlock subresource="FULL" objectname="B" mode="S" >
    <owner-list>
     <owner id="processadea42b848" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process81e52b4ca8" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>
10 июл 18, 16:48    [21559984]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
msLex
Member

Откуда:
Сообщений: 8065
Cane Cat Fisher
Есть таблицы А и В.

В одной сессии выполняем

update A set ... where...



У таблицы А есть триггер, где модифицируется таблица В:

update t
  from inserted i, deleted d, B t...



таким образом, сперва накладывается X-блокировка на таблицу А, а затем IX на таблицу В. Пока понятно.

В другой сессии выполяется запрос

select..
  from B join A



так вот, оказывается, что этот запрос накладывает свои S-блокировки сперва на В, а затем пытается на А.
И получается классический deadlock - доступ к двум ресурсам в разном порядке.

Самое простое, что советуют в этом случае - сделать порядок доступа одинаковым. Но как это сделать для SELECT с JOIN ?

Есть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

MS SQL 2014. Обе транзакции READ COMMITTED.

PS. Про READ_COMMITTED_SNAPSHOT знаю, но он выключен, включать его не в моей компетенции. Хотелось бы попробовать обойтись управлением блокировками.


Блокировки накладываются в том же порядке, в котором движок обращается к данным

Если ваш
select..
from B join A

сначала читает строку из B а потом из A то в том же порядке накладываются блокировки


возможность поменять порядок наложения блокировок сильно зависит от плана запроса
10 июл 18, 16:59    [21560022]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
invm
Member

Откуда: Москва
Сообщений: 9343
Cane Cat Fisher
Самое простое, что советуют в этом случае - сделать порядок доступа одинаковым. Но как это сделать для SELECT с JOIN ?
Может стоит выбирать оптимальный путь, а не простой?
Cane Cat Fisher
Есть ли какие-то хинты, которые бы задавали очередность наложения блокировок?
Непосредственных, естественно, нет. Опосредованно влияющие есть (см. далее).

Если таки пойдете "простым" путем, то будьте готовы к возможной деградации производительности вашего select.
Опция force order повлияет на соединения всех таблиц в запросе.
Если хотите манипуляции с очередностью блокировок ограничить только конкретным соединением, то это можно осуществить, например, так:
select..
  from A inner loop (или merge или hash) join B
10 июл 18, 16:59    [21560023]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Cane Cat Fisher,

ну 2 апдейта и селект. Как не пляшите INSERT SELECT ничего не поменяет
10 июл 18, 17:02    [21560028]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
Cane Cat Fisher
Member

Откуда:
Сообщений: 1809
invm
select..
  from A inner loop (или merge или hash) join B


Спасибо, так и сделал. Помогло.
11 июл 18, 16:55    [21563357]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Cane Cat Fisher
invm
select..
  from A inner loop (или merge или hash) join B


Спасибо, так и сделал. Помогло.

теперь insert блокирует всб таблицу и дедлока действительно нет...
11 июл 18, 16:58    [21563373]     Ответить | Цитировать Сообщить модератору
 Re: Очередность блокировок в JOIN  [new]
Cane Cat Fisher
Member

Откуда:
Сообщений: 1809
TaPaK
теперь insert блокирует всб таблицу и дедлока действительно нет...

И что же приводит к блокировке всей таблицы? inner loop, или перестановка таблиц местами?
11 июл 18, 17:42    [21563579]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить