CODEWITHSUNDEEP
sql-server-2005sql-server-2008c#-4.0transactionstransactionscope
DooDoo
DooDoo

Reputation: 13447

Difference between Implicit and Explicit Transaction

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?

Upvotes: 21

Views: 55959

Answers (4)

Mike Dimmick
Mike Dimmick

Reputation: 9802

SqlTransaction - which TransactionScope uses under the covers - only sends T-SQL BEGIN TRANSACTION commands for SQL Server 2000 and earlier.

For SQL Server 2005 and later, the TDS protocol was extended to allow clients to directly manipulate transactions without sending BEGIN TRANSACTION etc. To see these in the Profiler, select the 'TM: Begin Tran starting' events, etc. You will probably need to check the 'Show all events' checkbox to see these events - they are under the Transactions category.

See Transaction Manager Request in the TDS protocol documentation, TM: Begin Tran Starting Event Class in the Profiler documentation, and SqlInternalTransaction.ExecuteTransactionYukon in the .NET Reference Source.

Upvotes: 0

Jom George
Jom George

Reputation: 1127

Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.

Explicit Transaction has the beginning, ending and rollback of transactions with the command Begin Transaction, Commit Transaction and Rollback Transaction.

In the explicit transaction, if an error occurs in between we can rollback to the beginning of the transaction which cannot be done in implicit transaction.

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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.

Upvotes: 15

GolezTrol
GolezTrol

Reputation: 116110

In Explicit transaction mode, you will need to start a transaction explicitly. In Implicit transaction mode, a transaction is automatically started after each commit. So you will only have to commit.

Since the transaction is started 'implicitly', you will not see an explicit 'BEGIN' in the logs. :)

By default the database operates in explicit transaction mode with autocommiting transactions enabled. That actually meand that unless an explicit transaction is started using BEGIN TRANSACTION, every data modification is started in a separate transaction which is committed after the statement. That allows the database to rollback an entire statement when it fails (for instance a bulk insert, or an insert that modifies other data in a trigger).

Upvotes: 5

Related Questions