зміст
1. Помилка 1С: "Конфлікт блокувань при виконанні транзакції". В чому причина?
2. Помилки в 1С через блокувань
2.1 Приклад необхідної блокування в 1С
2.2 Приклад надлишкової блокування в 1С
2.3 Як позбутися від надлишкових блокувань в 1С

Помилка 1С: "Конфлікт блокувань при виконанні транзакції". В чому причина?

Це питання виникло у нас на проекті по впровадженню ЗУП2.5 з чисельністю 20000 і середньою кількістю одночасних сесії користувачів 200.
На етапі дослідної експлуатації при розрахунку зарплати користувачі почали інтенсивно працювати з документами «Нарахування зарплати співробітникам організацій». Обсяг документів був близько 2500 рядків. У користувачів почали з'являтися повідомлення «Конфлікт блокувань при виконанні транзакції», і розрахунок доводилося запускати заново.

Стали розбиратися. Виявилося, ми зіткнулися з ефектом «Надлишкової блокування». Зазвичай цей ефект з'являється при паралельному проведенні документів, під час нього найпершим документом блокується великий обсяг записів регістрів на весь час проведення документа. Це блокування затримує проведення інших документів, заважає паралельну роботу користувачів і уповільнює робочий процес. Взагалі блокування даних при проведенні документів річ корисна, вона зберігає цілісність даних і гарантує правильність виконання розрахункових алгоритмів. Але буває так, що або обсяг заблокованих даних надмірний, або час блокування занадто велике. В результаті ми маємо багато користувачів систему, яка по суті є однопользовательской: замість паралельного проведення документів - послідовне.

Помилки в 1С через блокувань

Приклад необхідної блокування в 1С

Уявімо таку ситуацію - є два документа «Нарахування зарплати співробітникам організацій», в яких зазначено однаковий податковий період, а на закладці ПДФО вказані однакові співробітники. Розглянемо випадок, коли блокування взагалі відсутня. Якщо послідовно запускати розрахунок цих документів, то в першому сума ПДФО зважить правильно, а в другому буде дорівнює нулю, тому що розрахований і фактично нарахований ПДФО на момент проведення другого документа будуть збігатися.

Але якщо запустити ці документи паралельно, то вони одночасно нарахують ПДФО, не підозрюючи про існування один одного, і в результаті податок подвоїться. Якщо блокування налаштована вірно, то перший документ, запущений на частку секунди раніше другого, встигне першим прочитати і заблокувати дані про фактично обчисленому податок в регістрі «ПДФО розрахунки з бюджетом» по співробітнику Пушкіну А.С. З цього запиту буде видно, що фактичний податок за січень поки не нараховувався і значить треба виконати рух по регістру. Блокування буде відпущена тільки після завершення запису в регістр. Другий документ, дійшовши до запиту читання фактично нарахованого податку буде поставлений системою на очікування до тих пір, поки перший документ не закінчить транзакцію проведення, після чого він прочитає в запиті, що податок вже нараховано і рух по регістру виконувати не треба. Це необхідна блокування.

Звичайно, цей приклад притягнутий за вуха для простоти пояснення. Насправді логіка ЗУП 2.5 така, що для задвоєння ПДФО користувачам не потрібно прикладати особливих зусиль. ПДФО розраховується до проведення документа, а при проведенні вміст табличній частині просто заноситься в регістри без будь-якої перевірки. Користувачам між розрахунком і проведенням надається можливість подивитися майбутній результат і при необхідності поправити руками. Звичайно це великий плюс на користь гнучкості зупа, але пред'являє високі вимоги до професійного рівня розраховувачів. Тому питання запобігання задвоєння ПДФО вирішується організаційним шляхом або за допомогою додаткових перевірочних звітів. Звичайно, в ЗУП2.5 є регістри, які розраховуються і записуються одночасно при проведенні документа, наприклад «ПДФО до заліку», але цей приклад довелося б довше пояснювати;).

Приклад надлишкової блокування в 1С

