How does all code inside TransactionScope block not really executed until scope is committed or rollbacked?

Question

I've read about TransactionScope and this article, but I still don't understand 2 things:

1. When SqlCommand.ExecuteNonQuery is executed it doesn't really executed until scope.Complete() is invoked? If it's true, so where all the operations that were executed within the scope are remain and wait for scope.Complete() or scope.Rollback()?
2. When TransactionScope is instantiated how it prevents from SqlCommand.ExecuteNonQuery to be executed and wait for scope.Complete() or scope.Rollback()? Does it creates some "place" and SqlCommand somehow knows about it and puts the instructions in there?

Answer 1

TransactionScope hides a lot of stuff under the covers.

When you create a TransactionScope, everything you do inside it, is in the context of database transaction(s). So the SQL statements will be executed immediately, but their effects will be inside a transaction so that other processes will not be aware of them having occurred until the transaction commits.

If you are only working with a single database, then a transaction is opened against that and committed, or rolled back according to whether you .Complete() or not. Also if an exception occurs within the context of the TransactionScope then the Transaction is rolled back.

If you are working with multiple databases, a transaction is created in each one, and Microsoft Distributed Transaction Coordinator (MSDTC) manages the overall transaction. When you .Complete() MSDTC will then instruct each individual transaction to commit.

Note MSDTC is not limited to databases - see here for more info.

Answer 2

[1] When SqlCommand.ExecuteNonQuery is executed it doesn't really executed until scope.Complete() is invoked?

No this is not correct. Your command is executed on the line where you call ExecuteNonQuery. It is, however, interesting to know where all the changes are stored. The changes do not go directly to the affected table(s) on the server side, rather the changes are stored in a temporary place (again on a server side), which leads to an answer on your second question

[2] When TransactionScope is instantiated how it prevents from SqlCommand.ExecuteNonQuery to be executed and wait for scope.Complete() or scope.Rollback()?

It does not prevent as such, the action is executed, but because the result of the action is stored in a temporary location you must either merge these changes with the main table(s) - scope.Commit() or discard these changes - scope.Rollback() (or whatever is used to discard the changes in the specific database data provider)

Answer 3

This might help your questions you can enable tracing for the MSDTC to see transaction

Please see link for detailed information http://support.microsoft.com/kb/899115

Hope this helps..

Answer 4

A transaction scope doesn't prevent the code from executing, it prevents the transaction been committed. So in the case of a SqlCommand.ExecuteNonQuery, because it is inside a transaction, the SqlCommand looks at the transaction co-ordinator and sees that is was called inside a transaction, so when it connects through to SQL Server, the transaction is maintained, so SQL Writes the data to the database but it is not available to be read unless the transaction is committed (or someone does a "dirty read"). If complete is never called then when the TransactionScope is disposed it is rolled back, and SQL can undo the insert (or whatever else it did).

Anything that uses transactions (such as other database technologies) need to implement their .Net code to support transactions.

Transactions are essentially used in databases, but theoretically, other code could be made to support it.

But, to answer your question, the code is not stopped until the Complete() method is called, it is run, but within a transaction.