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

Откуда: Москва
Сообщений: 203
Добрый день.

Помогаю разработчикам с определением причин появления дедлоков. Изначально было похоже на классику: проблема с одной из таблиц, две страницы в ней, два запроса.
Первый запрос с Insert и Select блочит первую страницу и хочет вторую, а второй запрос заблочил вторую страницу и хочет первую. Стандартный дедлок, вопросов не было.

Эту часть переработали, но проблема осталась. Сейчас при дедлоке на этой же таблице блочат друг друга два селекта.

Данные собраны через Extended Events. Прикладываю схему.
По схеме блокировку первой страницы делает почему-то не S, а IX и SIX, что для выловленных запросов избыточно. Насколько я понимаю, для решения проблемы нужно понять, почему ставит блокировку не S, и по возможности изменить этот момент. Но пока причин не вижу.

Проблемная таблица "n_order_marketing_complex_service" - одна из трех таблиц участвующих в join.

Запрос жертва:

(@P1 nvarchar(4000))
            SELECT
                1 flow_doctype,
                ote.order_num flow_id,
                ote.order_num order_num,
                IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id,
                1 count,
                IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price,
                1 flow_type,
                ote.DateReg flow_date,
                IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total,
                od.bonuses,
                od.cito_factor,
                ote.OrderKontragentID sender_id,
                ote.OrderDiscount discount,
                IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price,
                IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id,
                omcs.complex_id
            FROM n_OrderDetail od
                LEFT JOIN OrdersToExport ote ON ote.order_num = od.order_num
                LEFT JOIN n_order_marketing_complex_service omcs ON omcs.order_num = od.order_num AND omcs.complex_id = od.serv_id
            WHERE ote.order_num = @P1


Оставшийся запрос:
(@P1 nvarchar(8))
            SELECT
                1 flow_doctype,
                ote.order_num flow_id,
                ote.order_num order_num,
                IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id,
                1 count,
                IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price,
                1 flow_type,
                ote.DateReg flow_date,
                IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total,
                od.bonuses,
                od.cito_factor,
                ote.OrderKontragentID sender_id,
                ote.OrderDiscount discount,
                IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price,
                IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id,
                omcs.complex_id
            FROM n_OrderDetail od
                LEFT JOIN OrdersToExport ote ON ote.order_num = od.order_num
                LEFT JOIN n_order_marketing_complex_service omcs ON omcs.order_num = od.order_num AND omcs.complex_id = od.serv_id
            WHERE ote.order_num = @P1


К сообщению приложен файл. Размер - 46Kb
18 дек 18, 12:55    [21766945]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
TaPaK
Member

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

т.е. SELECT без видимых причин решил что ему нужен SIX? Граф дедлока в xml покажите
18 дек 18, 13:04    [21766956]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Причина наверняка где-то есть.

План XML: (скрыл имя хоста, пользователя и базу)

<deadlock>
 <victim-list>
  <victimProcess id="process42eef144e8" />
 </victim-list>
 <process-list>
  <process id="process42eef144e8" taskpriority="0" logused="12536" waitresource="PAGE: 9:1:116120662 " waittime="1049" ownerId="30254457035" transactionname="user_transaction" lasttranstarted="2018-12-18T10:48:41.147" XDES="0x4e382696c8" lockMode="S" schedulerid="1" kpid="11736" status="suspended" spid="3547" sbid="1" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-12-18T10:48:41.283" lastbatchcompleted="2018-12-18T10:48:41.283" lastattention="1900-01-01T00:00:00.283" hostname=" скрыто " hostpid="0" loginname=" скрыто " isolationlevel="read committed (2)" xactid="30254457035" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="66" stmtend="2428" sqlhandle="0x02000000b2230d0756002740d3d3ca09f315bfc98114fd7c0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))
            SELECT
                1 flow_doctype,
                ote.order_num flow_id,
                ote.order_num order_num,
                IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id,
                1 count,
                IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price,
                1 flow_type,
                ote.DateReg flow_date,
                IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total,
                od.bonuses,
                od.cito_factor,
                ote.OrderKontragentID sender_id,
                ote.OrderDiscount discount,
                IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price,
                IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id,
                omcs.complex_id
            FROM n_OrderDetail od
                LEFT JOIN OrdersToExport ote ON ote.o   </inputbuf>
  </process>
  <process id="process65e4063c28" taskpriority="0" logused="28444" waitresource="PAGE: 9:1:116241658 " waittime="1250" ownerId="30254454272" transactionname="user_transaction" lasttranstarted="2018-12-18T10:48:40.977" XDES="0x58bcadb948" lockMode="S" schedulerid="10" kpid="10540" status="suspended" spid="3316" sbid="1" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-12-18T10:48:41.077" lastbatchcompleted="2018-12-18T10:48:41.077" lastattention="1900-01-01T00:00:00.077" clientapp="Apache HTTP Server" hostname=" скрыто " hostpid="11796" loginname=" скрыто " isolationlevel="read committed (2)" xactid="30254454272" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="2" stmtstart="62" stmtend="2464" sqlhandle="0x02000000115f650aea85b094ea93aa7fdedb9f1fbc4383670000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(8))
            SELECT
                1 flow_doctype,
                ote.order_num flow_id,
                ote.order_num order_num,
                IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id,
                1 count,
                IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price,
                1 flow_type,
                ote.DateReg flow_date,
                IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total,
                od.bonuses,
                od.cito_factor,
                ote.OrderKontragentID sender_id,
                ote.OrderDiscount discount,
                IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price,
                IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id,
                omcs.complex_id
            FROM n_OrderDetail od
                LEFT JOIN OrdersToExp   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="116120662" dbid="9" subresource="FULL" objectname="скрыто.dbo.n_order_marketing_complex_service" id="lockca1cd1e500" mode="SIX" associatedObjectId="72057596708061184">
   <owner-list>
    <owner id="process65e4063c28" mode="SIX" />
   </owner-list>
   <waiter-list>
    <waiter id="process42eef144e8" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="116241658" dbid="9" subresource="FULL" objectname=" скрыто.dbo.n_order_marketing_complex_service" id="lock50c07c2c00" mode="IX" associatedObjectId="72057596708061184">
   <owner-list>
    <owner id="process42eef144e8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process65e4063c28" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>