А тепер уявімо іншу ситуацію. При проведенні документа виконується запит, який повинен відібрати документи, в яких присутній співробітник з цього документа. Але запит написаний так, що сервер SQL змушений знаходити потрібні документи методом перебору. Для технічних фахівців це означає, що замість CLUSTERED INDEX SCAN виконується TABLE SCAN, тобто замість сканування таблиці індексів відбувається сканування самої таблиці. Причому в процесі перебору блокуються всі записи, до яких доторкнувся запит, навіть ті, в яких не присутні шукані співробітники. І це блокування буде діяти до кінця завершення проведення документа, що буде перешкоджати паралельному проведення документів з іншими співробітниками. Це надмірна блокування.

Як позбутися від надлишкових блокувань в 1С

Лікування надлишкових блокувань може йти двома шляхами. Перший - це оптимізація запитів, які виконуються всередині транзакцій і додавання необхідних табличних індексів в конфігураторі. Другий - це переклад виконання SQL-запитів на більш низький рівень ізоляції, коли при виконанні запитів записи в таблицях блокуються тільки на момент виконання самого запиту, або не блокуються зовсім. А необхідні блокування встановлюються засобами об'єкту «БлокіровкаДанних» і виконуються на стороні сервера 1С.

Тепер трохи теорії про рівні ізоляції на SQL сервері:

1. В автоматичному режимі в транзакціях використовується рівень ізоляції SERIALIZABLE. Цей рівень накладає блокування типу X (забороняє читання і запис) до кінця транзакції на всі дані, яких торкнулися запити або сталася запис даних.

2. У керованому режимі в транзакціях використовується рівень ізоляції ReadCommitted. Цей рівень на записані дані також встановлює блокування типу X до кінця транзакції. Але при виконанні запитів на дані накладає блокування типу S (забороняє запис і перевіряє чи немає в цей момент паралельних записів), при завершенні запиту блокування знімаються не чекаючи завершення транзакції.

3. Якщо база даних переведена в режим ReadCommitted SNAPSHOT, то в керованому режимі при читанні даних не накладається блокування типу S, є тільки блокування типу X під час запису.

Теж саме трохи більш докладно в таблиці:

Зазвичай лікування починають зі зниження рівня ізоляції, тому що це не особливо трудомісткий і дає швидкий результат. Досить перевести конфігурацію з «Автоматичного» режиму управління блокуванням даних в «Керований», і транзакції почнуть виконуватися на рівні ізоляції типу ReadCommitted, замість SERIALIZABLE або Repeatable Read.

Щоб переключити базу даних в режим READ COMMITTED SNAPSHOT (RCSI) необхідно в «SQL Server Management Studio» у властивостях бази даних встановити параметр "Is Read Committed Snapshot On" в значення "True":

У деяких джерелах пропонують встановити параметр "Allow Snapshot Isolation" в значення "True", але в цьому немає необхідності, тому що це призведе до включення іншого режиму ізоляції SNAPSHOT, яка не підтримується платформою 1С (На момент написання статті реліз платформи 8.3.9).

Режим управління блокуванням даних задається для неявних транзакцій, які виконуються під час запису або при проведенні документів, тобто всередині зумовлених процедур типу ПріЗапісі () або ОбработкаПроведенія (). Але більшість «важких» обчислень у типовій конфігурації ЗУП2.5 відбувається при виконанні команди «Розрахувати». При цьому в модулі об'єкта запускається процедура РассчітатьВсе (), всередині якої неодноразово повторюється конструкція НачатьТранзакцію () ... ЗафіксіроватьТранзакцію (). Це явно зазначені транзакції, всередині яких відбувається запис і очищення регістрів і виконуються запити. Нам необхідно переконатися, що при перемиканні конфігурації в керований режим в процедурі «РассчітатьВсе ()» транзакції також починають виконуватися на рівні ReadCommitted.

Для цього проведемо невеличкий експеримент:

• Запустимо SQL Server Profiler.

• Запустимо «NEW TRACE».

• Виконаємо підключення до сервера SQL.

• У вікні «Trace Properties» на закладці «General» виберемо «Use the template» = «Blank», а на закладці «Events Selections» розкриємо групу «Stored Procedures» і виберемо «RPC: Complited». За кнопці «Column Filters» вкажемо ім'я бази і тривалість виконання запитів більше 1.

