Поиск блокировок в MS SQL Server
13.10.2022
itpro
SQL Server
Один комментарий
Блокировки в SQL Server позволяют обеспечивать целостность данных при одновременном изменении несколькими пользователя. SQL Server блокирует объекты в таблице при начале транзакции и снимает блокировку при ее завершении. В этой статье мы научимся искать блокировки в базе данных MS SQL Server и удалять их.
Можно сымитировать блокировку одной из таблиц с помощью незакрытой транзакции (которая не завершена через rollback или commit). Например, выполните такой SQL запрос:
USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents
SQL Server перед внесением изменений сначала заблокирует таблицу. Попробуйте открыть SQL Server Management Studio и выполнить простой SQL запрос на выборку:
SELECT * FROM tblStudents
Запрос зависнет в состоянии ( Executing query ) пока не отвалится по таймауту. Дело в том, что запрос SELECT пытается обратиться к данным в таблице, которая заблокирована SQL Server-ом.
Чтобы вывести список заблокированных запросов в MSSQL Server, выполните команду:
select cmd,* from sys.sysprocesses
where blocked > 0
В колонке Blocked указан идентификатор процесса PID процесса, который заблокировал ресурсы. Здесь же видно и время ожидания для данного запроса (waittime в милисекундах). Можно использовать это поле для поиска наиболее старых блокировок.
select * FROM
master.dbo.sysprocesses
where 1=1
—and blocked <> 0
and spid = 59
По SPID процесса можно получить код последнего SQL запроса, выполнено в рамках данного процесса (транзакции):
Для принудительного завершения процесса и снятия блокировки, выполните команду:
Например, в моем случае это:
Если блокировки возникают постоянно, и вы хотите определить самые ресурсоемкие запросы, можно создать отдельную хранимую процедуру:
CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end — @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line
Теперь для вывод кода SQL запроса, который заблокировал таблицу, нужно указать только его SPID:
Exec PrintCurrentCode 51
Также код запроса можно получить по sql_handle процесса блокировки. Например:
select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)
Для поиска блокировок в MS SQL Server можно использовать Microsoft SQL Server Management Studio. Вы можете использовать один из следующих методов:
- Щелкните правой кнопкой по северу, запустите Activity Monitor и разверните Processes. Список запросов, ожидающих освобождения ресурсов указан со статусом SUSPENDED.
- Выберите базу данных -> Reports -> All Blocking Transactions. Здесь также видно список заблокированных запросов и SPID источника блокировки.
Предыдущая статья Следующая статья 
Диагностика взаимоблокировок
Мы продолжаем серию публикаций о блокировках в БД. В предыдущей части мы рассмотрели виды блокировок, с которыми столкнулись при разработке ПО и рассказали что такое взаимоблокировки.
Важной задачей является обнаружение и диагностика блокировок, для дальнейшего их анализа и своевременного принятия мер по их устранению. В данной статье рассмотрим, какими средствами мы пользуемся для решения данной задачи.
Журналы приложений (логи)
Основными средствами для обнаружения проблем в разрабатываемых нами приложениях являются журналы приложения. Взаимоблокировки не являются исключением. Вот так примерно они выглядят в наших логах:
1) текстовые логи:
2) журнал событий Windows (Eventlog):
Счетчики производительности
При разработке программных продуктов, в нашей компании используются счетчики производительности для подсчета количественных и средних значений различных показателей, в том числе взаимоблокировок. В мониторе счетчиков производительности, мы имеем возможность наблюдать возникновение взаимоблокировок.
Подробнее о сборе метрик взаимоблокировок будет сказано в следующей части.
MS SQL profiler
Для обнаружения, какие транзакции взаимно блокируются, мы пользуемся инструментом SQL Profiler, который позволяет наглядно увидеть следующую информацию:
– какие запросы привели к взаимной блокировке,
– какие ресурсы были заблокированы в транзакциях,
– какой тип блокировки был наложен на ресурсы,
– какая транзакция стала жертвой менеджера блокировок.
Для запуска мониторинга взаимоблокировок нужно в SQL Profiler, в свойствах трассировки выбрать события блокировок Locks и запустить саму трассировку.
Если во время трассировки произойдут события взаимной блокировки, они зарегистрируются в созданной нами трассе. Подробности зарегистрированных в трассе взаимных блокировок можно просмотреть, выделив его в списке событий (см. Рис. 4).
В подробностях видно какие транзакции взаимно заблокировались и какие ресурсы были запрошены. Рассмотрим пример на рис.7. По рисунку видно, что заблокировались 2 транзакции, которые запросили уже заблокированные записи друг у друга. Транзакция на рисунке слева (ProcessId = 79) запросила на блокировку обновления ресурс, занятый транзакцией справа (ProcessId=67). И наоборот, транзакция на рисунке справа (ProcessId=67) запросила на блокировку обновления ресурс, занятый транзакцией слева (ProcessId = 79). Общим ресурсом в обоих случаях является первичный ключ записи в таблицах Match и AdjudicationResult. Стрелки на графе показывают направление блокировки, а над ними отображается тип запрошенной блокировки или тип наложенной блокировки. Если подвести мышкой на транзакцию, то во всплывающей подсказке будет отображен запрос выполняемый в рамках конкретной транзакции. Проанализировав какие запросы вызывают взаимную блокировку, можно их модифицировать, чтобы разрешить взаимную блокировку.
Регистрация блокировок в журнале сервера MS SQL Server
MS SQL Server предоставляет возможность вывода возникающих ошибок блокировки в свой журнал трассировки с помощью флагов трассировки. Это полезно, например, если необходим сбор трассы в течение длительного периода времени (дни, недели). Для вывода ошибок блокировок в журнал сервера, необходимо включить флаги Trace Flag 1204 и Trace Flag 1222:
После этого в журнале MS SQL Server можно увидеть возникающие события блокировок:
Системные хранимые процедуры
Наложенные блокировки также можно посмотреть через системные хранимые процедуры/функции SQL Server. Для просмотра текущих блокировок используется системная хранимая функция sp_lock, которая возвращает следующую информацию:
Имя колонки
Описание
spid -Идентификатор процесса SQL Server.
dbid -Идентификатор базы данных.
ObjId -Идентификатор объекта, на который установлена блокировка.
IndId -Идентификатор индекса.
Type -Тип объекта. Может принимать значения: DB, EXT, TAB, PAG, RID, KEY.
Resource — Содержимое колонки syslocksinfo.restext. Обычно это идентификатор строки (для типа RID) или идентификатор страницы (для типа PAG).
Mode -Тип блокировки. Может принимать значения: Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeIn-Null, RangeIn-S, RangeIn-U, RangeIn-X, RangeX-S, RangeX-U, RangeX-X.
Status -Статус процесса SQL Server. Может принимать значения: GRANT, WAIT, CNVRT.
На рисунке 7 представлен пример использования функции sp_lock. На нем видно, что на три записи наложена совмещаемая блокировка типа KEY, это ключи выбираемых записей. Если вызывать функцию sp_lock без параметров, то она вернет абсолютно все блокировки всех процессов. Можно вывести блокировки только определенных процессов, передав идентификаторы процессов через запятую. Например, вызвав exec sp_lock 55, Мы выведем блокировки только процесса с идентификатором 55 (см. Рис. 7).
SQL Server имеет также функции sp_who и sp_who2 для просмотра активных в данный момент процессов. Разница между этими функциями только в составе выдаваемых данных, sp_who2 выдает больше информации. С помощью этой функции мы можем увидеть какие процессы в данный момент активны и в каком они состоянии. Нас прежде всего интересует состояние блокировки процессов, которые можно увидеть с помощью этой функции.
Sp_who2 показывает, все блокировки на том экземпляре SQL Server, где имеются проблемы. Запуск sp_who2 на проблемном сервере показывает, что там действительно есть заблокированные процессы, как следует из поля BlkBy в результатах процедуры, см. Рис. 8.
Можно с первого взгляда наглядно определить, что SPID 56 заблокирован SPID 54.
sp_who2 возвращает результирующий набор со следующими сведениями:
Имя колонки
Описание
SPID -Идентификатор процесса SQL Server.
Status — Состояние процесса
Login -Имя входа процесса
HostName -Имя хоста, который инициировал процесс
BlkBy -Идентификатор процесса, заблокировавший текущий процесс
DBName -Имя БД, к которому обратился процесс
Command -Исполняемая процессом команда или имя системного процесса ядра СУБД
CPUTime -Время выполнения процесса
DiskIO -Количество операций чтения/записи с диска
LastBatch -Время последнего вызова удаленной хранимой процедуры или инструкции EXECUTE клиентским процессом.
ProgramName -Имя приложения
REQUESTID -Идентификатор запроса. Применяется для идентификаций запросов, выполняемых в текущем сеансе.
Заключение
Итак, в данной статье мы рассмотрели следующие способы обнаружения и диагностики взаимоблокировок:
– журналы приложений (логи);
– штатные средства MS SQL Server.
Также кратко затронули, как пользоваться инструментами диагностики взаимоблокировок:
– MS SQL Profiler;
– регистрация блокировок в журнале MS SQL Server;
– использование системных хранимых процедур sp_lock, sp_who2.
Для быстрого обнаружения лучше всего использовать журналы приложения и счетчики производительности.
Штатные средства MS SQL Server лучше использовать при диагностике и исправлении проблем с взаимоблокировками.
Запись в журнал MS SQL Server диагностической информации может понадобиться в случае, если взаимоблокировка возникает очень редко и профайлером здесь не обойтись.
В следующей части статьи рассмотрим, какие виды взаимоблокировок бывают и как с ними бороться.
Автор статьи: Николай Иванов, Старший Разработчик, ITA Labs
How to check which locks are held on a table
How can we check which database locks are applied on which rows against a query batch?
Any tool that highlights table row level locking in real time?
DB: SQL Server 2005
7 Answers 7
This is not exactly showing you which rows are locked, but this may helpful to you.
You can check which statements are blocked by running this:
It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks.
Edit to add comment from @MikeBlandford:
The blocked column indicates the spid of the blocking process. You can run kill
to fix it.
To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it’s a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.
There are multiple different versions of «friendlier» sp_lock procedures available online, depending on the version of SQL Server in question.
In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it’s now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to «roll your own» sp_lock function here.
You can find current locks on your table by following query.
If multiple instances of the same request_owner_type exist, the request_owner_id column is used to distinguish each instance. For distributed transactions, the request_owner_type and the request_owner_guid columns will show the different entity information.
Devdrama
Просмотр блокирующих соединений в MS SQL in english
Написание объемных процедур для автоматизации производства неизбежно влечёт за собой использование транзакций, что порой вызывает головную боль: малейшая невнимательность может заблокировать базу.
Приведённый ниже фрагмент кода отображает подключенные логины, заблокированные базы, источник подключения и идентификатор сессии. Эти подключения и нужно сбрасывать. То есть, kill 67 . Сбросили, и копаем дальше.
По желанию можно поиграть с request_mode и посмотреть на другие типы подключений (активные, спящие).