Transactions, Chatty or Chunky?

Chatty or Chunky? What do you mean? Running code block with a transaction can ensure the atomicity of the code, all done or all undone. In defult programming mode, select statement will never start a transaction automatically. Data modification language, such insert, delete, update, merge, send, receive, etc, will automatically start a transaction as the command starts if there isn’t any transactions and will commit
automatically (if there isn’t any transactions). I call this kind of strategy Chatty.

I call it chunky when more data modification within a transaction and complete (commit/rollback) all of them at the end. It’s important to know the differences between those 2 strategies. Why? Let’s have a test here

use test
set nocount on
if object_id('table1') is not null
	drop table table1
go
create table table1 (id int)
go
truncate table table1
go
declare @i int = 0, @StartTime datetime2(3) = sysdatetime()
while @i< 3000
begin
	insert into table1 values (CHECKSUM(NEWID()))
	select @i = @i +1
end
select DATEDIFF(millisecond, @StartTime, sysdatetime()) [Chatty]
--529
go
truncate table table1
declare @i int = 0, @StartTime datetime2(3) = sysdatetime()
begin transaction
while @i< 3000
begin
	insert into table1 values (CHECKSUM(NEWID()))
	select @i = @i +1
end
commit
select DATEDIFF(millisecond, @StartTime, sysdatetime()) [Chunky]
--50
go

Chatty
-----------
1019

Chunky
-----------
35

Inserting 3000 records to a table with chatty strategy, my laptop needs to take 1.019 seconds to finish. However, by using chunky strategy, it only takes 0.035 seconds to complete, which is almost 30 times faster than chatty strategy.

One of the items in SQL Server “Best Practice Guide” is keep transaction small. This has been followed as truth by many developers neglecting the real truth in db world is “It depends”. Dealing with the size of a transaction is always an art. Bigger chunk of the transaction migh prevent records from being accessed by other sessions. This is blocking issue. Using chunky stategy might also lead transaction logs grow rapidly, which migh affect the other components of SQL Server such as transactional replication, backup/recovery, Change data captures, etc. But it provides performance!

SQL Server does natively support “chunky” strategy by using use set implicit_transactions on. By default, implicit transacions is set to off. Once this is set, the first SQL statement of a code block which also refereneces a table will cause a transaction start if there isn’t any transacitons.

set implicit_transactions on
select @@TRANCOUNT TansactionCountBeforeSelect
select top 0 * from table1
select @@TRANCOUNT TansactionCountAfterSelect
commit

TansactionCountBeforeSelect
---------------------------
0

id
-----------

TansactionCountAfterSelect
--------------------------
1

From the example above, a transaction is started automatically when “select top 0…” command started. Back old days, it’s almost impossible to set this flag on in a busy system due to the blocking. Starting from version SQL Server 2005, SQL Server supports read committed snapshot isolation level. It effectively reduces blockings in which makes this kind of strategy possible. Shall you change your current SQL programming style? It depends again. At least, this is the best alternative when you want to make big amount of changes to a table without employing bulk insert. What if you are in a distributed transaction environment? A chatty stagegy over such environment will kill the performance.