gifts2017

Кто что блокирует, MS SQL + 1C

Опубликовал andrey begimot (andrey-prog) в раздел Администрирование - Оптимизация БД (HighLoad)

Думаю каждый программист 1С работающий в более-менее крупной компании сталкивался с проблемой блокировки при проведении. Блокировка исчезает, как только ресурсы освободятся (проведется другой документ который блокировал регистр), но бывает что блокировка зависает надолго, а иногда очень надолго.

Введение

Все здравствуйте!

Думаю каждый программист 1С работающий в более-менее крупной компании сталкивался с проблемой блокировки при проведении. Блокировка исчезает, как только ресурсы освободятся (проведется другой документ который блокировал регистр), но бывает что блокировка зависает надолго, а иногда очень надолго. Перезагружать сервер можно, но не много (до тех пор пока вас не выгонят с работы).

Необходимо локализовать проблему в коде и в структуре БД. Первое что приходит на ум - отладчик, но на самом деле это уже второй шаг, первым делом надо понять кто-что блокирует.

В случае SQL-сервером, это сделать наконец-то удалось. Приведенный ниже код SQL-запроса уже неоднократно помогал мне найти условного "виновника" (условного потому что виноват не пользователь, а программист который написал "кривой" или неоптимизированный код). Надеюсь поможет в работе многим. Код только частично мой, в основном был собран из разных источников с простора интернета, но к сожалению ссылок не сохранилось.

Использование

В повседневной работе достаточно задействовать только первый блок "/*кто кого*/". Открываем MS SQL Server Management Studio, правой на корень - "new query" - вставляем код запроса (только верхнюю часть до "Кто что блокирует", остальное - для детального анализа), выполняем запрос (вверху есть кнопочка выполнения запроса).

Выполнив запрос, запоминаем "ID виновника", быстренько идем в консоль сервера 1С, заходим в ветку "Сеансы" нашей базы. Видим все соединения с 1С-сервером, ищем там колонку "Соединение с СУБД", чтобы увидеть соединения 1С-сервера с MSSQL-сервером. В колонке будет всего несколько заполненных значений, среди них и будет "ID виновника".

Что делать если его там нет, тут 3 варианта:

1. Вернитесь в MSSQL и сделайте запрос еще несколько раз подряд, если значения меняются или таблица вообще пуста - постоянной блокировки нет, у вас (уже) все в порядке.

2. Сеанс который блокирует MSSQL находится в другой базе т.е. блокировка не в той базе (можно попробовать задействовать ветку всех сеансов в консоли 1С-сервера) - вернитесь в MSSQL и внимательно посмотрите в колонку DB в ней находится название базы.

3. Бывает что ID процесса в 1С-консоли вообще отсутствует, такое тоже бывает если у вас есть какие-то внешние программы подключенные напрямую в базу 1С, если пускаете кого-то в MSSQL напрямую, то вариант не исключен.

Код SQL-запроса

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbid AS smallint;
use [ВАШАБАЗА];
SET @dbid=DB_ID();

/*кто кого*/
SELECT DB_NAME(pr1.dbid) AS 'DB'
      ,pr1.spid AS 'ID жертвы'
      ,RTRIM(pr1.loginame) AS 'Login жертвы'
      ,pr2.spid AS 'ID виновника'
      ,RTRIM(pr2.loginame) AS 'Login виновника'
      ,pr1.program_name AS 'программа жертвы'
      ,pr2.program_name AS 'программа виновника'
      ,txt.[text] AS 'Запрос виновника'
FROM   MASTER.dbo.sysprocesses pr1(NOLOCK)
       JOIN MASTER.dbo.sysprocesses pr2(NOLOCK)
            ON  (pr2.spid = pr1.blocked)
       OUTER APPLY sys.[dm_exec_sql_text](pr2.[sql_handle]) AS txt
WHERE  pr1.blocked <> 0

/* Кто что блокирует */
SELECT s.[nt_username]
      ,request_session_id
      ,tran_locks.[request_status]
      ,rd.[Description] + ' (' + tran_locks.resource_type + ' ' + tran_locks.request_mode + ')' [Object]
      ,txt_blocked.[text]
      ,COUNT(*) [COUNT]
