System.Transactions and database locking.

[Allan Ebdrup]

I've implemented transactions in my dotNet 2.0 project using
System.Transactions and TransactionScope.
---
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
options.Timeout = new TimeSpan(0, 5, 0);
using (TransactionScope transactionScope = new
TransactionScope(TransactionScopeOption.Required, options))
{
DoWork();
transactionScope.Complete(); //Tell the transaction to commit
}
---

When I debug this code and stop debugging inside the DoWork after it has
inserted some rows in some tables but before the transaction has completed,
selection database queries to the tables updated time out, (other users can
still run selects against the database). The selects run fine again when I
run the transaction to completion.

Is this because of my IsolationLevel.Serializable? And how do I avoid this
locking?

Is there some online documentation of isolation levels, that explains them
thoroughly?

Kind Regards,
Allan Ebdrup

 

[Marc Gravell]

Summary is here:
http://msdn2.microsoft.com/en-us/library/system.transactions.isolationlevel.aspx

do you mean you are blocking yourself? normally ADO.Net commands
should enlist inside the ambiant transaction (if one), so presumably
one of the following is the problem:
* The connections are not compatible for enlisting - is it the same
user account?
* Are you explicitely creating a null (chaos) transaction scope around
the select?
* Do your select commands / connections perhaps exist before the
transaction scope?

TransactionScope works best when the connection pool is utilised and
commands are created on-demand, rather than existing for an age
independently (in which case they may struggle to enlist).

Serializable isolation can cause blocking; this is expected (but it
reduces conflicting data movement during transactions, improving
ACIDity); in my experience, "range" locks are the most unanticipated
ones. Serializable isolation can also (when used incorrectly) cause
deadlocks if 2 serializable connections read data then both attempt to
escalate to a write lock; this can be reduces by using (UPDLOCK) when
you read the data to take out an exclusive lock at the start, rather
than escalate to exclusive from read.

Marc

 

[Marc Gravell]

(chaos)
I possibly meant ReadUncommitted...

 

[Ollie Riches]

This is because the state of the MSDTC on the local machine is inconsistent
with you stopping debugging half way through a transaction.

If you stop and restart the MSDTC on the local machine everything will be
fine.

I wonder if this a bug in VS 2005 and\or System.Transaction namespace

HTH

Ollie Riches

 

[Allan Ebdrup]

Summary is here:
http://msdn2.microsoft.com/en-us/library/system.transactions.isolationlevel.aspx

do you mean you are blocking yourself?

I'm running the transaction in debug mode in VS2005 and pausing inside the
transaction, when I connect to the database using the Query Analyzer,
selects form the tables touched by the transaction simply time out and never
complete. Users on other machinces can still connect to the database and run
the queries that time out for me. When I run the transaction to completion
everything is back to normal.

I can't see how a transaction could ever cause selects of tables involved in
the transaction to time out, the transaction should be atomic so I don't see
it's updates until it completes, but I can't see how it should cause selects
not to run while the transaction is in progress.

Kind Regards,
Allan Ebdrup

 

[Allan Ebdrup]

This is because the state of the MSDTC on the local machine is
inconsistent with you stopping debugging half way through a transaction.

If you stop and restart the MSDTC on the local machine everything will be
fine.

I wonder if this a bug in VS 2005 and\or System.Transaction namespace

So it's only a problem when debugging? My concern is that selects are
blocked while the transaction is in progress, will this not happen in
production when I'm not running in debug mode?

Kind Regards,
Allan Ebdrup.

 

[Ollie Riches]

Yes it will only happen when debugging from VS, it won't happen in
production

HTH

Ollie Riches

 

[Ollie Riches]

when doing a selct using the 'with' keyword as in:

Select * from Order with(nolock)

Sql Server can choose to ignore the 'with' keyword.

HTH

Ollie Riches

 

[Marc Gravell]

"Atomic" is only one part of ACID; "Isolated" is also a key one. That
means that another client should not be adversely affected by you...

Scenario: your transaction is open, and you have manipulated data;
somebody else wants to read that data (or a "range" that you impacted,
etc)... three options:
1: wait for your transaction to end, and then read the data in its
final form
2: return your original changes
3: return your as yet uncommitted changes

"NOLOCK" and low isolation levels work using option 3; this has the
unfortunate side-effect that if you rollback your change, they have a
copy of data *that never existed*. Whoops! Serializable works using 1,
which protects you from dirty / non-repeatable reads, but does it by
queuing competing access. Different callers with different isolations
will see different versions of the truth.

There are many scenarios eto cover the permutations of read/write
versus read/write.

Marc