SQL Server – Implicit Transaction – Tracking

Forward

Recently encountered session blocking sourced to “Implicit Transactions“.

That post is here.

Wanted to give more data on when the Transaction is actually started.

Lab

Create Table

Create Sample Table


use [tempdb]
go

if object_id('[dbo].[otter]') is null
begin

	create table [dbo].[otterBox]
	(

		  [id] int not null identity(1,1)

		, [dateAdded] datetime not null 
			constraint [constraintOtterBoxDateAdded]
				default getutcdate()

		, [addedBy] datetime not null 
			constraint [constraintOtterBoxAddedBy]
				default SYSTEM_USER

		, constraint [PK_OTTER_BOX] primary key
			(
				[id]
			)
	)



end


 

Track Transactions

Outline

  1. Set Implicit Transactions On
  2. Get data from other sources other than table
    • Get Transaction Count
  3. Get data from table
    • Get Transaction Count
  4. Get data post transaction count query
  5. Rollback Transaction
    • Get Transaction Count

 

Code


SET IMPLICIT_TRANSACTIONS ON
go

set transaction isolation level READ COMMITTED;
--set transaction isolation level read uncommitted;
go
 
SELECT
          [SOURCE-getdate()] = getdate()
        , [USER_NAME()] = USER_NAME()
        , [SYSTEM_USER()] = SYSTEM_USER
 
SELECT
        [SOURCE-@@TRANCOUNT-BEFORE] = @@TRANCOUNT
         
select top 5 *
 
from  [tempdb].[dbo].[otterBox]
 
select
     
      [SOURCE]
        = '@@TRANCOUNT-POST-SELECT-TABLE'
       
    , [@TRANCOUNT]
        = @@TRANCOUNT
 
    , [sessionID]
        = tblDTST.session_id
 
    , [transactionName]
        = tblDTAT.[name]
 
    , [transactionType]
        = case tblDTAT.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
 
    , [transactionBeginTS]
        = tblDTAT.transaction_begin_time

	, [transactionIsolationLevel]
		= case tblDMES.transaction_isolation_level 
				WHEN 0 THEN 'Unspecified' 
				WHEN 1 THEN 'ReadUncommitted' 
				WHEN 2 THEN 'ReadCommitted' 
				WHEN 3 THEN 'Repeatable' 
				WHEN 4 THEN 'Serializable' 
				WHEN 5 THEN 'Snapshot'
			end
 
from   sys.dm_tran_session_transactions tblDTST
 
inner join sys.dm_tran_active_transactions tblDTAT
 
        on tblDTST.transaction_id = tblDTAT.[transaction_id]

left outer join sys.dm_exec_sessions tblDMES

		on tblDTST.[session_id] = tblDMES.[session_id]
 
where   tblDTST.session_id = @@SPID
 
 
SELECT
        [SOURCE-@@TRANCOUNT-BEFORE-ROLLBACK] = @@TRANCOUNT
 
rollback
 
SELECT
        [SOURCE-@@TRANCOUNT-AFTER-ROLLBACK] = @@TRANCOUNT

 

Output

tracksessiontransactions_20170217_0840am

Explanation

  1. We queried system functions such as getdate, user_name, SYSTEM_User
    • We queried @@TRANCOUNT
      • @@TRANCOUNT came back as 0
  2. Queried an actual table
    • We queried @@TRANCOUNT
      • @@TRANCOUNT came back as 1
      • Transaction name came back as implicit_transaction
  3. Before rollback
    • Queried for Transaction Count and validated as 1
  4. After rollback
    • Queried for Transaction Count and validated reset to 0

Summary

When “Implict_Transaction” is set on:

  1. Transaction is only started when an actual table is queried, updated, inserted, and deleted
  2. Transaction Count is held at 1 until commit or rollback

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s