TransactionScope promotion & isolation levels

[Guest]

Our project is using the System.Transactions.TransactionScope class to
provide an ambient transaction which our DAL classes can leverage.

I'm looking for some information about the TransactionScope class to answer
a few questions about how it behaves when a transaction is promoted to a
distributed transaction.

Our environment is ASP.NETv2 on Win2003 calling db server running SQL2005.

In pseudo-code, we do something like this:

Using (scope1 = New TransactionScope())
DalA.IssueInsertAndUpdateStatementsToDb1()
Using (scope2 = New TransactionScope(TransactionScopeOptions.Suppress))
DalB.IssueInsertToDb2()
scope2.Complete()
End Using
scope1.Complete()
End Using

The first TransactionScope is used to create a transaction for a series of
DAL inserts and updates to our primary database. Then we start a second,
nested scope with the transaction option set to "Suppress" and it is used to
insert a record into a second database (on the same db server).

Both databases are configured for a default isolation level of
ReadCommitted, but we are seeing some transactions come across at the
Serializable isolation level.

My question is: is the Serializable isolation level being caused by the use
of TransactionScope (and its implicit use of DTC)?

If we were to provide the appropriate TransactionOption class to the
constructor of each scope object, would it provide us with ReadCommitted
isolation level, or does the use of DTC always force the use of Serializable?

Thanks,
Chris

 

[Mary Chipman [MSFT]]

You *want* Serializable for any transaction. Otherwise you'll possibly
have data loss/inconsistency. SQL Server automatically promotes a
explicit transaction to serializable when it locks all of the
resources involved. If it didn't, IOW, used read committed, then you'd
have behavior where individual rows are modified one at a time.
Serializable guarantees that you can re-run the transaction and it
will have the same effect the second time around. Read committed would
commit one row at a time sequentially. Some rows might be blocked due
to other resources holding locks, others not, so you'd have
inconsistent updates as SQLS updated each row separately. This
violates the whole idea of a transaction, where you want multiple
operations to occur as a single unit of work or else all get rolled
back so that the data is left in a consistent state either way.

So to answer your question, it's not necessarily DTS. Each insert
operation is going to execute inside of its own explicit transaction
scope as serializable whether or not it's part of the ambient
transaction. I remember hearing that there's a way to drop the
isolation level of a System.Transactions transaction, but I'm not sure
why you'd want to do this because it has the potential to screw up
your data.

-mary