TRANSACTION

◎ 查詢目前狀態為ACTIVE的TRANSACTION

SELECT [s_tst].[session_id],
       [s_es].[login_name] AS [Login Name],
       DB_NAME (s_tdt.database_id) AS [Database],
       [s_tdt].[database_transaction_begin_time] AS [Begin Time],
       [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
       [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
       [s_est].text AS [Last T-SQL Text],
       [s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt]
JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN sys.[dm_exec_sessions] [s_es] ON [s_es].[session_id] = [s_tst].[session_id]
JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er] ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC

--備註：
--1. Inner的Transaction不可以被ROLLBACK，若非得要，可以搭配 SAVE TRAN [Name]
--2. 如果ROLLBACK後面沒有接名字，則會ROLLBACK前面所有的TRAN
--3. COMMIT會執行最靠近的TRAN
--4. ROLLBACK最外層的TRAN，INNER的TRAN都會被ROLLBACK

◎ SavePoint

BEGIN TRAN Tranl;
PRINT @@TRANCOUNT; -- 1
SAVE TRAN SavePoint;
PRINT @@TRANCOUNT; -- 2

UPDATE  dbo.BankAccount
SET     BankAccountName = 'NewName'
WHERE   BankAccountID = 1

ROLLBACK TRAN SavePoint;
PRINT @@TRANCOUNT; -- 1
COMMIT TRAN Tranl;