Вводные данные:
- платформа 8.3.10.2753
- конфигурация Управление холдингом, редакция 3.0 (3.0.1.60)
- режим управления блокировками данных - управляемый
- режим совместимости - не использовать
- режим работы - клиент-сервер
- СУБД MS SQL 2014 Standard
Возникает конфликт блокировок SQL при выполнении запроса в транзакции, что довольно странно для базы, работающей в управляемом режиме блокировок данных.
Так как у базы включен управляемый режим блокировок данных, выключен режим совместимости и версия платформы 8.3, то запрос в транзакции должен выполняться с уровнем изоляции транзакций Read Committed Snapshot и конфликта блокировок не должно происходить.
Согласно методической литературе, в текущей конфигурации на платформе 8.3 запрос в транзакции может выполняться с уровнем изоляции, отличным от Read Committed Snapshot, в двух случаях:
1. База данных работает на MS SQL Server версии ниже MS SQL Server 2005. Этот пункт не выполняется, так как СУБД MS SQL 2014 Standard
2. Для базы принудительно выключен уровень изоляции транзакций Read Committed Snapshot. Проверили спец. скриптом - Read Committed Snapshot не отключали.
Каким образом возможен конфликт блокировок SQL при выполнении запроса в транзакции при управляемом режиме блокировок?
Еще если основной режим "Обычное приложение", тогда Read Committed Snapshot отключается. Но это, скорее всего, не ваш случай, поэтому нужно смотреть в SQL Management Studio, включен или нет, и если нет, включить и мониторить дальшейшее поведение.
SEL ECT 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
FR OM sys.dm_exec_sessions
LEFT JOIN sys.databases d ON sys.dm_exec_sessions.database_id = d.database_id
Должно быть всегда 'Read Committed' для твоего случая.
Но подозреваю, что дело не в этом.
/* return the one result set */
IF @Mode = 1
BEGIN;
SEL ECT
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,
(SEL ECT 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)
FR OM 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
(SEL ECT st.text
FR OM 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
--,t.resource_description AS blocking_resource_description
--,l.resource_associated_entity_id
FR OM 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;
/* return the first two result sets */
IF @Mode = 2
BEGIN;
SEL ECT
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]
FR OM sys.sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
WHERE spid IN (SEL ECT blocked FR OM sys.sysprocesses WHERE blocked <> 0)
AND blocked = 0;
SEL ECT
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,
(SEL ECT 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
)
FR OM 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
(SEL ECT st.text
FR OM 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
FR OM 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
WH ERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
END;
/* return all three result sets */
IF @Mode = 3
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]
FR OM sys.sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
WH ERE spid IN (SELECT blocked FR OM sys.sysprocesses WH ERE blocked <> 0)
AND blocked = 0;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
SUBSTRING(p2.[program_name], 1, 25) AS program_blocking,
SUBSTRING(p1.[program_name], 1, 25) 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)
FR OM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WH ERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN
(SELECT st.text
FR OM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
WH ERE p.spid = t.blocking_session_id) ELSE NULL
END AS statement_blocking
--,t.resource_description AS blocking_resource_description
--,l.resource_associated_entity_id
FR OM 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
WH ERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
SELECT DISTINCT
r.session_id AS spid,
r.percent_complete AS [percent],
r.open_transaction_count AS open_trans,
r.[status],
r.reads,
r.logical_reads,
r.writes,
s.cpu,
DB_NAME(r.database_id) AS [db_name],
s.[hostname],
s.[program_name],
--s.loginame,
--s.login_time,
r.start_time,
--r.wait_type,
r.wait_time,
r.last_wait_type,
r.blocking_session_id AS blocking,
r.command,
(SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
(CASE WHEN statement_end_offset = -1 THEN
LEN(CONVERT(NVARCHAR(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FR OM sys.dm_exec_sql_text(r.sql_handle)) AS [statement],
t.[text]
--,query_plan
FR OM sys.dm_exec_requests r
INNER JOIN sys.sysprocesses s ON s.spid = r.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) t
--CROSS APPLY sys.dm_exec_query_plan (r.plan_handle)
WH ERE r.session_id > 50 AND r.session_id <> @@spid
AND s.[program_name] NOT LIKE 'SQL Server Profiler%'
--AND db_name(r.database_id) NOT LIKE N'distribution'
--AND r.wait_type IN ('SQLTRACE_LOCK', 'IO_COMPLETION', 'TRACEWRITE')
ORDER BY s.CPU DESC;
END;
GO
Показать
Пускай админы запустят ее когда висит запись 1С
EXEC sp_Locks 3
Она выдаст три набора данных, по которым можно будет понять что происходит
(23) В тестовой среде выполнил хранимую процедуру, скрин во вложении. Конфликт блокировок на тестовой среде не воспроизводится, но насколько я понял по результату выполнения скрипта, есть ожидание на базе tempdb? Может быть конфликты блокировок возникают в базе tempdb?
(28) А что за скрипт выполнялся в Management Studio, который висел на блокировках tempdb? Судя по обрывку текста, он выбирал какие-то данные из системных таблиц. Если эти данные "цепляли" и данные временных таблиц, тогда ожидание вполне понятно (блокирующий сеанс в это время писал данные во временную таблицу с блокировкой всей таблицы).
Гоните метлой этих методистов.
Если конфликт блокировок возникает с участием блокировок на запись, то ни какое версионирование и управляемый режим не спасет.
Надо логировать контекст / код и устранять избыточные блокировки правкой кода, особенно если они связаны с укрупнением блокировок.
(8) У ТС блокировка возникает при выполнении запроса на чтение. В то время как READ COMMITTED SNAPSHOT гарантирует изоляцию читателей от соседних писателей. Пишущие блокировки не препятствуют чтению, так как чтение выполняется из снэпшота.
(12) Ессно записывается. Речь ведь о транзакционном чтении. Обычное чтение обсуждать смысла бы не было. Но ошибка транзакции записи происходит из-за таймаута ожидания блокировки на запросе чтения ("ошибка при вызове метода контекста Выполнить()"). Сам запрос на втором скриншоте.
(13) То, что на втором скриншоте, не позволяет ничего толком сказать. Хорошо бы увидеть план и sql текст запроса этого отчета и документа. И в коде выше могут ещё накладывать управляемые блокировки, может быть даже неявно, при чтении набора регистров например. Так же, не видно что тут есть транзация.
(14) Управляемые блокировки не пересекаются с блокировками СУБД. И что именно в том запросе - тоже не должно иметь значения. Если бы это был обычный read committed блокировочника, то ситуация была бы понятна - мы пытаемся прочитать данные, которые меняются соседней транзакцией и попадаем на блокировку. Но при read committed с опцией read committed snapshot такого быть не должно. Так что я вполне разделяю недоумение ТС.
(15) Подождите, ПриЗаписи это обработчик документа, который спотыкается на блокировках. А речь идет о том, что виновник этих блокировок данный отчет. Тут не известно есть ли транзакция (а на первом скрине есть галочка с транзакцией и без), неизвестен предшествующий код, который может вызывать блокировки на СУБД.
(16) это не отчет, а запрос в транзакции обработчика ПриЗаписи. Просто выделен в отдельный метод.
Имя документа Отчет может конечно сбивать, но достаточно вспомнить "Отчет о розничных продажах". Просто документ так назвали.
(16) Транзакция открывается в момент записи документа, см. вложение. При текущих условиях по идее выполнение запроса ни при каких обстоятельствах не должно вызывать конфликт блокировок на SQL, так как уровень изоляции RCSI. Вопрос, как такое в принципе возможно.
Нужно мониторить блокировки на сервере в этот момент. Тогда картина будет предельно ясна - кто кого и на чем именно блокирует.
А сколько в нормальной ситуации по времени выполняется этот запрос (если не в транзакции)? Если это очень тяжелый и кривой запрос, который сильно параллелится при этом на сервере, то у меня есть предположение.
(21) На запрос, скорее. Потому что если дело в параллелизме, значит скорее всего что-то не так с запросом. Хотя в некоторых случаях настройка этого свойства может улучшить общую ситуацию, но это уже тонкая оптимизация где универсальных советов быть не может. Нужно анализировать профиль нагрузки.
Просто когда запрос выполняется параллельно, то в мониторинге выглядит так, что параллельные части блокируются до тех пор, пока не выполнятся все "куски". Но может ли это привести в итоге к выдаче сабжевой ошибки - этого я не знаю.
(24) может не там ищите? Конечно нужно анализировать что сам sql выдает.
Но могу предположить, что просто отваливается по timeout времени транзакции. Т.е. сам запрос не попадает на взаимоблокировку, а просто не успевает отработать за время ограничения на транзакцию записи документа.
В итоге от блокировок помогла оптимизация запроса, но ответа на вопрос "каким образом возможен конфликт блокировок SQL при выполнении запроса в транзакции при управляемом режиме блокировок?" из-за отсутствия нормального доступа к серверам заказчика я так и не нашел. Во вложениях прилагаю исходную и оптимизированную типовую процедуры конфигурации УХ.