IsolationLevel

  • Thread starter Thread starter See Lim
  • Start date Start date
S

See Lim

Hi,

I'm using ReadUncommited level so that others can do a dirty read. But it
seems not to be working. What am I doing wrong here. Below is my test code:-
Thanks.

SqlConnection myConnection = new SqlConnection("Initial
Catalog=TEST;UID=sa;PWD=;Data Source=TEST;Connection Timeout=50;Connection
Lifetime=0;Max Pool Size=1000");

myConnection.Open();

SqlTransaction tr =
myConnection.BeginTransaction(IsolationLevel.ReadUncommitted, "test");

SqlCommand myCommand = new SqlCommand("insert into test (id,desp) values
(1000,'ddddd')", myConnection, tr);

myCommand.ExecuteNonQuery();

SqlConnection myConnection2 = new SqlConnection("Initial
Catalog=TEST;UID=sa;PWD=;Data Source=TEST;Connection Timeout=50;Connection
Lifetime=0;Max Pool Size=1000");

myConnection2.Open();

SqlCommand myCommand2 = new SqlCommand("select * from Seetest2",
myConnection2);

SqlDataAdapter D10 = new SqlDataAdapter(myCommand2);

DataTable DT10= new DataTable();

// I got a time out error when I try to do a Fill

D10.Fill(DT10);
 
See Lim,
Isolation levels are hard to understand. Setting your transaction iso level
to ReadUncommited allows you to read uncommited changes currently done by
other transactions, it does not allow other transactions to see your
uncommited changes.

Dirty reads are not the same as uncommitted changes btw, if your transaction
context allows dirty reads then you are not locking your predicate for
updates, a simpler way to say this would be that other people can update the
rows that you have in the where clause of your sql statement. Reading
uncommitted changes is dangerous, it allows you to see changes made by other
transactions before they commit them, if they roll them back they would have
never existed. Beware! here be dragons, bad things can happen.

Usually you would use a feature like ReadUncommited for monitoring/admin
purposes, it is not recommended for regular use. You can get similar
functionality with the NOLOCK tsql statement.

Hope this helped.
 
I thought by having the IsolationLevel of ReadUnCommitted on a transaction,
others can do a dirty read.

What I'm trying to achieve is to allow read by others during transaction.

Thanks.
 
I don't think you really want to do that. SQLS performs transactions
with sub-second response time, and in order to update data and
guarantee data consistency, needs to escalate locks so that each
transaction is either committed or rolled back in its entirety. So
even if you specify no locks or readuncommitted on an update
transaction, it won't have any affect on other transactions or other
connections simply doing a SELECT at that moment. You'd need to put
readuncommitted on all those SELECT statements, which you also don't
really want to do since it applies for the life of the connection. If
you really want to read dirty data, then a query hint would be a
better solution. See the topics "isolation levels" and "hints" in SQL
BooksOnline for more info.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
See Lim,

I have sent your sample question on to one of our Support Engineers. I
will reply back with it ASAP.

Thanks for your patience,

Brett Keown
Microsoft Support
(e-mail address removed)

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi See Lim,

I will not be able to provide a sample to you on how to do this. The other
SE's I have spoken with agree that its a very bad idea. Perhaps Angelsa
can provide you with a safe method. Just about everyone agrees on one
thing, its a bad idea.

Good luck to you with your project,

Brett Keown
Microsoft Support
(e-mail address removed)

This posting is provided "AS IS" with no warranties, and confers no rights.
 
There is no safe method, the right solution is to make sure that your
transactions are as atomic as possible. The second fill will not throw the
timeout exception until it has been unable to read the locked data for 30
seconds. If your transaction takes more than thirty seconds to complete you
have bigger problems than the timeout exception.

For an unsafe method take a look at the NOLOCK tsql statement.
 
Back
Top