SQL Server: Управление транзакциями

Гленн Берри, Луи Девидсон и Тим Форд

Если вам нужно управлять операциями SQL Server на более детальном уровне, нужно тщательно продумать, как управлять связанными с транзакциями DMO-объектами (Dynamic Management Object). Все динамические административные представления (Dynamic Management View, DMV), относящиеся к категории "связанных с транзакциями", начинаются со строки "sys.dm_tran_".

В конечном итоге все инструкции, выполняемые в SQL Server, являются транзакционными. При выполнении даже одной инструкции SQL "под капотом" инициируется неявная транзакция. Она инициируется и автоматически завершается. При использовании явных команд BEGIN TRAN и COMMIT TRAN можно объединять их в явные транзакции, то есть наборы инструкций, которые должны выполняться все или ни одной.

В SQL Server реализованы различные уровни изоляции транзакций для гарантии таких свойств транзакций, как атомарность, согласованность, изоляция и долговечность (ACID). На практике это означает, что в них используются долго- и кратковременные блокировки для обеспечения транзактного доступа к общей базе данных и предотвращения того, чтобы транзакции не мешали друг другу.

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

Какие транзакции активны и какие сеансы в них открыты? (административные представления со словами session_transactions, active_transactions)
Какие транзакции больше всего делают большую часть работы? (административные представления со словами database_transactions)
Какие транзакции создают проблемы с блокировками? (административные представления со словом locks).

Из всех этих вопросов чаще всего административные представления используются для исследования блокировок. Со временем должна повышаться активность в области исследования активности при использовании уровня изоляции моментального снимка. Этот вид изоляции впервые появился вSQL Server 2005. Изоляция моментального уровня устраняет возможность блокировки и взаимной блокировки за счет использования хранилища версий в базе данных tempdb для обеспечения параллелизма, а не создания блокировок объектов БД. Существует несколько динамических административных представлений для анализа этого уровня изоляции.

Мониторинг "долгоиграющих" транзакций

Перейдем к анализу сценариев. Если не указано иное, все эти сценарии работают в SQL Server 2005, 2008 и 2008 R2 и всем им требуется разрешение VIEW SERVER STATE. В сценарии используются два динамических представления. Первое, sys.dm_tran_database_transactions, описано в электронной документации по SQL Server так: "Возвращает сведения о транзакциях на уровне базы данных".

Второе, sys.dm_tran_session_transactions, "возвращает сведения о взаимосвязях связанных транзакций и сеансов".

Лаконичное описание sys.dm_tran_database_transactions больше скрывает, чем описывает настоящую полезность этого представления. Следующий сценарий содержит запрос, который показывает для каждого сеанса, какие базы данных используются в определенной транзакции, открытой этим сеансом, была ли эта транзакция переведена в состояние только для чтения в какой-то из баз данных (по умолчанию большинство транзакций доступны только для чтения), когда это случилось, сколько записей внесено в журнал и сколько байт были задействованы от имени этих записей в журнале:

SELECT st.session_id , DB_NAME(dt.database_id) AS database_name , CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'

ELSE 'read-write' END AS transaction_state , dt.database_transaction_begin_time AS read_write_start_time , dt.database_transaction_log_record_count , dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt

ON st.transaction_id = dt.transaction_idORDER BY st.session_id , database_name

Такие запросы представления sys.dm_tran_database_transactions очень полезны для наблюдения таких вещей, как:

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

Обычная и краткосрочная блокировка

В нашем примере сценария используется динамическое представление sys.dm_tran_locks, предназначенное для работы с транзакциями и описанное в электронной документации так: "Возвращает сведения о ресурсах диспетчера блокировок, активного в данный момент. Каждая строка представляет текущий активный запрос диспетчеру блокировок о блокировке, которая была получена или находится в ожидании получения. Столбцы в результирующем наборе разделяются на две группы: ресурс и запрос. Группа ресурсов описывает ресурсы, на которые был выполнен запрос блокировки, а группа запросов описывает запрос блокировки".

Это административное представление полезно для выявления проблем с блокировками в экземпляре БД:

-- Look at active Lock Manager resources for current database

SELECT request_session_id ,

DB_NAME(resource_database_id) AS [Database] , resource_type , resource_subtype , request_type , request_mode , resource_description , request_mode , request_owner_type
FROM sys.dm_tran_locksWHERE request_session_id > 50 AND resource_database_id = DB_ID() AND request_session_id <> @@SPIDORDER BY request_session_id ;

-- Look for blocking

SELECT tl.resource_type , tl.resource_database_id , tl.resource_associated_entity_id , tl.request_mode , tl.request_session_id , wt.blocking_session_id , wt.wait_type , wt.wait_duration_msFROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC ;

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

Обычно для того, чтобы "поймать" блокировку, приходится несколько раз выполнять каждый из этих запросов. Если вы обнаружите две инструкции изменения данных или запрос и изменение данных, которые "сплелись" в жесткой или даже взаимной блокировке, вам потребуется извлечь текст SQL-запросов, проанализировать их, выполнить на тестовой системе (с включенной трассировкой посредством Profiler) и решить проблему путем изменения запросов или добавления индексов.

Ссылки по теме