Узнаем текущий уровень транзакции в SQL Server

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

Чтобы установить глубину транзакции, обратитесь к переменной @@TRANCOUNT:

SELECT @@TRANCOUNT;

Уровень изоляции можно определить при помощи DBCC USEROPTIONS, найти нужную информацию поможет строка с isolation level:

DBCC USEROPTIONS;

Получите больше информации через DMV

Команда DBCC USEROPTIONS дает общее представление. Однако для более детального анализа сложных скриптов и приложений предоставляются системные динамические представления управления (DMV). Для определения уровня изоляции транзакций текущей сессии используйте sys.dm_exec_sessions:

-- В SQL Server уровень изоляции имеет комкованное значение!
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Константы уровней изоляции, согласно документации Microsoft:

• 0 = Не определён
• 1 = ReadUncommitted
• 2 = ReadCommitted
• 3 = RepeatableRead
• 4 = Serializable
• 5 = Snapshot

Баланс между параллелизмом и согласованностью

Выбор уровня изоляции – это поиск баланса между согласованностью данных и параллельностью процессов. В этом контексте ключевыми являются специфика работы системы и вероятность конфликтов между транзакциями. Понимание уровней изоляции может помочь решить проблемы, связанные с взаимными блокировками или замедлением транзакций.

Параллелизм на микроуровне

Анализ воздействия различных уровней изоляции на производительность включает в себя их тестирование и сравнение результатов. Высокие уровни, такие как Serializable, обеспечивают строгую согласованность, но могут замедлить параллельное выполнение процессов в системе. Регулировка скриптов для специфических сценариев и версий SQL Server – это ключ к оптимальному сочетанию целостности данных и производительности.

Руководство по выбору уровней изоляции

• READ UNCOMMITTED: Хороший выбор для случаев, когда абсолютная точность данных не является критической.
• READ COMMITTED: Обеспечивает баланс между точностью данных и производительностью.
• REPEATABLE READ: Необходим, когда важно сохранять последовательность чтения неизменной между запросами.
• SERIALIZABLE: Обеспечивает максимальную согласованность данных, но может привести к снижению параллелизма.

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

Уровни изоляции транзакций можно сопоставить с уровнями охраны:

Каждый следующий уровень предоставляет более строгую изоляцию и защиту от конфликтов и фантомного чтения.

Уровень изоляции устанавливается командой:

SET TRANSACTION ISOLATION LEVEL выбранный_уровень;

Проверить текущий уровень можно следующим образом:

SELECT CASE transaction_isolation_level 
       WHEN 1 THEN 'READ UNCOMMITTED'
       WHEN 2 THEN 'READ COMMITTED'
       WHEN 3 THEN 'REPEATABLE READ'
       WHEN 4 THEN 'SERIALIZABLE'
       WHEN 5 THEN 'SNAPSHOT'
       END AS transaction_isolation_level 
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Иерархия уровней может быть визуализирована следующим образом:

🚶‍♂️🔓 > 🔐🚪 > 🔒🚪 > 🛡️🚪 // Каждый символ "двери" представляет уровень изоляции повыше.

Глубина транзакций против уровня изоляции

Сравнивать глубину транзакции (@@TRANCOUNT) и уровень изоляции можно сравнить с сопоставлением яблок и апельсинов – это совершенно разные вещи. Если @@TRANCOUNT отражает количество активных транзакций, то уровень изоляции регулирует взаимодействие между ними.

Кейс с несколькими базами данных

В сложных системах, когда транзакции влияют на несколько баз данных, критически важно контролировать их уровень изоляции. Для этого используется запрос с несколькими соединениями:

-- Две базы данных – это всегда лучше, чем одна!
SELECT s.transaction_isolation_level, d.name AS 'database'
FROM sys.dm_exec_sessions s
JOIN sys.databases d ON s.database_id = d.database_id
WHERE session_id = @@SPID;

Диагностика проблем

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

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

1. SET TRANSACTION ISOLATION LEVEL (Transact-SQL) – SQL Server | Microsoft Learn — Детальное руководство по управлению уровнем изоляции транзакции в SQL Server.
2. PostgreSQL: Documentation: 16: 13.2. Transaction Isolation — Документация по уровням изоляции транзакции в PostgreSQL.
3. MySQL :: MySQL 8.0 Reference Manual :: 13.3.7 SET TRANSACTION Statement — Инструкция по настройке уровня изоляции транзакций в MySQL.
4. Data Concurrency and Consistency — Разъяснение уровней изоляции транзакций в Oracle.
5. Using Transactions (The Java™ Tutorials > JDBC Database Access > JDBC Basics) — Управление изоляцией транзакций с использованием JDBC.