18 дек 18, 13:21    [21766990]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
TaPaK
Member

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

Смотрите транзакции целиком. inputbuf показывает совсем не всё
18 дек 18, 13:36    [21767020]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Ваши селекты выполняются в транзакциях, в которых ранее были модификации "скрыто.dbo.n_order_marketing_complex_service". Об этом свидетельствует наличие IX.
18 дек 18, 13:40    [21767029]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

А можете подсказать где? Я выкладывал из xml_deadlock_report из extended events.

К сообщению приложен файл. Размер - 45Kb
18 дек 18, 13:45    [21767035]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

То есть - я вижу часть транзакции с селектом и где вроде нет причин, а блокирует часть, что через Extended Events не поймал?
18 дек 18, 13:46    [21767039]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Danion
То есть - я вижу часть транзакции с селектом и где вроде нет причин, а блокирует часть, что через Extended Events не поймал?
Вы видите граф дедлока с конфликтующими инструкциями. Но это не означает, что ресурсы, за которые идет драка, заблокированы только этими инструкциями .
У вас именно этот случай.
18 дек 18, 13:59    [21767081]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Спасибо за ответ.

А я как-то могу получить полную видимость ситуации с этими блокировками?
Или это только через разработчиков можно, чтобы смотрели что полностью выполняется при этом запросе?
18 дек 18, 14:37    [21767166]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
TaPaK
Member

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

Спасибо за ответ.

А я как-то могу получить полную видимость ситуации с этими блокировками?
Или это только через разработчиков можно, чтобы смотрели что полностью выполняется при этом запросе?

Быстрее у разработчиков, судя по тому что у вас там одно и то же
18 дек 18, 14:38    [21767169]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Хорошо, попробую через разработчиков. Стабильность по времени начала и конца массовых дедлоков присутствует.
18 дек 18, 14:49    [21767186]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

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

Сорее всего, ваш селект сканирует таблицу n_order_marketing_complex_service.
Возможно создание правильного индекса уберет таковое и запросы из разных сессий перестанут пересекаться по данным. Заодно может снизится гранулярность блокировок.
Соответственно, данный дедлок исчезнет.
18 дек 18, 16:21    [21767352]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Не понял как добавление индекса поможет с блокировками, которые накладываются пока не очень понятно где.

В селекте из этой таблицы участвуют в join поля omcs.order_num и omcs.complex_id.

