I am getting the strangest deadlock error, sporadically, on a MultiQuery. I have a table of calls (this is for a hotline), which has an admin page that lists all the calls so they can be monitored, edited, etc. The calls page utilizes a .NET Gridview with paging. I am using the multiquery to get the total record count, so I can feed that to the .NET Gridview paging mechanism so and so I can limit my first query in the multiquery to just the records needed for the page the user is on, rather than getting thousands of records in the query and only displaying the first ten or whatever. I thought deadlocks only occured when two or more updates to a record were being attempted at the same time. In this program only one person at a time can update a call (the program interface enforces that), and the MultiQuery sql statements are both selects, so I can't see how these deadlock errors are occuring. Here is my query, and the error:




NHibernate.HibernateException: Failed to execute multi query ---> System.Data.SqlClient.SqlException: Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at NHibernate.Driver.NHybridDataReader.Read()
at NHibernate.Loader.Loader.Advance(IDataReader rs, RowSelection selection)
at NHibernate.Impl.MultiQueryImpl.DoList()
--- End of inner exception stack trace ---
at NHibernate.Impl.MultiQueryImpl.DoList()
at NHibernate.Impl.MultiQueryImpl.ListIgnoreQueryCache()
at NHibernate.Impl.MultiQueryImpl.List()
at CrisisCenter.Data.Dao.CrisisCenterHibernateImpl.GetAllCalls(String SortExpression, Int32 maximumRows, Int32 startRowIndex) in
....snip


Has anyone else every experienced something like this? I am using SQL Server 2005. Very simple database. Just the Calls table and a few lookup tables attached, plus a table called CallResponses which has a one-to-one relationship with the HotlineCalls table.

Are you using SQLServer with plain read-commited isolation level?

If yes, then probably you can solve your problem by using READ COMMITTED SNAPSHOT isolation level.

I'm afraid I'm a bit confused. In the MSDN chapter I read on isolation levels, the way you set them is in a transaction in a T-SQL statement, which I'm not using anywhere, just an HQL query. I'm not sure how changing the isolation level would be done in my case.

You can set the isolation level in your hibernate configuration file



It is very important to know which isolation level to use when accessing a relational database in multiuser mode.
I recommend you to read some documentation about as it is a 'absolutely not to ignore topic'.

Thanks for the information. I'll see if this helps.