> I have a method that explicitly opens a connection and begins a
> transaction on SQL Server 2000. I use the System.Data.SqlClient.
>
> Before commiting (or rollbacking) this transaction I need to open
> another connection to use with a datareader. What is important to note
> is that I'm not interested in uncommited data. I would like to perform
> some queries on data that existed before the BeginTransaction
> instruction.
This is hardly doable because the update/insert transaction will
lock the tablerows affected for readers, if you do not specify table
lock hints in your update/insert query in the transaction.
If you are interested in data which existed BEFORE the
transaction, why not executing that code before the transaction starts?
You can even use the same connection for that, which saves performance.
> I have a problem because during the execution of the transaction I can
> not execute a select statement on the tables involved in the
> transaction. The "Open connection" works but the ExecuteReader method
> times out.
correct. It hangs on a lock. Check 'current activity' in
enterprise manager for locks.
> I tested the execution of my method in debug mode. If I stop on a
> breakpoint, I cannot even use the query analyzer at the same time to
> execute a select statement. It does wait until the
> transaction is over.
caused by the same problem: the rows which you want to access
are locked for readers. You can only READ these rows if you specify a
hint that ignores the rowlock, i.e. 'NOLOCK' or 'READUNCOMMITTED'. You
specify these hints with your SELECT statement for the datareader, using
a 'WITH' statement after the table name (or alias) in the FROM clause.
See FROM in books online for details and descriptions on other hints.
> It looks like that during my transaction all the queries are
> serialized and have to wait for my transaction to commit or rollback.
So I had a
> look at the isolation level enum. Apparently, the possible isolation
> levels in ado .net for Sql Server are ReadCommited, ReadUncommited,
> RepeatableRead and Serializable. I tried them all but it does
> not change anything.
True, SqlServer does not allow dirty read lock hints on
update/delete or insert statements. This means that these statements
always lock a row for readers which are not specifying a lock hint for
dirty data. From a logical POV this is correct, since you do not want to
read dirty data that can eventually be rolled back, so it is more wise
to wait for a locked row than to read the dirty update.
> Considering that this mechanism works very well with Oracle
> (I tested it via System.Data.OracleClient), I have 2 questions:
Oracle uses multi-version concurrency control, which means that
when you change a row, a new version is created separated from the
current version, which is still accessable for everyone. When the
transaction is committed, the new version becomes the current version.
This means that readers are not blocked by writers. However you can
achieve almost the same amount of concurrency control in SqlServer most
of the time by specifying lock hints or rescheduling your code.
> - Am I using the isolation level in a bad way? The behavior of my
> application is the same regardless of the Isolation level I use in the
> BeginTransaction method. Is it because it is not the right place to
> configure the isolation level?
no, you are doing it right, READUNCOMMITTED is not influencing
write locks, see FROM clause in books online.
> - Is it a limitation of SQL Server compared to Oracle?
In a way it is, if you want to use SqlServer as if it is Oracle.
However from a logical point of view it isn't really, they just defined
'current version' a little different, however in a way the Oracle (and
postgresql btw) way of working with concurrency is more flexibel and
transparent.
FB
======= You can't sell what's free ==================================
Senior Software Engineer @ Solutions Design : http://www.sd.nl
Get my free, open source .NET software at : http://www.sd.nl/software
=======================================================================