FROM   sys.dm_tran_locks AS tran_locks WITH (NOLOCK)
       JOIN sys.sysprocesses AS s WITH (NOLOCK)
            ON  tran_locks.request_session_id = s.[spid]
       JOIN (
                SELECT 'KEY' AS sResource_type
                      ,p.[hobt_id] AS [id]
                      ,QUOTENAME(o.name) + '.' + QUOTENAME(i.name) AS [Description]
                FROM   sys.partitions p
                       JOIN sys.objects o
                            ON  p.object_id = o.object_id
                       JOIN sys.indexes i
                            ON  p.object_id = i.object_id
                            AND p.index_id = i.index_id
                UNION ALL
                SELECT 'RID' AS sResource_type
                      ,p.[hobt_id] AS [id]
                      ,QUOTENAME(o.name) + '.' + QUOTENAME(i.name) AS [Description]
                FROM   sys.partitions p
                       JOIN sys.objects o
                            ON  p.object_id = o.object_id
                       JOIN sys.indexes i
                            ON  p.object_id = i.object_id
                            AND p.index_id = i.index_id
                UNION ALL
                SELECT 'PAGE'
                      ,p.[hobt_id]
                      ,QUOTENAME(o.name) + '.' + QUOTENAME(i.name)
                FROM   sys.partitions p
                       JOIN sys.objects o
                            ON  p.object_id = o.object_id
                       JOIN sys.indexes i
                            ON  p.object_id = i.object_id
                            AND p.index_id = i.index_id
               
                UNION ALL
                SELECT 'OBJECT'
                      ,o.[object_id]
                      ,QUOTENAME(o.name)
                FROM   sys.objects o
            ) AS RD
            ON  RD.[sResource_type] = tran_locks.resource_type
            AND RD.[id] = tran_locks.resource_associated_entity_id
       OUTER APPLY sys.[dm_exec_sql_text](s.[sql_handle]) AS txt_Blocked
WHERE  (
           tran_locks.request_mode = 'X'
           AND tran_locks.resource_type = 'OBJECT'
       )
       OR  tran_locks.[request_status] = 'WAIT'
GROUP BY
       s.[nt_username]
      ,request_session_id
      ,tran_locks.[request_status]
      ,rd.[Description] + ' (' + tran_locks.resource_type + ' ' + tran_locks.request_mode + ')'
      ,txt_blocked.[text]
ORDER BY
       6 DESC
       

IF EXISTS ( SELECT  Name

            FROM    tempdb..sysobjects

            WHERE   name LIKE '#LOCK_01_01%' )
    DROP TABLE #LOCK_01_01


CREATE TABLE #LOCK_01_01

    (

      spid INT,

      dbid INT,

      ObjId INT,

      IndId SMALLINT,

      Type VARCHAR(20),

      Resource VARCHAR(50),

      Mode VARCHAR(20),

      Status VARCHAR(20)

    )

INSERT  INTO #LOCK_01_01

EXEC sp_lock


select OBJECT_NAME(ObjId) as [Имя объекта], Mode [Тип блокировки (код)],

CASE
     WHEN Mode='Sch-S' THEN 'Блокировка стабильности схемы. Гарантирует, что элемент схемы, такой как таблица или индекс, не будет удален до тех пор, пока сеанс связи удерживает блокировку стабильности схемы на данный элемент схемы;'

 WHEN Mode='Sch-М' THEN '= Блокировка изменения схемы. Должен поддерживаться любым сеансом связи, во время которого предполагается изменить схему данного ресурса. Гарантирует, что другие сеансы не имеют ссылок на обозначенный объект;'

 WHEN Mode='S' THEN 'S = Коллективная блокировка. Удерживающему сеансу предоставлен коллективный доступ к ресурсу;'

 WHEN Mode='U' THEN 'U = Блокировка обновления. Указывает блокировку обновления, полученную на ресурсы, которые со временем могут быть обновлены. Используется для предотвращения общей формы взаимоблокировки, которая возникает, когда множество сеансов блокируют ресурсы для потенциального обновления в последующее время;'

 WHEN Mode='X' THEN 'X = Монопольная блокировка. Удерживающему сеансу предоставлен исключительный доступ к ресурсу;'

 WHEN Mode='IS' THEN 'IS = Блокировка с намерением коллективного доступа. Указывает намерение поместить S блокировки на некоторые подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='IU' THEN 'IU = Блокировка с намерением обновления. Указывает намерение поместить U блокировки на некоторые подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='IX' THEN 'IX = Блокировка с намерением монопольного доступа. Указывает намерение поместить X блокировки на некоторые подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='SIU' THEN 'SIU = Коллективная блокировка с намерением обновления. Указывает коллективный доступ к ресурсу с намерением получения блокировок обновления на подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='SIX' THEN 'SIX = Коллективная блокировка с намерением монопольного доступа. Указывает коллективный доступ к ресурсу с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='UIX' THEN 'UIX = Блокировка обновления с намерением монопольного доступа. Указывает блокировку обновления ресурса с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок;'

 WHEN Mode='BU' THEN 'BU = Блокировка массового обновления. Используется для массовых операций;'
     --[ ELSE else_result_expression ]
