Microsoft KB Archive/316667

= BUG: SqlDataReader does not propagate deadlock exceptions in SqlClient =

Article ID: 316667

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1

-



This article was previously published under Q316667



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Threading
 * System.Data
 * System.Data.SqlClient
 * System.Diagnostics



SYMPTOMS
When you execute the SqlTransaction.ExecuteReader function within a repeatable read transaction, the transaction is blocked and then aborted due to a deadlock. When a repeatable read transaction is blocked by a deadlock, SqlTransaction.ExecuteReader may throw incorrect exceptions and, in some situations, may return with an empty reader.

Here is an example of an unexpected error message that you may receive:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

The expected error message, both for incorrect exceptions and for an empty reader, is as follows:

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.



RESOLUTION
When you use SqlTransaction.SqlExecuteReader, always follow it with a call to SqlDataReader.Read followed by SqlDataReader.NextResult to see the error. It is especially important to capture the error situation that produces an empty reader. To advance to the next result, call the DataReader.NextResult method as demonstrated below.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
The example below produces a deadlock in Microsoft SQL Server. Because timing is critical, two different threads are used. Each thread is used for a transaction; this is the only way that we can consistently reproduce the deadlock condition for demonstration purposes.

In this example, an empty reader returns without any error. To see the correct error, just uncomment the line of code as shown below.  Open a new Microsoft Visual C# .NET console project. Delete all of the code inside the code window.  Paste the following code inside the module: using System; using System.Threading; using System.Data; using System.Data.SqlClient; using System.Diagnostics; class MainThread {   [STAThread] public static void Main(string[] args) {       Debug.WriteLine( &quot;Creating table.&quot; ); String connectionString = &quot;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=master&quot;; SqlConnection c = new SqlConnection(connectionString); c.Open; CreateTable( c ); c.Close;

// Start clean. SqlConnection c1 = new SqlConnection(connectionString); SqlConnection c2 = new SqlConnection(connectionString);

c1.Open; c2.Open;

SqlTransaction t1 = c1.BeginTransaction( System.Data.IsolationLevel.RepeatableRead ); SqlTransaction t2 = c2.BeginTransaction( System.Data.IsolationLevel.RepeatableRead ); Debug.WriteLine( &quot;Starting test.&quot; );

ReadStuff( &quot;select * from dmvasitest where a = 1&quot;, c1, t1 ); WriteStuff( &quot;update dmvasitest set b = 2 where a = 2&quot;, c2, t2 ); SecondThread.Run( &quot;select * from dmvasitest where a = 2&quot;, c1, t1 ); Debug.WriteLine( &quot;-- PLEASE NOTE THAT PREV STATEMENT IS BLOCKED NOW. NEXT STATEMENT WILL CAUSE DEADLOCK&quot; ); WriteStuff( &quot;update dmvasitest set b = 1 where a = 1&quot;, c2, t2 );

c1.Close; c2.Close; Debug.WriteLine( &quot;Test done.&quot; ); }

public static void CreateTable( SqlConnection c ) {       // Create new table. String text1 = @&quot; if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dmvasitest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[dmvasitest] &quot;; String text2 = @&quot; create table dmvasitest (  a int,   b int ) &quot;; String text3 = @&quot; insert into dmvasitest (a,b) values (1,1) insert into dmvasitest (a,b) values (2,2) &quot;;

SqlCommand cmd1 = new SqlCommand( text1, c ); cmd1.ExecuteNonQuery; SqlCommand cmd2 = new SqlCommand( text2, c ); cmd2.ExecuteNonQuery; SqlCommand cmd3 = new SqlCommand( text3, c ); cmd3.ExecuteNonQuery; }

public static void ReadStuff( String text, SqlConnection c, SqlTransaction t ) {       Debug.WriteLine( &quot;Reading: '{0}'&quot;, text ); SqlCommand command = new SqlCommand( text, c, t ); SqlDataReader reader = command.ExecuteReader; try {           while (reader.Read) {               int a = reader.GetInt32(0); int b = reader.GetInt32(1); }           Debug.WriteLine( &quot;All data has been read. -- FIRST TIME OK, BUT YOU SHOULD NOT SEE THIS FOR THE SECOND TIME!!!&quot; ); // Uncomment following line to see the correct error. // reader.NextResult; }       catch(Exception e)        { Debug.WriteLine( &quot;Exception thrown: {0}&quot;, e.ToString ); }       finally {           reader.Close; }   }

public static void WriteStuff( String text, SqlConnection c, SqlTransaction t ) {       Debug.WriteLine( &quot;Writing: '{0}'&quot;, text ); SqlCommand command = new SqlCommand( text, c, t ); try {           command.ExecuteNonQuery; Debug.WriteLine( &quot;Data written.&quot; ); }       catch(Exception e)        { Debug.WriteLine( &quot;Exception thrown: {0}&quot;, e.ToString ); }   } }

class SecondThread {   String m_text; SqlConnection m_connection; SqlTransaction m_transaction; public SecondThread( String text, SqlConnection connection, SqlTransaction transaction ) {       m_text = text; m_connection = connection; m_transaction = transaction; }   private void Run {       MainThread.ReadStuff( m_text, m_connection, m_transaction ); }

public static void Run( String text, SqlConnection connection, SqlTransaction transaction ) {       SecondThread secondThread = new SecondThread( text, connection, transaction ); Thread thread = new Thread( new ThreadStart( secondThread.Run ) ); thread.Start; Thread.Sleep(300); }

}                    Press F5 to run the application. On the View menu, click Other Windows, then click Output to view the result.

You receive a random error similar to the error described in the &quot;Symptoms&quot; section of this article.</li>  Edit the code and uncomment the following line: // reader.NextResult; </li> Press F5 to run the application.</li> On the View menu, click Other Windows, then click Output to view the result.

This time you receive the expected results indicating the actual problem.</li></ol>

<div class="references_section">