Перевод базы данных 1С в управляемый режим блокировок и включение механизма RCSI (Read Committed Snapshot Isolation) на уровне MS SQL Server теоретически должны полностью избавить систему от конфликтов между читающими и пишущими транзакциями. В этой конфигурации запросы на чтение данных (SELECT) обращаются к версионному хранилищу в базе данных tempdb и не должны блокироваться операциями модификации данных (INSERT, UPDATE, DELETE). Однако на практике разработчики и системные администраторы периодически сталкиваются с ошибками вида "Превышено максимальное время ожидания предоставления блокировки" на уровне СУБД.
В этой статье мы подробно разберем, почему возникают конфликты блокировок SQL в оптимистичном режиме изоляции транзакций, как локализовать проблему с помощью низкоуровневых скриптов и какие архитектурные решения помогут навсегда избавиться от этой проблемы.
Даже если в настройках СУБД включен параметр READ_COMMITTED_SNAPSHOT ON, существует целый ряд факторов, способных вызвать ожидания и взаимоблокировки на уровне SQL Server. Проанализируем основные из них:
ПриЗаписи или ОбработкаПроведения) выполняется крайне неоптимальный запрос, время всей транзакции увеличивается пропорционально времени выполнения этого запроса. Все блокировки на запись, которые платформа 1С наложила на таблицы регистров или документов до выполнения этого запроса, будут удерживаться монопольно СУБД до завершения всей транзакции (вызова команды COMMIT). В результате другие сессии, пытающиеся изменить те же таблицы, будут ожидать освобождения ресурсов и в итоге завершатся по таймауту.tempdb для хранения версий строк. Если ваши запросы создают множество тяжелых временных таблиц, в tempdb возникает жесткая конкуренция за системные страницы распределения памяти (возникают ожидания типа PAGELATCH_UP, PAGELATCH_EX). Блокировки метаданных при создании и удалении временных таблиц внутри транзакций могут заблокировать работу всей СУБД.Sch-S на целевую таблицу. Если в этот момент СУБД инициирует автоматическое обновление статистики по этой таблице или выполняются регламентные операции с индексами, накладывается блокировка изменения схемы Sch-M. Эти блокировки несовместимы, что приводит к зависанию даже простейших запросов чтения, несмотря на RCSI.Max Degree of Parallelism выставлен некорректно) потоки одного и того же запроса начинают блокировать друг друга (ожидания CXPACKET или CXCONSUMER), порождая так называемые внутризапросные взаимоблокировки.Для начала убедимся, что настройки СУБД и платформы соответствуют требованиям для работы RCSI. Выясним реальное состояние базы данных с помощью системных запросов.
Попросим системного администратора выполнить в SQL Server Management Studio (SSMS) запрос для проверки активности версионирования в нашей базе данных:
SELECT name, is_read_committed_snapshot_on, compatibility_level
FROM sys.databases
WHERE name = 'Имя_Вашей_Базы_Данных';
В результате значение в поле is_read_committed_snapshot_on должно быть равно 1. Если возвращается 0, это означает, что механизм RCSI выключен, и СУБД работает по классическому пессимистичному сценарию с блокировками читателей.
Далее проверим фактический уровень изоляции активных сессий пользователей 1С в СУБД. Для этого выполним следующий диагностический скрипт:
SELECT
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS TRANSACTION_ISOLATION_LEVEL,
d.name AS DatabaseName,
s.session_id,
s.program_name
FROM sys.dm_exec_sessions s
LEFT JOIN sys.databases d ON s.database_id = d.database_id
WHERE d.name = 'Имя_Вашей_Базы_Данных' AND s.session_id > 50;
В штатном режиме работы платформы 8.3 для всех сессий 1С должен быть установлен уровень изоляции Read Committed. При включенном RCSI этот уровень автоматически преобразуется СУБД в чтение из версионного хранилища без наложения блокировок разделяемого чтения (S-locks).
Если настройки СУБД верны, но конфликты продолжают происходить, необходимо поймать момент зависания и проанализировать, какие именно ресурсы и процессы участвуют в конфликте. Для этого развернем на сервере СУБД специальную хранимую процедуру sp_Locks, которая позволяет детально анализировать ожидания и взаимоблокировки.
Создадим процедуру в базе данных:
CREATE PROCEDURE dbo.sp_Locks (
@Mode int = 2,
@Wait_Duration_ms int = 1000 /* Порог ожидания в миллисекундах */
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF @Mode = 1
BEGIN;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
p2.[program_name] AS program_blocking,
p1.[program_name] AS program_blocked,
DB_NAME(l.resource_database_id) AS [database],
p2.[hostname] AS host_blocking,
p1.[hostname] AS host_blocked,
t.wait_duration_ms,
l.request_mode,
l.resource_type,
t.wait_type,
(SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) /2 ) + 1) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN (SELECT st.text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st WHERE p.spid = t.blocking_session_id) ELSE NULL END AS statement_blocking
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.dm_tran_locks AS l ON t.resource_address = l.lock_owner_address
INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
END;
IF @Mode = 2
BEGIN;
SELECT
spid, [status], CONVERT(CHAR(3), blocked) AS blocked, loginame,
SUBSTRING([program_name] ,1,25) AS program,
SUBSTRING(DB_NAME(p.dbid),1,10) AS [database],
SUBSTRING(hostname, 1, 12) AS host, cmd, waittype, t.[text]
FROM sys.sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0) AND blocked = 0;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
p2.[program_name] AS program_blocking,
p1.[program_name] AS program_blocked,
DB_NAME(l.resource_database_id) AS [database],
p2.[hostname] AS host_blocking,
p1.[hostname] AS host_blocked,
t.wait_duration_ms,
l.request_mode,
l.resource_type,
t.wait_type,
(SELECT SUBSTRING(st.text, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1 ) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN (SELECT st.text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st WHERE p.spid = t.blocking_session_id) ELSE NULL END AS statement_blocking
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.dm_tran_locks AS l ON t.resource_address = l.lock_owner_address
INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
END;
GO
Запустим сбор данных в момент, когда в 1С зависает проведение или запись документа:
EXEC sp_Locks @Mode = 2, @Wait_Duration_ms = 1000;
Анализ результатов выполнения этой процедуры позволит точно сопоставить блокируемый SQL-запрос (statement_blocked) и блокирующий запрос (statement_blocking). Обратим особое внимание на колонку resource_type и wait_type. Если там фигурируют ожидания, связанные с базой данных tempdb или блокировками метаданных, корень проблемы лежит в неоптимальности самого кода 1С.
Рассмотрим комплекс мер, направленных на ликвидацию конфликтов блокировок:
Если запрос выполняется внутри транзакции слишком долго (например, более 5–10 секунд, а в особо запущенных случаях — 140 секунд и более), его оптимизация является задачей наивысшего приоритета. Проанализируем следующие моменты:
Рассмотрим архитектурный паттерн, при котором тяжелые аналитические или проверочные запросы выполняются до начала транзакции записи объекта. Если бизнес-логика позволяет, алгоритм должен выглядеть следующим образом:
// ПЛОХО: тяжелый запрос внутри обработчика записи
Процедура ПередЗаписью(Отказ, РежимЗаписи, РежимПроведения)
// Транзакция уже открыта платформой
РезультатЗапроса = ВыполнитьТяжелыйАналитическийЗапрос(); // Удерживает транзакцию!
КонецПроцедуры
// ХОРОШО: чтение данных до открытия транзакции
&НаКлиенте
Процедура ЗаписатьОбъект()
ПараметрыЗапроса = ПолучитьПараметрыДляАнализа();
РезультатАнализа = ВыполнитьАнализНаСервереБезТранзакции(ПараметрыЗапроса);
Если РезультатАнализа.РазрешеноЗаписать Тогда
ЭтотОбъект.Записать(); // Транзакция будет быстрой
Иначе
ВызватьИсключение "Запись невозможна!";
КонецЕсли;
КонецПроцедуры
Совместно с системным администратором выполним оптимизацию СУБД MS SQL Server:
tempdb на несколько равных частей (по количеству ядер процессора, но не более 8 файлов) для устранения конкуренции за системные страницы распределения памяти (PFS/SGAM).Max Degree of Parallelism (максимальная степень параллелизма) в значение, отличное от нуля (например, от 2 до 4, в зависимости от конфигурации сервера), чтобы не позволять одному неоптимальному запросу монополизировать все ядра процессора и вызывать взаимоблокировки параллелизма.Применение этих рекомендаций позволит полностью раскрыть потенциал управляемого режима блокировок в связке с RCSI, минимизировать простои пользователей и повысить общую стабильность системы 1С.