END as [Тип блокировки]

,syspr.spid, syspr.dbid, syspr.open_tran, syspr.status, syspr.hostprocess, syspr.loginame, syspr.hostname

 From
#LOCK_01_01

inner join master.dbo.sysprocesses as syspr
on syspr.spid = #LOCK_01_01.spid and syspr.dbid = #LOCK_01_01.dbid

where
#LOCK_01_01.Type = 'TAB'
and
#LOCK_01_01.dbid = @dbid     

/* Чем занят сервер*/
SELECT s.[spid]
      ,s.[loginame]
      ,s.[open_tran]
      ,s.[blocked]
      ,s.[waittime]
      ,s.[cpu]
      ,s.[physical_io]
      ,s.[memusage]
       INTO #sysprocesses
FROM   sys.[sysprocesses] s

WAITFOR DELAY '00:00:01'

SELECT txt.[text]
      ,s.[spid]
      ,s.[loginame]
      ,s.[hostname]
      ,DB_NAME(s.[dbid]) [db_name]
      ,SUM(s.[waittime] -ts.[waittime]) [waittime]
      ,SUM(s.[cpu] -ts.[cpu]) [cpu]
      ,SUM(s.[physical_io] -ts.[physical_io]) [physical_io]
      ,s.[program_name]
FROM   sys.[sysprocesses] s
       JOIN #sysprocesses ts
            ON  s.[spid] = ts.[spid]
            AND s.[loginame] = ts.[loginame]
       OUTER APPLY sys.[dm_exec_sql_text](s.[sql_handle]) AS txt
WHERE  s.[cpu] -ts.[cpu]
       + s.[physical_io] -ts.[physical_io]
       > 500
       OR  (s.[waittime] -ts.[waittime]) > 3000
GROUP BY
       txt.[text]
      ,s.[spid]
      ,s.[loginame]
      ,s.[hostname]
      ,DB_NAME(s.[dbid])
      ,s.[program_name]
ORDER BY
       [physical_io] DESC
       
