Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Transaction for select ? As if you are gonna rollback select ? –  IvanP May 28 '13 at 16:21
    
@IvanP I now is stupid... but I looking for a good awnser whith a good explanation to know it this has and impact en performance. –  elranu May 28 '13 at 16:32
2  
Starting a transaction for a SELECT isn't stupid if you're using transaction-level snapshot isolation, or if you need to maintain the locks taken by the SELECT (any isolation level) after the statement completes. –  Jon Seigel May 28 '13 at 17:18
    
Looking at impact of BEGIN TRAN SELECT ... COMMIT vs just SELECT there appears to be an extremely minor performance difference. –  Martin Smith Sep 6 '13 at 17:13

2 Answers 2

up vote 27 down vote accepted
+100

Is a bad practice to create a transaction always?

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 statement types documented in the books online article (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 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 an 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 over head 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

Found an interesting article by Jeff Atwood describing Deadlocks due to Dining Philosophers Puzzle and describing read committed snapshot isolation level.

EDIT:

Out of curiosity, I did some test measuring the impact on T-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 :

enter image description here

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: (Edited as highlighted by @TravisGan)

  • Insert took 19 secs.
  • Every Autocommit will Flush the T-log buffer to the disk due to autocomit (after @TravisGan highlighted, and I missed that to mention) .
  • The CHECKPOINT process will be completing fast as the amount of dirty log buffer required to be flushed will be less as it runs quiet often.

IMPLICIT & Explicit Transaction:

  • Insert took 2 secs.
  • For EXPLICIT transaction, the log buffers will be flushed only when they are full.
  • Contrary to Autocommit transaction, in EXPLICIT transaction, the CHECKPOINT process will take longer duration as it will have more log buffers to flush (remember that log buffers are 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 influence as well.

From the above tests, there is near to no difference between Implicit & Explicit transactions.

Thanks to @TravisGan for helping to add more to the answer.

share|improve this answer
    
So a transaction with may selects does not have any cost? Begin Tran Select *; Select *; End Tran; It will be the same without creating a transaction? –  elranu May 28 '13 at 16:28
2  
@elranu To answer your question, there is no REAL difference and NO cost. It totally depends on what ISOLATION level you are using. For e.g. If you are using explicit transactions for DMLs under a HIGHER ISOLATION level (other than default Isolation level) then there might be some very niche difference (but you wont even notice those). See this post with some numbers to understand sqlserver-tips.blogspot.com/2006/12/… –  Kin May 28 '13 at 16:43
    
@Kin The test above doesn't show the difference between implicit and explicit transaction. The first test is slower because t-log is written to the disk on each insert statement due to autocommit. The second test is faster because t-log is only written to the disk once during the one commit. –  Travis Gan May 28 '13 at 23:23
    
@TravisGan I am not outlining the difference. Just making point of using Implicit vs Explicit and their effect on T-log. I have updated my answer .. as somehow I missed that to mention it. Thanks for pointing it out. –  Kin May 28 '13 at 23:31
1  
@Kin The first test isn't an implicit transaction as implicit_transactions isn't enabled and there is no commit/rollback command used. The first test is an autocommit transaction. Please refer msdn.microsoft.com/en-us/library/ms175523(v=sql.105).aspx In fact, if coded correctly, implicit transaction should have about the same performance like the explicit transaction in your test. –  Travis Gan May 29 '13 at 5:26

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.