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
On Tue, 24 Jul 2007 18:28:03 -0700, clrudolphi
[quoted text, click to view] <clrudolphi@discussions.microsoft.com> wrote:
>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,