• Кнопку RUN поки натискати не будемо, тому що нам треба спочатку запустити базу даних в режимі налагодження і зупинити виконання розрахунку документа «Нарахування зарплати співробітникам організацій» перед виконанням самого масивного запиту. Наприклад, це буде команда
«Результат = Запрос.ВиполнітьПакет ();" в функції «ПолучітьДанниеНДФЛПоРегістратору» в загальному модулі «ПроведеніеРасчетов». Тут відбувається виконання основного запиту для розрахунку ПДФО. Поставимо на ній точку зупину відладчика і запустимо розрахунок в документі.

· Після того як відладчик зупиниться, натиснемо кнопку RUN в профайлером.

· Тепер зробимо один крок в отладчике кнопкою F11. Коли запит буде виконаний і відладчик перейде на наступний крок, зупинимо читання профайлером кнопкою «Pause Selected Trace».

· Тепер знайдемо найтриваліший запит по колонці Duration і уважно вивчимо текст запиту. Якщо при зверненні до реальної (а не тимчасовою) таблиці після слова WITH варто SERIALIZABLE, то ми маємо справу з автоматичним режимом блокування. Якщо нічого не варто - то з керованим.

Якщо в хинти запиту (Hint - це параметр після слова WITH, що дозволяє впливати на план виконання запиту) не вказано рівень ізоляції, то виконується рівень ізоляції, встановлений за замовчуванням для поточної SQL-сесії. Визначити рівень ізоляції, діючий за умовчанням для поточних сесій можна в «SQL Server Management Studio» за допомогою команди

SELECT CASE transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'SERIALIZABLE'

WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL

FROM sys.dm_exec_sessions

У керованому режимі для всіх сесій буде вказано режим ReadCommitted.

Таким чином ми отримали експериментальне підтвердження того, що після переведення в керований режим всієї конфігурації, в цьому режимі починають працювати не тільки процедури запису і проведення, а й процедури розрахунку документів ЗУП. А це означає, що кількість повідомлень про конфлікт блокувань буде істотно знижено, а паралельність роботи з базою даних збільшиться.

Після того, як включили керований режим блокування ми повинні переконатися, що позбувшись від надлишкових блокувань, ми не пішли в іншу крайність і не втратили необхідні блокування, які захищають систему від порушення цілісності даних при активній паралельну роботу користувачів.

Налаштування керованих блокувань - це тема для окремої статті. Коротенько скажу, що програмно керовані блокування встановлюються за допомогою об'єкта «БлокіровкаДанних». Самі керовані блокування працюють вже не на рівні SQL сервера, як у випадку з автоматичними блокуваннями, а на рівні сервера 1С. Для визначення необхідних і достатніх керованих блокувань треба розуміти логіку програми одночасно на рівні бізнес-процесів і на рівні архітектури таблиць СУБД.

Але на мій погляд, для таких конфігурацій, як ЗУП2.5 взагалі немає сенсу використовувати будь-які блокування, краще використовувати перевірочні звіти для виявлення порушення цілісності даних - на практиці це найшвидший спосіб розрахунку зарплати. Особливо на великих підприємствах, де точно є співробітники з внутрішнім суміщенням у відокремлених підрозділах, а за кожним ОП закріплений окремий розраховувач, що і є причиною задвоєння ПДФО. Який б не був вишколений персонал, сама ідеологія конфігурації допускає можливість задвоєння ПДФО. Тому краще не заважати користувачам працювати паралельно під час масованих місячних розрахунків, а по завершенні точково і швидко виправити невеликий відсоток помилок, ніж змушувати їх сидіти і нервувати в черзі через страх допустити хоча б одну помилку. У цьому проекті ми використовували самопісний звіт «Перевірка ПДФО», який відображав співробітників з некоректним ПДФО.

Так само на цьому проекті ми зіткнулися з ефектом «Ескалація блокувань», коли SQL сервер сам приймає рішення, що треба укрупнити область накладення блокувань аж до блокування цілком всієї таблиці. В результаті робота користувачів зупиняється, і всі чекають завершення проведення одного документа - винуватця ескалації, або коли з таймаут знімуться взаємні блокування, або відбудеться перезавантаження сервера. В яких випадках виникає ескалація і як з цим боротися теж тема для окремої статті.

Валерій Федоров

Керівник проектів ТОВ "Кодерлайн"