How to deal with deadlocks in an instance of a SQL Server database accessed by NHibernate?

Question

I am working on a database that suffers from dead ends. We are developing a database using NHibernate. What are some common deadlock approaches that we observe around certain tables?

Answer 1

The best solution, use stored procedures to control access to data so that you can directly write TSQL code. nHibernate can make stored procedure calls just fine.

However, since this solution almost never flies, you can try to treat the symptoms. First, make sure you have good indexes on the tables, so that queries that run from nHibernate run as well as they can. Secondly, if you are using SQL SErver 2008+, use the read snapshot lock. This will go a long way in locking and locking, which you see, both of which lead to deadlocks.

On a side note, set the server optimization for the special workload. This will radically help memory management and procedure processing.