Ещё раз про транзакции

Бывает три типа транзакций

autocommit
это режим используемый по умолчанию, любое запущенное выражение sql автоматически фиксируется после своего завершения. Например если при попытке выполнения двух комманд вставки, одна из них отработала успешно , а вторая с ошибкой. Успешная вставка будет принята сервером, поскольку эти две вставки автоматически выполнялись в разных транзакциях. Такой тежим не всегда является допустимым, часто атомарнустью должно обладать не одно, а несколько выражений sql.

implicit
В этом режиме транзакция автоматически открывается после выполнения одного из выражений : ALTER TABLE, FETCH, REVOKE, CREATE,
GRANT, SELECT, DELETE, INSERT, TRUNCATE TABLE, DROP, OPEN, and UPDATE
Зафиксировать открытую таким образом транзакцию можно при помощи Commit или откатить rollback.
В пределах сессии imlicit режим включается при пощи конструкции
SET IMPLICIT_TRANSACTIONS ON

exmplicit
В этом случае транзакции запускаются и прекращаются явно.


Зависшие транзакции приводят к нежелательным блокировкам и к разрастанию журнала выполнения транзакций.

Для того чтобы увидеть незавершённые транзакции существует комманда
DBCC OPENTRAN('DatabaseName')

Дополнительную информацию о выполняемых на сервере транзакциях можно получть при помощи динамического представления sys.dm_tran_session_transactions

Пример:
SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1


Текст последнего выполненового в соединении запроса можно увидеть с помощью функции sys.dm_exec_sql_text и представления sys.dm_exec_connections

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE session_id = 54


Последние запущенные выражения sql видны в представлении sys.dm_exec_requests

Информацию о типе и состоянии запущенных транзакций можно получть с помощью
sys.dm_tran_active_transactions

SELECT transaction_begin_time,
CASE transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END tran_type,
CASE transaction_state
WHEN 0 THEN 'not been completely initialized yet'
WHEN 1 THEN 'initialized but has not started'
WHEN 2 THEN 'active'
WHEN 3 THEN 'ended (read-only transaction)'
WHEN 4 THEN 'commit initiated for distributed transaction'
WHEN 5 THEN 'transaction prepared and waiting resolution'
WHEN 6 THEN 'committed'
WHEN 7 THEN 'being rolled back'
WHEN 8 THEN 'been rolled back'
END tran_state
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 145866

Обобщим последовательность действий по выявлению долго выполняющихся транзакций:
1) Используем sys.dm_tran_session_transactions чтобы установить соответствия между идентификаторами сессий и транзакций
2) Используем sys.dm_exec_connections и sys.dm_exec_sql_text для того чтобы найти самые старые запросы в сесии (most_recent_sql_handle)
3) Используем sys.dm_tran_active_transactions для того чтобы определить как долго была открыта транзакция , её тип и состояние