CODEWITHSUNDEEP
c#.netsql-server
alexg
alexg

Reputation: 683

SqlCommand.ExecuteReader() does not throw SqlException when deadlocked

We're using C# SqlCommand.ExecuteReader() to issue SQL Server stored procedure and SQL requests inside a transaction.

When the connection is chosen as a deadlock victim, ExecuteReader() does NOT throw SqlException with 1205 deadlock code for some commands but DOES for others.

According to MSDN

If a transaction is deadlocked, an exception may not be thrown until Read is called.

Considering that we use SqlCommand object encapsulated inside our own database request framework, is there a way to always guarantee that the exception is thrown when a deadlock occurs?

We're using .Net 4.5, SQL Server 2008 R2, Visual Studio 2012

Here is a simplified version of our database access framework code:

SqlDataReader DoWork( string sql ) {
    ...
    cmd = new SqlCommand( sql );
    SqlDataReader rdr = null;

    try {
        rdr = cmd.ExecuteReader( CommandBehavior.Default ); 
    } catch (SqlException sqle) {
        // Log the error, throw a custom exception, etc.
        // if (sqle.ErrorCode == 1205) ...
        ...
        if (rdr != null) {
            rdr.Close();
            rdr = null;
        }
    }
    // All is well, so just return to caller to consume the result set
    return rdr;
}

...

main() {
    ...
    SqlDataReader result = DoWork( "select ...";

    if (result.HasRows) { // Check there is data to read...
        while (result.Read()) {
            ...
        }
    result.Close();
    ...
}

Upvotes: 3

Views: 1539

Answers (2)

usr
usr

Reputation: 171226

I don't know why you are doing this:

if (result.HasRows)

This is not necessary and it prevents the deadlock from appearing:

If a transaction is deadlocked, an exception may not be thrown until Read is called.

Delete that if. It's a common anti pattern. It's often introduced by people who copy sample code without really understanding what it does.

This in your catch is also an anti pattern:

    if (rdr != null) {
        rdr.Close();
        rdr = null;
    }

Just use using.

Upvotes: 4

Simon Price
Simon Price

Reputation: 3261

this is the code from that link, Stack wouldnt allow it as an answer 

function DoWork() {
using (TransactionScope scope = new TransactionScope(...)) {
cmd = new SqlCommand("select ...");
using (DataReader rdr = cmd.ExecuteReader ()) {
    while(rdr.Read()) {
      ... process each record
    }
 }
  scope.Complete ();
 }
}

Upvotes: 0

Related Questions