Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Очередность блокировок в JOIN / 11 сообщений из 11, страница 1 из 1
10.07.2018, 15:47
    #39672143
Cane Cat Fisher
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Есть таблицы А и В.

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

Код: sql
1.
update A set ... where...



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

Код: sql
1.
2.
update t
  from inserted i, deleted d, B t...



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

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

Код: sql
1.
2.
select..
  from B join A



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

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

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

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

PS. Про READ_COMMITTED_SNAPSHOT знаю, но он выключен, включать его не в моей компетенции. Хотелось бы попробовать обойтись управлением блокировками.
...
Рейтинг: 0 / 0
10.07.2018, 15:53
    #39672146
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat Fisher,

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

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

по вашему прядок shared lock в одном запросе О_о имеет значение, а не то что же они блокируют.
...
Рейтинг: 0 / 0
10.07.2018, 16:01
    #39672149
komrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat FisherЕсть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

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

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

FORCE ORDER hint
...
Рейтинг: 0 / 0
10.07.2018, 16:03
    #39672155
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
komradCane Cat FisherЕсть ли какие-то хинты, которые бы задавали очередность наложения блокировок?

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

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

FORCE ORDER hint
действительно, именно "очередность наложения блокировок", пусть и запрос заодно перепишет тогда. Но как друзья вы не садитесь...
...
Рейтинг: 0 / 0
10.07.2018, 16:48
    #39672186
Cane Cat Fisher
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
TaPaKа как вы достигли такого понимания вопроса?


Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
<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>

...
Рейтинг: 0 / 0
10.07.2018, 16:59
    #39672193
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat FisherЕсть таблицы А и В.

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

Код: sql
1.
update A set ... where...




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

Код: sql
1.
2.
update t
  from inserted i, deleted d, B t...




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

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

Код: sql
1.
2.
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 то в том же порядке накладываются блокировки


возможность поменять порядок наложения блокировок сильно зависит от плана запроса
...
Рейтинг: 0 / 0
10.07.2018, 16:59
    #39672194
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat FisherСамое простое, что советуют в этом случае - сделать порядок доступа одинаковым. Но как это сделать для SELECT с JOIN ?Может стоит выбирать оптимальный путь, а не простой?
Cane Cat FisherЕсть ли какие-то хинты, которые бы задавали очередность наложения блокировок?Непосредственных, естественно, нет. Опосредованно влияющие есть (см. далее).

Если таки пойдете "простым" путем, то будьте готовы к возможной деградации производительности вашего select.
Опция force order повлияет на соединения всех таблиц в запросе.
Если хотите манипуляции с очередностью блокировок ограничить только конкретным соединением, то это можно осуществить, например, так:
Код: sql
1.
2.
select..
  from A inner loop (или merge или hash) join B
...
Рейтинг: 0 / 0
10.07.2018, 17:02
    #39672195
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat Fisher,

ну 2 апдейта и селект. Как не пляшите INSERT SELECT ничего не поменяет
...
Рейтинг: 0 / 0
11.07.2018, 16:55
    #39672673
Cane Cat Fisher
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
invm
Код: sql
1.
2.
select..
  from A inner loop (или merge или hash) join B



Спасибо, так и сделал. Помогло.
...
Рейтинг: 0 / 0
11.07.2018, 16:58
    #39672675
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
Cane Cat Fisherinvm
Код: sql
1.
2.
select..
  from A inner loop (или merge или hash) join B



Спасибо, так и сделал. Помогло.
теперь insert блокирует всб таблицу и дедлока действительно нет...
...
Рейтинг: 0 / 0
11.07.2018, 17:42
    #39672697
Cane Cat Fisher
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Очередность блокировок в JOIN
TaPaKтеперь insert блокирует всб таблицу и дедлока действительно нет...
И что же приводит к блокировке всей таблицы? inner loop, или перестановка таблиц местами?
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Очередность блокировок в JOIN / 11 сообщений из 11, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]