3

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.

Community
  • 1
  • 1
SRKX
  • 1,806
  • 1
  • 21
  • 42
  • The "atomic" unit in an ACID relation database is a transaction. Choose the correct [isolation level](http://en.wikipedia.org/wiki/Isolation_%28database_systems%29) for the task and commit/rollback appropriately -- this won't help with critical regions outside the data, though (it is strictly related to the RDBMS transactions!). Using a `TransactionScope` is one way to help with such transactions through an implicit thread-local transaction (saves having to pass it about explicitly). –  Jul 22 '11 at 07:14
  • 1
    http://stackoverflow.com/questions/2884863/under-what-circumstances-is-an-sqlconnection-automatically-enlisted-in-an-ambient/2886326 – Mitch Wheat Jul 22 '11 at 07:38
  • @Mitch: thanks. Do you think the same is true for anything done with the ADO Entities Framework. – SRKX Jul 22 '11 at 08:14
  • @SRKX : without checking, I'm not 100% sure. The Entity Framework may alter the default isolation level depending on what's being performed. – Mitch Wheat Jul 22 '11 at 08:16
  • I just updated my answer with Entity Framework details – Richard Blewett Jul 22 '11 at 08:24

3 Answers3

2

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

Richard Blewett
  • 6,089
  • 1
  • 18
  • 23
  • Ok so in your opinion what should be in the `TransactionScope`? do you think the instance of the model should be created within the scope or only the `SaveChanges()` method is important? – SRKX Jul 22 '11 at 08:38
  • Create the Model (I assume you mean the ObjectContext wrapper) inside the TransactionScope – Richard Blewett Jul 22 '11 at 08:45
1

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    TransactionScope uses Serializable by default – Richard Blewett Jul 22 '11 at 07:17
  • 1
    You're right that SQL Server uses READ COMMITTED by default for its transactions - not sure why they chose something different for TxScope - maybe for least surprise for people who weren't used to transactions – Richard Blewett Jul 22 '11 at 07:29
  • I learnt something today! TransactionScope does not work as you would expect! – Mitch Wheat Jul 22 '11 at 07:41
  • So basically, by default, everything that happened from the creation of the `TransactionScope` until the `Commit()` call will be treated a a single operation by the DB? including (possibly) table reading, data-processing and so on? – SRKX Jul 22 '11 at 08:00
  • @SRKK: yes, as long as the connection is enlisted in the transaction. – Mitch Wheat Jul 22 '11 at 08:14
1

A Transaction is always atomic, whether started in SQL, on in the client (eg TransactionScope)

gbn
  • 422,506
  • 82
  • 585
  • 676