System.Transactions ReadUncommitted

Dec 18, 2006

I got a question today about setting the isolationlevel on a transaction to ReadUncommitted. The question was why the transaction still locked the row it was updating, when isolationlevel is set to ReadUncommitted. It was expected that using ReadUncommitted on the transaction allowed other requests (on a different connection) to read the data, even when it wasn’t committed. When we take a look at the SQL Server 2005 Books Online you can read the following:

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

When you read this real fast, you might think that statements can read rows that have been modified by transactions, but not yet committed. But it doesn’t say that. It says, “rows that have been modified by other transactions…” So what’s the difference? Let’s look at it through code.

I’ve setup a simple table with two columns. The table is called [MyTable] with a primary key called [MyTableId] of type integer. Also a column called [value] of type varchar(50). I’ve added five rows which you can see on the left.

I’ve got some examples in which I’ll create a transaction, update the 5th row with a new value. In every example I’ve added wait statements so that another connection can try to read the changed value of this 5th row. It’ll either fail because of a lock, or not if done correctly.

The following T-SQL code will setup the transaction’s isolation level to Read Uncommitted (line 1), start a new transaction (line 4) and try to update the table (lines 6-8). It than waits for 15 seconds (line 10) and finally rollback the transaction (line 12). Within these 15 seconds you’ll have time to execute another query in another query-window. For example “select * from MyTable where MyTableId = 5“. You’ll notice it’ll lock-up for 15 seconds.

1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 2 GO 3 4 BEGIN TRANSACTION 5 6 UPDATE MyTable 7 SET Value = ‘changed’ 8 WHERE MyTableId = 5 9 10 WAITFOR DELAY ’00:00:15′ 11 12 ROLLBACK TRANSACTION

Why is this happening when IsolationLevel is set to Read Uncommitted? The answer lies in the single word “other”, as stated above. When setting IsolationLevel to Read Uncommitted, you can read rows from another transaction. But you’ll have to set the isolation level on this connection. On the connection you’re reading with! For this you don’t need a new transaction, but you do need to copy line 1 from the T-SQL code above.

Or you can use a table hint. “Hint’ is actually a misnomer, because it’s not a hint. It overrules the currently set isolation level. In our case, we could use the table hint as follows.

SELECT Value FROM MyTable WITH (READUNCOMMITTED) WHERE MyTableId = 5

We can achieve the same result(s) in .NET. I’m written my examples using .NET 2.0 but I’m (pretty) sure the first example can be used in .NET 1.x as well.

1 const string connectionString = “server=dennisvista;database=laboratory;integrated security=sspi;”; 2 3 const string query1 = “update mytable set value = ‘changed’ where myTableId = 5”; 4 const string query2 = “select value from myTable where myTableId = 5”; 5 const string query3 = “select value from myTable with (readuncommitted) where myTableId = 5”; 6 const string setIsolationLevel = “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”; 7 8 SqlConnection con1 = new SqlConnection(); 9 SqlConnection con2 = new SqlConnection(); 10 11 con1.ConnectionString = connectionString; 12 con2.ConnectionString = connectionString; 13 14 con1.Open(); 15 con2.Open(); 16 17 SqlTransaction trans = con1.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); 18 19 SqlCommand cmd1 = new SqlCommand(); 20 cmd1.Connection = con1; 21 cmd1.Transaction = trans; 22 cmd1.CommandText = query1; 23 24 SqlCommand cmd2 = new SqlCommand(); 25 cmd2.Connection = con2; 26 cmd2.CommandText = query2; 27 28 cmd1.ExecuteNonQuery(); 29 30 // Uncomment the following line to set the ReadUncommited isolation level 31 // new SqlCommand(setIsolationLevel, con2).ExecuteNonQuery(); 32 33 // Timeout will occur because of the lock 34 string value = cmd2.ExecuteScalar().ToString(); 35 36 trans.Rollback(); 37 38 con1.Close(); 39 con2.Close();

We’ve got two connections set up. On line 17, we set the IsolationLevel to ReadUncommitted. Again this doesn’t make sense for the result we want to achieve at line 34.We execute query1 on line 28, which will create a transactional lock on line 34.

At line 31 however you can see in a single line that we set the IsolationLevel to Read Uncommitted, on the second connection! So if you uncomment line 31, you’ll actually read the data that line 28 is trying to update. Of course it’s not yet committed, but that’s what Read Uncommitted does. In this example we’re not using query3, but it would produce the same result as setting the isolation level. The difference is with query3 we’re not setting Read Uncommitted on the entire connection. Instead we’re only using it with a single query. Using TransactionScope So we finally get to using System.Transactions. In the example below we’re using the exact same queries as above. You can see we wrap the TransactionScope around both connections. But we first set up the IsolationLevel in line 2, then pass this to the TransactionScope constructor on line 4.Now both connections automatically enlist in our transaction and also both have the IsolationLevel set to Read Uncommitted.

1 TransactionOptions transOptions = new TransactionOptions(); 2 transOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted; 3 4 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, transOptions)) 5 { 6 using (SqlConnection con1 = new SqlConnection(connectionString)) 7 { 8 con1.Open(); 9 SqlCommand cmd = new SqlCommand(query1, con1); 10 cmd.ExecuteNonQuery(); 11 } 12 13 using (SqlConnection con2 = new SqlConnection(connectionString)) 14 { 15 con2.Open(); 16 //con2.EnlistTransaction(null); 17 SqlCommand cmd = new SqlCommand(query2, con2); 18 string value = cmd.ExecuteScalar().ToString(); 19 } 20 }

The problem here is however that we’ll automatically get bumped up to a distributed transaction. So make sure the MSDTC service is running. Conclusion Now you know how you can read uncommitted data that’s being updated in other transactions. Be sure to know however that this is called a “dirty read”. You’re reading data that in 99% of the time is data you don’t want. It’s not being updated for nothing! The person who asked me the question actually had a valid reason to choose this, although the initial design of the application might not be the best. But it wasn’t his, so he wasn’t to blame.

Sahil Malik once made a weblog post about why the heck (old weblog) we would want to use ReadUncommitted. He couldn’t think of a single reason to use it. I now know one reason, but I still agree with him. I’m sorry if I wasted your time with this long weblog post, with a final conclusion that you shouldn’t use ReadUncommitted. 😉

Table of contents

Share this article

System.Transactions ReadUncommitted

Mozy

Vista desktop search amp; starting applications as administrator

Menu