Greetings & Salutations!
sheldonhull.com
Back to posts

Implicit Transactions

sql-server

Never messed around with this setting in the server configuration, so I was unfamilar with the impact it would have.If I ran a statement with something like the following:

insert into foo select bar insert into foo select bar

I know that if the first had an aborting error, such as text was too long, the second statement would not complete as the batch would have failed. If you instead did

insert into foo select bar GO insert into foo select bar

and had the same error, the second would be completed, since the first would throw an error, but the GO separates the second statement explicitly into another batch, and therefore another transaction. Interestingly, the Implicit Transactions option changes the behavior to making each statement act as if it was encapsulated by begin transaction --- commit transaction instead of requiring this to be defined. So if you set implicit transactions on and ran the statement below with no go statement:

insert into foo select bar insert into foo select bar

It is really operating as if:

begin transaction insert into foo select bar commit transaction GO begin transaction insert into foo select bar commit transaction

MSDN - Implicit Conversions is a resource that further documents the behavior, indicating that a rollback for the particular transaction is handled automatically. This means that since that each statement is treated as a transaction that it will not abort the second statement and terminate execution if the first experinces the error, since by “implicit conversions” this would be handled separately. MSDN article with example code to walk through it https://technet.microsoft.com/en-us/library/ms190230(v=sql.105).aspx

/******************************************************* STEP 1: SETUP / use tempdb; set nocount on; set xact_abort off; set implicit_transactions off; if object_id(‘dbo.TestImplicitTrans’,‘U’) is not null begin print ‘Dropped dbo.TestImplicitTrans per existed’; drop table dbo.TestImplicitTrans; end; print ‘create table dbo.TestImplicitTrans’; create table dbo.TestImplicitTrans ( test_k int primary key identity(1,1) not null ,random_text varchar(5) not null ); go / TEST 1: xact_abort off set implicit_transactions off Results in:

  • first transaction fails
  • second transaction succeeds (this is due to xact_abort off not being activated) test_k random_text 2 12345 *******************************************************/ use tempdb; go set nocount on; set xact_abort off; set implicit_transactions off; truncate table dbo.TestImplicitTrans; print ‘Statement 1 START’; insert into dbo.TestImplicitTrans (random_text) values (‘00001x’); print ‘Current trancount: ’ + cast(@@trancount as varchar(100)); insert into dbo.TestImplicitTrans (random_text) values (‘00002’); print ‘Current trancount: ’ + cast(@@trancount as varchar(100)); insert into dbo.TestImplicitTrans (random_text) values (‘00003’); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 1 END’; print char(13) + char(13) + ‘Statement 2 START’; insert into dbo.TestImplicitTrans (random_text) values (‘12345’ — random_text - varchar(5) ); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 2 END’; select

from dbo.TestImplicitTrans as TIT; go /******************************************************* TEST 2: xact_abort on set implicit_transactions off Results in:

  • first transaction fails
  • second transaction doesn’t execute due to xact abort being set on test_k random_text NONE *******************************************************/ use tempdb; go set nocount on; set xact_abort on; set implicit_transactions off; truncate table dbo.TestImplicitTrans; print ‘Statement 1 START’; insert into dbo.TestImplicitTrans (random_text) values (‘12345x’ — random_text - varchar(5) ONE CHARACTER TOO LARGE ); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 1 END’; print char(13) + char(13) + ‘Statement 2 START’; insert into dbo.TestImplicitTrans (random_text) values (‘12345’ — random_text - varchar(5) ); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 2 END’; select

from dbo.TestImplicitTrans as TIT; go /******************************************************* TEST 2: xact_abort off set implicit_transactions off Results in:

  • first transaction fails
  • second transaction doesn’t execute due to xact abort being set on test_k random_text NONE *******************************************************/ use tempdb; go set nocount on; set xact_abort on; set implicit_transactions on; truncate table dbo.TestImplicitTrans; print ‘Statement 1 START’; insert into dbo.TestImplicitTrans (random_text) values (‘12345x’ — random_text - varchar(5) ONE CHARACTER TOO LARGE ); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 1 END’; print char(13) + char(13) + ‘Statement 2 START’; insert into dbo.TestImplicitTrans (random_text) values (‘12345’ — random_text - varchar(5) ); print ‘Successfully inserted: ’ + cast(@@rowcount as varchar(10)); print ‘Statement 2 END’; select

from dbo.TestImplicitTrans as TIT; go