Is it a bad practice to always create a transaction?

Question

Is it a bad practice to always create a transaction?

For example, it is a good practice to create a transaction for nothing but one simple SELECT?

What is the cost of creating a transaction when it is not really necessary?

Even if you are using an isolation level like READ UNCOMMITTED, is it a bad practice?

Answer 1

Is a bad practice always to create a transaction?

It depends on what context you are talking here. If it is an update, then I would highly recommend using transactions explicitly. If it is a SELECT then no (explicitly).

But wait there is more to understand first:
Everything in SQL Server is contained in a transaction.

When the session option IMPLICIT_TRANSACTIONS is OFF and you explicitly specify begin tran and commit/rollback then this is commonly known as an Explicit Transaction. Otherwise, you get an autocommit transaction.

When IMPLICIT_TRANSACTIONS is ON an implicit transaction is automatically started when executing one of the documented statement types (e.g. SELECT / UPDATE / CREATE) and it must be committed or rolled back explicitly. Executing a BEGIN TRAN in this mode would increment @@TRANCOUNT and start another "nested" transaction)

To switch which mode you're in, you'd use:

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

select @@OPTIONS & 2

If the above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

How much is the cost of creating a transaction when is not really necessary?

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.

Refer: Using Row Versioning-based Isolation Levels

Even if you are using isolation level read_uncomitted. Is a bad practice? Because it shouldn't have problems with locking.

READ_UNCOMMITED isolation level will allow dirty reads by definition i.e. One transaction will be able to see uncommitted changes made by other transaction. What this isolation level does is, it relaxes the overhead of locking - method of acquiring locks to protect database concurrency.

You can use this on a connection/query level, so that it does not affect other queries.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

An interesting article by Jeff Atwood describes deadlocks due to the 'Dining Philosophers Puzzle' and the read committed snapshot isolation level.

---

Out of curiosity, I did some tests measuring the impact on the transaction log with perfmon counters like Log Bytes Flushed/Sec, Log Flush Waits/Sec (No. of commits per sec that are waiting on LOG flush to occur) as below graph:

Sample code:

create table testTran (id int, Name varchar(8))
go

-- 19 sec
-- Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin 
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
---------------------------------------------------
-- 2 sec
-- Implicit transaction
SET IMPLICIT_TRANSACTIONS ON
declare @i int
set @i = 0
while @i < 100000
begin 
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF


----------------------------------------------------
-- 2 sec
-- Explicit transaction
declare @i int
set @i = 0
BEGIN TRAN
WHILE @i < 100000
Begin
INSERT INTO testTran values (1,'Kin Shah')
set @i = @i+1
End
COMMIT TRAN

Autocommit Transactions

• Insert took 19 secs.
• Every autocommit will flush the log buffer to the disk.

Implicit & Explicit Transaction

• Insert took 2 secs.
• For an explicit transaction, the log buffers will be flushed only when they are full.

There is a DMV sys.dm_tran_database_transactions that will return information about transactions at database level.

Obviously, this is more sort of a simplistic test to show the impact. Other factors like disk subsystem, database auto growth settings, initial size of the database, other processes running on the same server\database, etc will have an influence as well.

From the above tests, there is near to no difference between implicit & explicit transactions.

Answer 2

A SQL statement always runs in a transaction. If you don't start one explicitly, every SQL statement will run in a transaction of itself.

The only choice is whether you bundle multiple statements in one transaction. Transactions that span multiple statements leave locks that hurt concurrency. So "always" creating a transactions is not a good idea. You should balance the cost against the benefit.

Answer 3

The issue is whether a group of operations must be treated as a single action. In other words all of the operations must be completed and committed successfully or none of the operations can be committed. If you have a scenario that requires you to read preliminary data and then perform updates based on that data then the initial read should probably be part of the transaction. Note: I am avoiding Select/Insert/Update on purpose. The transaction scope may actually be at the application level and involve multiple database(s) operations. Think of classics patterns such as Airplane Seat Reservation or Bank Balance Query/Withdrawal. One must take a wider view of the problem to ensure the whole application yields reliable, consistent data.