Does TransactionScope support atomic DB execution?

Question

I am at the moment developping a project where the DAL is implemented in .Net using the ADO Entity Framework.

The DB structure is not straightforward and I would like to be able to ensure that some DB operations are atomic.

I know that you can do that by creating a STORED PROCEDURE and using a DB TRANSACTION (as mentioned in this SO post). (I'm using SQL Server)

The thing is, I would like to keep the logic on the software side as much as possible, and I was considering using .Net TransactionScope. Although I understand that it works well from a software point of view (nothing is committed to the DB until the whole scope is committed), I doubt it still ensures DB atomic execution.

Does somebody know about this?

More specifically, usually the code would look like this:

using (TransactionScope scope = new TransactionScope())
{
  /*
   * Do some opreations such as reads, write, insert, deletes
   */
   scope.Commit()
}

What I'd like to make sure, is that everything within the brackets is done "atomically" (isolation=SERIALIZABLE I guess). What I mean by that is that I don't want the state of the DB to be able to change when the code within the scope is executing.

Answer 1

TransactionScope runs the updates in a transaction (and possibly the reads as well depending on your transaction isolation level)

The database providers are already part of this transaction as they are used within the transaction scope - therefore the transaction scope is, effectively, a database transaction (and possibly more if you have multiple databases / message queues / etc involved in the transaction)

EDIT: Entity Framework Details

Entity framework uses the underlying provider's connection BeginTransaction method. In the case of SqlConnection it uses the default for SqlServer and so will use ReadCommitted

So if you don't use a transaction scope it will default to Read Committed rather than Serializable

Answer 2

What do you mean by "DB atomic execution"? There are various transaction isolation levels.

When talking to SQL Server a .NET TransactionScope enlists a SQL Server transaction, which by default will be a Serializable isolation level.