DROP TABLE #sysprocesses

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Александр Шкураев (salexdv) 09.07.13 10:16
(0) "Виновника", как правило, можно вычислить просто взглянув на колонку "Заблокировано СУБД" в списке соединений базы или кластера. А вот детали - это уже интересно
2. Данила Елистратов (CagoBHuK) 09.07.13 11:34
Чем не понравилась обработка Славы Гилева?
3. andrey begimot (andrey-prog) 09.07.13 12:12
Что-то где-то видел, про Гилева наслышан, статьи читал..
4. Яков Коган (Yashazz) 09.07.13 14:12
(2) Кстати, я её что-то потерял из виду, можно ссылку, если есть навскидку?
5. Данила Елистратов (CagoBHuK) 09.07.13 16:38
Есть тока сама обработка. Правила запрещают размещать ее. :(
6. Иван (SinglCOOLer) 10.07.13 07:19
(5) CagoBHuK, скиньте пжл на почту эту обработку, хочу чтобы все средства анализа были, уже замучили блокировки? написал в личку
7. Dragon Ago (DragonAgo) 10.07.13 10:05
Кто-то постоянно что - то делает, а другие лишь твердят, что ты работать просто не умеешь
8. Кикос Одинэсенко (servs) 10.07.13 10:28
Очень интересно. Спасибо, плюсую!
9. Кикос Одинэсенко (servs) 10.07.13 10:35
Небольшое дополнение:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Указывает, что инструкции могут считывать строки, которые были изменены другими транзакциями, но еще не были зафиксированы.
10. andrey begimot (andrey-prog) 10.07.13 10:46
(1) Ошибаетесь, не в большинстве, максимум в 50%, "Заблокировано СУБД" может быть больше у жертвы, из этого следует большая неоднозначность в каком месте конфигурации надо копать (по сути ответа вообще не получить).
VladimirL; +1 Ответить
11. Ирина Х (Amara) 10.07.13 11:33
Спасибо. Интересно не только обнаружение "виновника" но и анализ и выявление проблемного куска кода.
12. muha muhaha (fr.myha) 10.07.13 12:45
Интересно, но сложно кажется.
13. qweasd qweasdzc (serega3333) 10.07.13 13:41
(2) CagoBHuK, а какая обработка имеется ввиду?
за скрипт автору спасибо!
14. Евгений Мартыненков (JohnyDeath) 10.07.13 14:43
Мне кажется, сложновато будет прыгать туда-сюда как описывает автор.
Может проще ТехЖурнал настроить на блокировки и прочие долгие операции?
15. Вячеслав Гилёв (Gilev.Vyacheslav) 11.07.13 00:19
quNas; wwk; Valet; h00k; tormozit; Aleskey_K; zaursoft; JohnyDeath; +8 Ответить 3
16. Вячеслав Гилёв (Gilev.Vyacheslav) 11.07.13 00:20
17. Вячеслав Гилёв (Gilev.Vyacheslav) 11.07.13 00:22
(13) serega3333, см (15), ту, которую я написал в апреле 2008 года :)
18. Виталий Змиевский (francisco) 11.07.13 08:57
Все эти обработки заточены под MS SQL? Для Postgre SQL нет ничего похожего?
19. andrey begimot (andrey-prog) 11.07.13 13:21
(18) francisco, В запросе участвуют системные таблицы MSSQL, для Postgre SQL конечно не подойдет он. В Postgre наверное тоже есть доступ к системной информации, попробуйте погуглить по блокировкам в Postgre, запрос то "не знает" что мы 1с-ники)), блокировки они в любой базе могут быть, может что-то найдете общее для Postgre, а оно и для базы 1С подойдет, пишите.
20. Игорь Иванов (paybaseme) 14.07.13 22:49
(16) Gilev.Vyacheslav, на вашем сайте выложен Скрипт обновления индексов на ms sql server. А вот хотелось бы полюбопытствовать, этот скрипт нужно запускать плюсом к "стандартным", которые рекомендует 1С делать? (реиндексация и дефрагментация индексов)
21. Вячеслав Гилёв (Gilev.Vyacheslav) 15.07.13 13:01
22. Сергей Старых (tormozit) 16.07.13 13:33
(15) Кроме этой сыроватой обработки, вашего облачного сервиса и ЦУП есть другие инструменты? Техножурнал на сколько я заметил довольно мало информации по блокировке регистрирует.
23. Вячеслав Гилёв (Gilev.Vyacheslav) 16.07.13 13:43
не понятен вопрос: Вас не устраивает функционал? не устраивает цена? другое?
24. Сергей Старых (tormozit) 16.07.13 14:04
(23) Имеются ввиду инструменты, написанный под платформу 1С и позволяющие выполнять анализ блокировок в базах 1С. Функционал LOCK1C меня конечно не удовлетворяет, т.к. продукт старый не использует многие современные возможности платформы. Облачный сервис не устраивает оперативностью. ЦУП не устраивает опять же оперативностью, сложностью настройки и низкой надежностью.
25. Вячеслав Гилёв (Gilev.Vyacheslav) 16.07.13 14:11
Облачный сервис не устраивает оперативностью.
Какая оперативность нужна?
26. Сергей Старых (tormozit) 16.07.13 14:32
27. Вячеслав Гилёв (Gilev.Vyacheslav) 16.07.13 14:49
надо понимать, что это "почти моментально", т.е. на таком промежутке технологический журнал "еще недоступен", а значит снимок будет только данных со скуля, контекст кода 1с еще будет не доступен, а ведь его надо не просто "получить", но еще пропарсить и свернуть до аналитики
т.е. устраивает ли в течении 5 секунд видет только блокировки скуля (без блокировок 1с)?

