sql transactions – Nested transactions, Saving transactions

Today I come across an interesting article about SQL Transactions by Kenneth Fisher. This blog and subsequent pointers explains very well about transactions, nested transactions, saving transactions and best practices of using transactions in stored procedures.

I was experimenting around nested transactions, you can play around by changing the parameters in following script

SET NOCOUNT ON

DECLARE @NestlevCommit3 tinyint
DECLARE @NestlevCommit2 tinyint
DECLARE @NestlevCommit1 tinyint

set @NestlevCommit3 = 1 -- 1 or 0 to Commit or Rollback 
set @NestlevCommit2 = 0 -- 1 or 0 to Commit or Rollback 
set @NestlevCommit1 = 1 -- 1 or 0 to Commit or Rollback 


if (OBJECT_ID('tempdb..##table1') is not NULL)
	drop table ##table1

create table ##table1(val varchar(60))

insert ##table1 values ('0. Zero Level before starting transaction')

print 'line 20: ' + convert(varchar(2),@@TRANCOUNT)
begin transaction -- Level 1
	print 'line 22: ' + convert(varchar(2),@@TRANCOUNT)
	insert ##table1 values ('1. First Level Before Saving Transaction')
	save transaction transavel3
	print 'line 25: ' + convert(varchar(2),@@TRANCOUNT)
	insert ##table1 values ('2. First Level After Saving Transaction')
	begin transaction -- Level 2
		print 'line 28: ' + convert(varchar(2),@@TRANCOUNT)
	insert ##table1 values ('3. Second Level Before Saving Transaction')
		save transaction transavel2
	insert ##table1 values ('4. Second Level After Before Saving Transaction')
		print 'line 32: ' + convert(varchar(2),@@TRANCOUNT)

		begin transaction -- Level 3
			print 'line 35: ' + convert(varchar(2),@@TRANCOUNT)
			insert ##table1 values ('5. Third Level Before Saving Transaction')
			save transaction transavel1		
			insert ##table1 values ('6. Third Level After Saving Transaction')
			print 'line 39: ' + convert(varchar(2),@@TRANCOUNT)

			if (@NestlevCommit3 = 0 and @@TRANCOUNT > 0)
				rollback transaction transavel1
			if (@@TRANCOUNT > 0)
				commit

		print 'line 46: ' + convert(varchar(2),@@TRANCOUNT)
	
	if (@NestlevCommit2 = 0 and @@TRANCOUNT > 0)
		rollback transaction transavel2
	if (@@TRANCOUNT > 0)
		commit

	print 'line 53: ' + convert(varchar(2),@@TRANCOUNT)

if (@NestlevCommit1 = 1)
begin
	if (@@TRANCOUNT > 0)
		commit
end
else
	if (@@TRANCOUNT > 0)
		rollback 

select * from ##table1
print 'line 65: ' + convert(varchar(2),@@TRANCOUNT)

drop table ##table1</code>

Updated on 2014/2/12: You can also find all option transactions in SQL server using following query

SELECT  DTAT.transaction_id
FROM    sys.dm_tran_active_transactions DTAT
WHERE   DTAT.name <> 'worktable' ; 
Advertisements
This entry was posted in Script, SQL, SQL Query and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

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