Hi, 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. 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. 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. 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. Considering that this mechanism works very well with Oracle (I tested it via System.Data.OracleClient), I have 2 questions: - 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? - Is it a limitation of SQL Server compared to Oracle? I would be very grateful if someone could have some sort of explanation. Jerome Grelier =================================== This list is hosted by DevelopMentor� http://www.develop.com You may be interested in Guerrilla .NET, 24 March 2003, in London and Boston http://www.develop.com/courses/gdotnet View archives and manage your subscription(s) at http://discuss.develop.com
