CODEWITHSUNDEEP
c#sql-server-2008deadlock
dsum
dsum

Reputation: 1493

Why sqlCommand.EndExecuteNonQuery() is blocked?

I have an C# application that connect to SQL Server 2008R2 with the connection string Asynchronous Processing=true; and MultipleActiveResultSets=True;

The application has multiple threads and each open its own connection to access the database.

Most of the time the application works, but occasionally some of these thread were blocked at the following code: 

IAsyncResult asyncResult = sqlCommand.BeginExecuteNonQuery();
int rowsAffected = sqlCommand.EndExecuteNonQuery(asyncResult);

sqlCommand.EndExecuteNonQuery() never returns, or times out.

I check the stacktrace:

Child SP         IP               Call Site
000000000242d608 00000000778618ca [HelperMethodFrame_1OBJ: 000000000242d608] System.Threading.WaitHandle.WaitOneNative(System.Runtime.InteropServices.SafeHandle, UInt32, Boolean, Boolean)
000000000242d740 000007fef795bf64 System.Threading.WaitHandle.InternalWaitOne(System.Runtime.InteropServices.SafeHandle, Int64, Boolean, Boolean)
000000000242d780 000007fee438bed6 System.Data.SqlClient.SqlCommand.WaitForAsyncResults(System.IAsyncResult)
000000000242d7c0 000007fee438baf1 System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(System.IAsyncResult)

I have set the sqlCommand.CommandTimeout to 30 sec, but I can't figure out why this problem happen occasionally.

I checked if SQL Server has blocked sessions, but there isn't. Is it possible because there are multiple connections own by the same application, and somehow they blocked each other?

Upvotes: 1

Views: 760

Answers (1)

goalie7960
goalie7960

Reputation: 873

It does appear this code is pointless, but you should check the IsCompleted flag on the asyncResult object. Calling EndExecuteNonQuery if IsCompleted is false will end up blocking.

Upvotes: 1

Related Questions