Как проверить в реальном времени блокировки в SQL Server 2005

Быстрый ответ

Для просмотра активных блокировок на таблицах в SQL Server применяется совокупность динамических управляющих представлений sys.dm_tran_locks и sys.partitions. Следующий запрос выведет информацию о блокировках, активных на таблице 'YourTableName':

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    l.resource_type, 
    l.request_mode,
    l.request_session_id
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE OBJECT_NAME(p.object_id) = 'YourTableName';

Вместо 'YourTableName' введите имя той таблицы, которая вас интересует. В результате выполняется вывод информации о типе блокировки, ее состоянии и сессии, которая инициировала ее.

Выявление блокировок

Для идентификации операций, ждущих снятия блокировки, используйте следующий запрос. Он покажет информацию о процессах, в ожидании снятия блокировки, и spid блокировавшей сессии:

SELECT cmd, * 
FROM sys.sysprocesses 
WHERE blocked > 0;

Если блокировка негативно влияет на работу системы, можно применить команду KILL {spid}, которая требует особой осторожности и внимательности.

Отслеживание блокировок таблиц

Чтобы узнать, кто инициировал блокировку и какую команду выполнил, выполните следующий запрос:

SELECT t.text, r.session_id
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

Анализ данных о блокировках

Для удобства анализа можно создать персональную функцию sp_lock, предоставляющую данные о типах блокировок, используемых ресурсах и их роли в транзакциях. Это позволит упростить работу с уже существующими системными вариантами.

Визуализация

Для наглядности представим блокировки таблиц как занятые места в кинотеатре. Мы находимся в зрительном зале, где демонстрируется фильм "Матрица 4":

Кинозал 🎥: [Место 1, Место 2, Место 3, Место 4]

🔓 Нет блокировки: [👤, 👤, 🆓, 👤]
🔒 Блокировка:     [👤, 👤, 🔐, 👤]

Для проверки, кто занял данные места (установил блокировку):

SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';

Результат проверки мест:

🔍 Проверка блокировок:
- Место 1 (👤): Свободно
- Место 2 (👤): Свободно
- Место 3 (🔐): Занято ЗАПРОСОМ 🍿
- Место 4 (👤): Свободно

Суть идеи: таким же образом, как мы узнаем, свободно ли место в кинотеатре, в базе данных мы выясняем наличие блокировок в таблице.

Разбор типов блокировок

Различные виды блокировок указывают на разное управление доступом. Объединив sys.dm_tran_locks с системными таблицами, можно получить подробные данные:

SELECT 
    t.name AS TableName,
    l.resource_type,
    l.resource_description,
    l.request_mode,
    l.request_status,
    OBJECT_NAME(p.object_id) AS ResourceName
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
JOIN sys.tables t ON p.object_id = t.object_id
WHERE t.name = 'YourTableName';

Данный запрос позволит подробно рассмотреть описание ресурса и уровень запроса.

Мониторинг блокировок в режиме реального времени

Для проведения детального мониторинга блокировок следует отфильтровать в sys.dm_tran_locks неактуальные блокировки и сосредоточиться на активных транзакциях:

SELECT 
    DB_NAME(l.resource_database_id) AS DatabaseName,
    s.session_id,
    s.login_name,
    r.command,
    l.request_mode
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE l.resource_database_id = DB_ID('YourDatabaseName')
AND l.resource_type = 'OBJECT';

Работа с распределёнными транзакциями

При работе с распределёнными транзакциями используйте request_owner_guid из sys.dm_tran_locks, чтобы отслеживать блокировки, касающиеся нескольких баз данных или серверов.

Полезные материалы

1. Руководство по блокировкам и версионированию строк в транзакциях SQL Server | Microsoft Learn — Основательное руководство по механизмам блокировок в SQL Server.
2. PostgreSQL: Документация: 28.3. Просмотр блокировок — Официальная документация по мониторингу блокировок в PostgreSQL.
3. MySQL :: Руководство по MySQL 8.0 :: Блокировка метаданных — Ревью механизма блокировки метаданных в MySQL 8.0.
4. MySQL :: Руководство по MySQL 8.0 :: SHOW OPEN TABLES — Справка о команде SHOW OPEN TABLES для мониторинга блокировок таблиц в MySQL.
5. Блокировка файлов и параллелизм в SQLite Версия 3 — Сведения о механизмах блокировки файлов и параллелизма в SQLite.
6. Настройка sendmail за файерволом – Stack Overflow — Обсуждение на Stack Overflow, как обнаружить блокировки за session_id.