С недавнего времени я начала заниматься работами по оптимизации 1с, и решила написать несколько статей на эту тему, чтобы не забыть то, чему я научилась. Своеобразную базу знаний, поэтому если подобная задача уже где-то разбиралась, то прошу прощения. Заодно, возможно, кому-нибудь это поможет в работе. Любая критика статьи приветствуется.
Сегодня я разберу deadlock СУБД.
Итак, имеется программа доработанная программа 1С Медицина.Больничная аптека 2.0. режим управления блокировки данных - управляемый, платформа 8.3.10.2252, режим совместимости 8.3.8.
При проведении документов "Заказ поставщику" возникает deadlock на СУБД со следующим текстом:
Ошибка при вызове метода контекста (Записать): Ошибка при выполнении обработчика - 'ОбработкаПроведения': {РегистрСведений.ИзмененияЕдиницИзмеренияНоменклатуры.МодульМенеджера(72)}:
Ошибка при вызове метода контекста (Записать): Конфликт блокировок при выполнении транзакции: Microsoft SQL Server Native Client 11.0: Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. HRESULT=80004005, SQLSrvr: SQLSTATE=40001, state=34, Severity=D, native=1205, line=1
Для начала настроим SQL profiler на сбор дедлоков. И довольно быстро ловим необходимое:
Видим, что дедлок возникает на одном ресурсе - InfoRg8072, по структуре данных видим, что это регистр сведений ИзмененияЕдиницИзмеренияНоменклатуры:
Таблица: РегистрСведений.ИзмененияЕдиницИзмеренияНоменклатуры, Имя таблицы хранения: InfoRg8072, Назначение: Основная
- поля:
Период (Period)
Номенклатура (Fld8073)
ЕдиницаИзмерения (Fld8074)
Документ (Fld8075)
Ответственный (Fld8076)
Ошибка (Fld8077)
ОбластьДанныхОсновныеДанные (Fld385)
- индексы:
ByPeriod
Период + Номенклатура + ЕдиницаИзмерения + Документ (Period + Fld8073 + Fld8074 + Fld8075)
ByDims
Номенклатура + ЕдиницаИзмерения + Документ + Период (Fld8073 + Fld8074 + Fld8075 + Period)
Метод пристального взгляда на код ничего не дал, поэтому я настроила ЦУП и поймала дедлок, чтобы проанализиро вать что именно происходит. ЦУП поймал блокировку: 
Посмотрим на содержимое блокировок процесса 1. Итак, смотрим на первую блокировку - U (блокировка обновления).
Запрос:
SELECT TOP 1
T1._Fld385
FROM dbo._InfoRg8072 T1
WHERE ((T1._Fld385 = ?)) AND (T1._Fld8073RRef = ? AND T1._Fld8074RRef = ? AND T1._Fld8075_TYPE = 0x08 AND T1._Fld8075_RTRef = 0x000000B6 AND T1._Fld8075_RRRef = ?)
План:
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([HP_w_sql_001].[dbo].[_InfoRg8072].[_InfoRg8072_ByDims] AS [T1]), SEEK:([T1].[_Fld385]=[@P1] AND [T1].[_Fld8073RRef]=[@P2] AND [T1].[_Fld8074RRef]=[@P3] AND [T1].[_Fld8075_TYPE]=0x08 AND [T1].[_Fld8075_RTRef]=0x000000B6 AND [T1].[_Fld8075_RRRef]=[@P4]) ORDERED FORWARD)
Ничего криминального не видно...
Смотрим дальше, еще одна блокировка U:
Запрос:
SELECT TOP 1
T1._Fld385
FROM dbo._InfoRg8072 T1
WHERE ((T1._Fld385 = ?)) AND (T1._Fld8075_TYPE = 0x08 AND T1._Fld8075_RTRef = 0x000000B6 AND T1._Fld8075_RRRef = ?)
План:
| |--Top(TOP EXPRESSION:((1)))
| |--Index Seek(OBJECT:([HP_w_sql_001].[dbo].[_InfoRg8072].[_InfoRg8072_ByPeriod] AS [T1]), SEEK:([T1].[_Fld385]=[@P1]), WHERE:([HP_w_sql_001].[dbo].[_InfoRg8072].[_Fld8075_RRRef] as [T1].[_Fld8075_RRRef]=[@P2] AND [HP_w_sql_001].[dbo].[_InfoRg8072].[_Fld8075_TYPE] as [T1].[_Fld8075_TYPE]=0x08 AND [HP_w_sql_001].[dbo].[_InfoRg8072].[_Fld8075_RTRef] as [T1].[_Fld8075_RTRef]=0x000000B6) ORDERED FORWARD)
А вот тут уже интереснее.
Видно, что тут использовался индекс, поскольку оператор Index Seek, но посмотрев повнимательнее, видим, что seek идет только по разделителю данных, ([_Fld385]=[@P1]), а у после seek идет where, это означает, что данным полям индекс использовать не удалось и происходит сканирование данных.
Почему не был использован индекс? Потому что в составном индексе крайне важен порядок следования полей. И если условия отбора есть в индексе, но не стоит в самом начале или идут не подряд, то индекс не может использоваться. В нашем случае есть вот такой индекс:
Номенклатура + ЕдиницаИзмерения + Документ + Период (Fld8073 + Fld8074 + Fld8075 + Period)
А запросе у нас есть отбор только по полю Документ (Fld8075). А перед этим в составном индексе есть еще и поле Номенклатура и ЕдиницаИзмерения, поэтому данный индекс не может использоваться.
Итак, получается первым запросом у нас блокируется одна запись из регистра _InfoRg8072, а потом блокируется почти весь регистр (рамках разделителя) до окончания транзакции.
Посмотрев второй процесс, можно обнаружить точно такие же запросы.
А что будет если два человека будут проводить документы с разными данными? Первый успешно блокирует блокировкой U одну запись из регистра, второй так же блокирует другую запись из этого же регистра, а дальше каждый из них пытается заблокировать весь регистр целиком, но не могут, потому что первый ждет второго, а второй - первого, что приводит к дедлоку.
Итак, почему же не используется индекс? Давайте посмотрим на индексы регистра _InfoRg8072
- индексы:
ByPeriod
Период + Номенклатура + ЕдиницаИзмерения + Документ (Period + Fld8073 + Fld8074 + Fld8075)
ByDims
Номенклатура + ЕдиницаИзмерения + Документ + Период (Fld8073 + Fld8074 + Fld8075 + Period)
Мы видим, что есть индекс, где первым полем идет Fld8075 отсутствует, а значит происходит сканирование данных.
Теперь давайте посмотрим код 1с:
Вот код, который отрабатывает при записи документа Заказ поставщику:
При выполнении строки
НаборЗаписей.Записать(Истина);
Выполняется первый запрос. (использующий индексы)
При проведении документа выполняется вот этот код:
При выполнении строки
НаборЗаписей.Записать(Истина);
Выполняется второй запрос. (не использующий индексы)
Как видно, здесь не стоят отборы по номенклатуре и единицы измерения, а есть только отбор по документу, поэтому подходящего индекса нет, он не используется.
Мы видим типичный дедлок захвата ресурса в разном порядке:
Первый пользователь захватывает ресурс 1 в регистре сведений,
Второй пользователь захватывает ресурс 2 в регистре сведений,
Дальше первый пытается захватить весь регистр (поскольку в плане запроса скан), но не может - натыкается на захват ресурса 2.
Второй так же пытается захватить весь регистр, но не может, поскольку натыкается на захват ресурса 1.
Итого - дедлок
Теперь решение этой проблемы.
Вариант 1.
Перенести измерение "Документ" наверх. В этом случае индекс будет строиться следующим образом:
ByDims
Документ + Номенклатура + ЕдиницаИзмерения + Период (Fld8075 + Fld8073 + Fld8074 + Period)
Вариант 2.
Проиндексировать измерение "Документ". Тогда в добавиться дополнительный индекс такого вида:
ByDims
Документ + Период + Номенклатура + ЕдиницаИзмерения (Fld8075 + Period + Fld8073 + Fld8074)
В обоих вариантах индекс будет использоваться, поскольку поле "Документ" находится в начале индекса.
По ряду причин был выбран второй вариант и после индексации измерения документ, дедлоки больше не возникали.
UPD 09.07.2019
В комментариях был задан хороший вопрос, а не возник ли дедлок по причине чтения в транзакции записей регистра, а затем его записи. Мол, это хрестоматийный пример дедока.
Но есть следующие проблемы.
1. Это пример дедлока на управляемой блокировке. А в моем примере блокировка СУБД
2. При проведении документа в коде ставится отбор по документу, чтобы воспроизвести такую блокировку мне нужно провести в двух сессиях один и тот же документ, что невозможно.
3. Для успокоения совести я провела следующий эксперимент.
а) Убрала индексирование измерения "документ"
б) Добавила управляемую блокировку в код (по просьбам, установив отбор по и номенклатуре):
г) Провела два документа с разными данными в разных сессиях, и на одном из них поймала дедлок СУБД:
Как видно, дедлок воспроизводится, несмотря на установку управляемой блокировки.
UPD 10.07.2019
Я подготовила для вас модельную базу, на которой воспроизводится дедлок. Там используется точно такой же код, который представлен в статье, специально, чтобы вы могли поупражнятся в оптимизации кода и установить какие угодно транзакции и блокировки.
PS
1. не забудьте развернуть базу в клиент-серверном варианте.
2. Дедлок возникает, если поставить точку останова в строке 40 модуля документа заказ поставщику.
