Difference between Implicit and Explicit Transaction

Question

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?

Solution 1

• Implicit Transactions: http://msdn.microsoft.com/en-us/library/ms188317.aspx
• SET IMPLICIT_TRANSACTIONS { ON | OFF} http://msdn.microsoft.com/en-us/library/ms187807.aspx

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.

Solution 2

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).

Solution 3

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.

Comments

[Arian]

Thanks.I know that when we execute a single INSERT statment it execute in Implicit transaction.You say that Implicit transaction will start after each commit.consider we have 2 satement in TransactionScope that second statment will throw exception.when first statment 1 complete it committed and new transaction will create.you say this?but why when second transaction throw exception entire transaction will rollback in TransactionScope?

[zzlalani]

@Kerezo no, once a transaction is committed all the statements will be locked into the database since the last commit. so if any statement throws an exception the only loss will be number of statements executed after that last commit

[Mike Dimmick]

Implicit and auto-commit are different things. When you SET IMPLICIT_TRANSACTIONS ON, the next DML command you send starts a new transaction. Those changes are pending until you either COMMIT or ROLLBACK. If you disconnect without sending COMMIT, it automatically rolls back. Auto-commit occurs if you send a DML command with IMPLICIT_TRANSACTIONS OFF and no pending explicit transaction - here the data is automatically committed at the end of executing the DML command, before the next command is processed. I've never used implicit transactions.

[jlb]

It's correct to say that transactions are explicit in implicit_transactions off mode iff you (explicitly) use BEGIN TRANSACTION ..., otherwise this is referred to as autocommit mode.