или все таки придеться признать что еще нужно пропарсить управляемые блокировки, выполнять корреляционный анализ и смериться с тем что анализ происходит "постфактум"?
28. Сергей Старых (tormozit) 16.07.13 14:56
(27) Никто не спорит что здесь имеет место традиционный баланс между оперативностью и глубиной анализа. В каких то задачах важнее первое, в каких то второе. Техножурнал на таком промежутке вполне доступен (смотри трассировку запроса в консоли запросов из подсистемы "Инструменты разработчика" http://www.youtube.com/watch?v=0YrEHuMmd3g&feature=share&list=UUhi09WYkuu4uLc6rrdc9Dog).
29. Вячеслав Гилёв (Gilev.Vyacheslav) 16.07.13 14:58
(28) tormozit, я не хочу критиковать, но не надо путать схему когда в базе сидит только один разработчик и для эксперимента держит 3 сессии для отладки и базу на 500 пользователей
Инструмент разработчика за 5 секунд проанализирует блокировки всех пользователей? серьезно?
мы сейчас о каком случаи говорим?
30. Сергей Старых (tormozit) 16.07.13 15:19
> Инструмент разработчика за 5 секунд проанализирует блокировки всех пользователей? серьезно?
Я такого не утверждал. Я утверждал о доступности получения данных технологического журнала с задержкой 5 сек. Конечно здесь влияет, насколько большой поток событий пишется в журнал.

> путать схему когда в базе сидит только один разработчик и для эксперимента держит 3 сессии для отладки и базу на 500 пользователей
У нас пока нет 500 пользователей. Возможно поэтому мы друг друга не понимаем.
31. Вячеслав Гилёв (Gilev.Vyacheslav) 16.07.13 15:43
Ну тогда возращаемся к исходному вопросу: какая оперативность должна быть у сервисов, которые работают на любых проектах? Не надо путать "массовый продукт" и способный работать на узком спектре.
Нам не сложно для отдельной учетки сервисов изменить оперативность анализа, но цена - уменьшение количества анализируемой информации.
На платных проектах мы делаем "и не такое" :)
Даже больше скажу, мы готовы интегрировать "инструмент разработчика" в наши сервисы и вы сами поймете что не все так просто, как кажется с виду. Сейчас в наших сервисах лежат "террабайты" аналитики, ваш инструмент врядли сможет "отрисовать" даже визуально, зато мы могли бы сотрудничать, раз оба продукта бесплатные.
32. andrey begimot (andrey-prog) 17.07.13 05:10
Если кому-то интересно, нашелся код для определения блокировок для PostgreSQL, кто может проверьте, рабочий или нет..

"-- For PostgreSQL Version < 9.2
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
     kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.procpid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.procpid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;


-- For PostgreSQL Version >= 9.2
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;"
...Показать Скрыть
Sol; morevalar; Gilev.Vyacheslav; +3 Ответить
33. Dimon (klel) 17.07.13 10:27
Занятная статейка нужно опробовать =) автору спасибо буду пробовать
35. Станислав Чернов (uinx) 28.08.13 03:32
Ругается:
Сообщение 8115, уровень 16, состояние 2, строка 179
Ошибка арифметического переполнения при преобразовании expression к типу данных int.

На строке: SELECT txt.[text]
36. aleksei (alekseies) 28.08.13 17:09
sg 102, Level 15, State 1, Line 19
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 191
Incorrect syntax near '.'.
37. aleksei (alekseies) 28.08.13 17:19
sg 102, Level 15, State 1, Line 19
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 191
Incorrect syntax near '.'.

Все ОК! Не ту базу выбрал .........
38. Владимир Аникин (Vo-Va) 27.09.13 13:03
(15)Gilev.Vyacheslav, а есть что то подобное для PostgreSQL?
39. Вячеслав Гилёв (Gilev.Vyacheslav) 11.10.13 19:10
Постргресс - версионник, если там долгие блокировки, значит надо переписывать алгоритм - а для этого мы пока не придумали как делать "диагностику" )
40. Вячеслав Корендясов (madfox) 28.12.13 04:08
41. Albert A (albert) 16.01.14 16:01
БП 2.0, SQL 2008 SP3
конфигурация немного доработана, но ОСВ и регистры бухгалтерии не изменялись
пользователь начинает собирать ОСВ и программа иногда повисает и не отвисает за разумное время.
запрос выдает следущее:
Прикрепленные файлы:
42. andrey begimot (andrey-prog) 16.01.14 18:09
(41) - по крайней мере взаимоблокировок нет, реиндексацию базы делаете? Посмотрите также в сторону увеличения быстродействия, например перенести tempdb на отдельный диск, выделить mssql побольше памяти и т.п.
43. Вячеслав Гилёв (Gilev.Vyacheslav) 18.01.14 10:10
(41) albert, прежде всего надо обсчитать статистку с полным сканированием
44. Сергей Старых (tormozit) 16.11.14 21:07
Как получить информацию об установленных в текущий момент управляемых блокировках?
45. Vova Petrov (bdsmka) 21.11.14 08:13
Сообщение 102, уровень 15, состояние 1, строка 19
Неправильный синтаксис около конструкции ".".
Сообщение 102, уровень 15, состояние 1, строка 71
Неправильный синтаксис около конструкции ".".
Сообщение 102, уровень 15, состояние 1, строка 191
Неправильный синтаксис около конструкции ".".

Как победить?
46. Рамиль Баширов (bashirov.rs) 30.01.15 15:00
Доступно, понятно, но сложновато в том, что бегать надо "туда-обратно". Спасибо за код!
47. Евген Сок (6630) 25.03.15 06:20
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа