Difference between Implicit and Explicit Transaction

Arian picture Arian · Feb 13, 2011 · Viewed 47.8k times · Source

What is the difference between Implicit and Explicit transaction in Sql Server 2008?

What happens in TransactionScope background? I'm using TransactionScope but in Sql server profiler I don't see "Begin transaction..." statement.

How does it work?

Answer

RichardTheKiwi picture RichardTheKiwi · Feb 14, 2011

Basically, in c# when you set the TransactionScope to Implicit, it calls the SQL Server SET command to put the connection in IMPLICIT_TRANSACTIONS mode. Anything that you do (using one of the commands listed in the 2nd link) starts a transaction that is kept open until a commit is issued. If no commit is issued at the end of a connection, an implicit ROLLBACK is performed.

This differs from the OFF setting, which also puts every statement into a transaction - the difference is that in the OFF mode (therefore transactions are explicit), each transaction (singular statement) is immediately committed.