По таблице есть индексы и например этот не кластерный индекс включает данные поля:
CREATE UNIQUE NONCLUSTERED INDEX [iu_n_order_marketing_complex_service$order_num$complex_id$service_id] ON [dbo].[n_order_marketing_complex_service]
(
	[order_num] ASC,
	[complex_id] ASC,
	[service_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
18 дек 18, 16:47    [21767388]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Danion
Не понял как добавление индекса поможет с блокировкам
Индекс может убрать сканирование таблицы из плана выполнения. Соответственно, конкурирующие запросы перестанут пересекаться по читаемым данным.
Показанный индекс возможно малоприменим для данного запроса, т.к. за значениями столбцов omcs.price и omcs.total все равно придется лезть в таблицу.
План запроса смотрите - там все видно.
18 дек 18, 17:17    [21767434]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Вот план запроса, поиск по индексу составил 91%. Как-то много.
! знак ругается на большее количество преобразований типов данных в рамках плана запросов.

К сообщению приложен файл. Размер - 106Kb
18 дек 18, 17:47    [21767481]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
komrad
Member

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

Вот план запроса, поиск по индексу составил 91%. Как-то много.

это трактуется так: 91% всего времени выполнения происходил index seek (самое быстрое что может быть)

% - это величина относительная, а не абсолютная
18 дек 18, 17:52    [21767489]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Konst_One
Member

Откуда:
Сообщений: 11518
нужен актуальный план выполнения
18 дек 18, 17:55    [21767496]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Просто там несколько сканирований индексов и 91% у одного удивило.

А чем этот план не актуальный? Данные из кеша. При дедлоке Extended Events похоже не собирает данные, попробую профайлером завтра, там вроде было.

Пытаюсь выяснить подробности у разрабов, пока выходит одна транзакция с кучей Select, update, delete, insert, один из кусков которой и есть с селектами. Тогда почему не падает всё остальное...
18 дек 18, 18:03    [21767508]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

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

"Правильный" для данного запроса индекс есть (правда не тот, который вы показали) и он задействован. Значит версия о полном сканировании таблицы отпадает.
18 дек 18, 18:16    [21767523]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Индекс этот применялся:

CREATE NONCLUSTERED INDEX [in_n_order_marketing_complex_service$complex_id$order_num$service_id$price$total] ON [dbo].[n_order_marketing_complex_service]
(
[complex_id] ASC
)
INCLUDE ( [order_num],
[service_id],
[price],
[total]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Транзакция с кучей действий с разным таблицами, один из этапов которой в первом посте с селектами. При падении должно откатывать все этапы.
При этом мне казалось, что блокировки наложенные в транзакции остаются до её окончания, а проблемы с одной таблицей только.

С таблицей по которой дедлоки происходит update, потом этом запрос с селектами, после в ней ничего не меняется.
Кроме варианта с грязным чтением (который может дать не соответствующие действительности данные) что-то можно сделать? Например как-то изменять уровень блокировки на S после update...
19 дек 18, 09:14    [21767949]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Danion
С таблицей по которой дедлоки происходит update, потом этом запрос с селектами, после в ней ничего не меняется.
Покажите этот update.
Danion
что-то можно сделать?
Радикально - включить RCSI.
Паллиативно, пока не выяснена причина дедлока - добавить хинт rowlock к таблице в селекте.
19 дек 18, 09:31    [21767958]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

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

Вот:

 UPDATE [dbo].[n_order_marketing_complex_service] SET [total]=:yp0, [bonuses]=:yp1 WHERE [dbo].[n_order_marketing_complex_service].[aid]=3028507
-----------------
 UPDATE [dbo].[n_order_marketing_complex_service] SET [total]=:yp0, [bonuses]=:yp1 WHERE [dbo].[n_order_marketing_complex_service].[aid]=3028504
-----------------
 UPDATE [dbo].[n_order_marketing_complex_service] SET [total]=:yp0, [bonuses]=:yp1 WHERE [dbo].[n_order_marketing_complex_service].[aid]=3028505
-----------------
 UPDATE [dbo].[n_order_marketing_complex_service] SET [total]=:yp0, [bonuses]=:yp1 WHERE [dbo].[n_order_marketing_complex_service].[aid]=3028506
19 дек 18, 09:44    [21767967]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
aid - это ПК?
19 дек 18, 10:11    [21767984]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Danion
Member

Откуда: Москва
Сообщений: 203
При включении RCSI нашел упоминание о проблемах с миррорингом в начале. Вроде не критично с данной проблемой блокировок, но база в AlwaysOn. Как-то может влиять? Вроде не должно.

invm,

Нет, похоже на номер заказа. По нему настроен первичный ключ таблицы.

ALTER TABLE [dbo].[n_order_marketing_complex_service] ADD PRIMARY KEY CLUSTERED
(
[aid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
19 дек 18, 10:20    [21767995]     Ответить | Цитировать Сообщить модератору
 Re: Дедлоки с Intent Locks объектов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
интересные тут беседы у вас
invm
aid - это ПК?

Danion
Нет
...
ALTER TABLE [dbo].[n_order_marketing_complex_service] ADD PRIMARY KEY CLUSTERED ([aid] ASC)

19 дек 18, 10:27    [21768005]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить