Конфликт блокировок SQL при выполнении запроса в режиме управляемых блокировок

1. kholkin 207 15.11.18 16:29 Сейчас в теме
Добрый день!

Вводные данные:
- платформа 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 при выполнении запроса в транзакции при управляемом режиме блокировок?
Прикрепленные файлы:
user879687; +1 Ответить
По теме из базы знаний
Вознаграждение за ответ
Показать полностью
Ответы
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
2. JohnGalt 58 15.11.18 16:36 Сейчас в теме
Еще если основной режим "Обычное приложение", тогда Read Committed Snapshot отключается. Но это, скорее всего, не ваш случай, поэтому нужно смотреть в SQL Management Studio, включен или нет, и если нет, включить и мониторить дальшейшее поведение.
3. kholkin 207 15.11.18 16:46 Сейчас в теме
(2) Основной режим запуска - Управляемое приложение.
Доступа у меня к SQL Management Studio нет, просил сисадминов выполнить скрипт

SEL ECT is_read_committed_snapshot_on FR OM sys.databases
WHERE name= 'DataBaseName'

Скрипт возвращает 1, значит RSCI включен.
4. JohnGalt 58 15.11.18 16:48 Сейчас в теме
(3) А уровень изоляции какой установлен?
5. kholkin 207 15.11.18 16:49 Сейчас в теме
(4) А где это посмотреть?
6. JohnGalt 58 15.11.18 16:54 Сейчас в теме
(5) В таблице параметров базы, таблица называется sys.databases
7. herfis 508 15.11.18 17:07 Сейчас в теме
(5) Для текущих сессий можно посмотреть так:
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' для твоего случая.
Но подозреваю, что дело не в этом.
10. kholkin 207 15.11.18 18:55 Сейчас в теме
(7) попрошу завтра сисадминов выполнить скрипт
11. herfis 508 15.11.18 19:02 Сейчас в теме
(10) Пусть на всякий случай проверят, какой режим совместимости для базы установлен.
19. kholkin 207 16.11.18 09:25 Сейчас в теме
(7) Скрипт вернул 36 строк с одним ответом: Read Commited.
Compatibility level в свойствах базы: SQL Server 2014.

Ничего необычного...
23. herfis 508 16.11.18 10:56 Сейчас в теме
Надыбал такую хранимку для мониторинга блокировок:
CRE ATE PROCEDURE dbo.sp_Locks
(
@Mode int = 2,
@Wait_Duration_ms int = 1000 /* 1 seconds */
)
/*
19/04/2008 Yaniv Etrogi
http://www.sqlserverutilities.com
*/
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000

/* 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

Она выдаст три набора данных, по которым можно будет понять что происходит
25. kholkin 207 16.11.18 14:22 Сейчас в теме
(23) поверчу на тестовом серваке сначала этот запрос, спасибо
28. kholkin 207 19.11.18 10:39 Сейчас в теме
(23) В тестовой среде выполнил хранимую процедуру, скрин во вложении. Конфликт блокировок на тестовой среде не воспроизводится, но насколько я понял по результату выполнения скрипта, есть ожидание на базе tempdb? Может быть конфликты блокировок возникают в базе tempdb?
Прикрепленные файлы:
29. herfis 508 19.11.18 13:02 Сейчас в теме
(28) А что за скрипт выполнялся в Management Studio, который висел на блокировках tempdb? Судя по обрывку текста, он выбирал какие-то данные из системных таблиц. Если эти данные "цепляли" и данные временных таблиц, тогда ожидание вполне понятно (блокирующий сеанс в это время писал данные во временную таблицу с блокировкой всей таблицы).
8. Gilev.Vyacheslav 1916 15.11.18 17:55 Сейчас в теме
(0)
"Согласно методической литературе, "
Гоните метлой этих методистов.
Если конфликт блокировок возникает с участием блокировок на запись, то ни какое версионирование и управляемый режим не спасет.
Надо логировать контекст / код и устранять избыточные блокировки правкой кода, особенно если они связаны с укрупнением блокировок.
9. herfis 508 15.11.18 18:12 Сейчас в теме
(8) У ТС блокировка возникает при выполнении запроса на чтение. В то время как READ COMMITTED SNAPSHOT гарантирует изоляцию читателей от соседних писателей. Пишущие блокировки не препятствуют чтению, так как чтение выполняется из снэпшота.
12. DarkUser 15.11.18 21:57 Сейчас в теме
(9) Так на скрине же видно что там что-то записывается в базу. Возможно отчет пытается сохранить полученные данные?
13. herfis 508 15.11.18 22:20 Сейчас в теме
(12) Ессно записывается. Речь ведь о транзакционном чтении. Обычное чтение обсуждать смысла бы не было. Но ошибка транзакции записи происходит из-за таймаута ожидания блокировки на запросе чтения ("ошибка при вызове метода контекста Выполнить()"). Сам запрос на втором скриншоте.
14. DarkUser 15.11.18 22:28 Сейчас в теме
(13) То, что на втором скриншоте, не позволяет ничего толком сказать. Хорошо бы увидеть план и sql текст запроса этого отчета и документа. И в коде выше могут ещё накладывать управляемые блокировки, может быть даже неявно, при чтении набора регистров например. Так же, не видно что тут есть транзация.
15. herfis 508 15.11.18 22:48 Сейчас в теме
(14) Управляемые блокировки не пересекаются с блокировками СУБД. И что именно в том запросе - тоже не должно иметь значения. Если бы это был обычный read committed блокировочника, то ситуация была бы понятна - мы пытаемся прочитать данные, которые меняются соседней транзакцией и попадаем на блокировку. Но при read committed с опцией read committed snapshot такого быть не должно. Так что я вполне разделяю недоумение ТС.
Так же, не видно что тут есть транзация.

Обработчик "ПриЗаписи" - это транзакция записи.
16. DarkUser 15.11.18 23:05 Сейчас в теме
(15) Подождите, ПриЗаписи это обработчик документа, который спотыкается на блокировках. А речь идет о том, что виновник этих блокировок данный отчет. Тут не известно есть ли транзакция (а на первом скрине есть галочка с транзакцией и без), неизвестен предшествующий код, который может вызывать блокировки на СУБД.
17. spacecraft 15.11.18 23:31 Сейчас в теме
(16) это не отчет, а запрос в транзакции обработчика ПриЗаписи. Просто выделен в отдельный метод.
Имя документа Отчет может конечно сбивать, но достаточно вспомнить "Отчет о розничных продажах". Просто документ так назвали.
18. kholkin 207 16.11.18 09:09 Сейчас в теме
(16) Транзакция открывается в момент записи документа, см. вложение. При текущих условиях по идее выполнение запроса ни при каких обстоятельствах не должно вызывать конфликт блокировок на SQL, так как уровень изоляции RCSI. Вопрос, как такое в принципе возможно.
Прикрепленные файлы:
20. herfis 508 16.11.18 10:18 Сейчас в теме
(18)
Вопрос, как такое в принципе возможно.

Нужно мониторить блокировки на сервере в этот момент. Тогда картина будет предельно ясна - кто кого и на чем именно блокирует.
А сколько в нормальной ситуации по времени выполняется этот запрос (если не в транзакции)? Если это очень тяжелый и кривой запрос, который сильно параллелится при этом на сервере, то у меня есть предположение.
21. kholkin 207 16.11.18 10:23 Сейчас в теме
(20) Подозреваете, что возможно дело в свойстве сервера Max Degree of Parallelism?
22. herfis 508 16.11.18 10:30 Сейчас в теме
(21) На запрос, скорее. Потому что если дело в параллелизме, значит скорее всего что-то не так с запросом. Хотя в некоторых случаях настройка этого свойства может улучшить общую ситуацию, но это уже тонкая оптимизация где универсальных советов быть не может. Нужно анализировать профиль нагрузки.
Просто когда запрос выполняется параллельно, то в мониторинге выглядит так, что параллельные части блокируются до тех пор, пока не выполнятся все "куски". Но может ли это привести в итоге к выдаче сабжевой ошибки - этого я не знаю.
24. kholkin 207 16.11.18 14:21 Сейчас в теме
(22) 140 секунд запрос выполняется, запрос вроде из типовой, недопиленый, но я его еще толком не анализировал.
26. spacecraft 16.11.18 16:09 Сейчас в теме
(24) может не там ищите? Конечно нужно анализировать что сам sql выдает.
Но могу предположить, что просто отваливается по timeout времени транзакции. Т.е. сам запрос не попадает на взаимоблокировку, а просто не успевает отработать за время ограничения на транзакцию записи документа.
27. tyazhovkin 17.11.18 15:13 Сейчас в теме
Похожее было при большой нагрузке и большом количестве операций от каждого пользователя, проблема сама решилась, когда нагрузка спала.
30. kholkin 207 08.06.19 16:36 Сейчас в теме +1 $m
В итоге от блокировок помогла оптимизация запроса, но ответа на вопрос "каким образом возможен конфликт блокировок SQL при выполнении запроса в транзакции при управляемом режиме блокировок?" из-за отсутствия нормального доступа к серверам заказчика я так и не нашел. Во вложениях прилагаю исходную и оптимизированную типовую процедуры конфигурации УХ.
Прикрепленные файлы:
Типовая процедура.txt
Доработанная процедура.txt
Оставьте свое сообщение

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот