NHibernate IStatelessSession CreateQuery failure

Question

When we released last friday, I received an error which I do not get on acceptance. The error message is:

could not execute update query[SQL: delete from dbo.MyTable where col1=? and col2=? and col3=? and col4=? and col5=?]

My C# code is as follows:

var hqlDelete = "DELETE MyTable m WHERE m.Col1 = :var_1 AND m.Col2 = :var_2 AND m.Col3= :var_3 AND m.Col4 = :var_4 AND m.Col5= :var_5";
var deletedEntities = session.CreateQuery(hqlDelete)
                             .SetString("var_1", variable1)
                             .SetString("var_2", variable2)
                             .SetString("var_3", variable3)
                             .SetString("var_4", variable4)
                             .SetString("var_5", variable5)
                             .ExecuteUpdate();
transaction.Commit();
session.Close();

Now, as I said, the error did not trigger when testing on acceptance. Also, when I test with the production database (code from my developer seat), it works without problems too.

The code is triggered when I call a web service and POST a "measurement" to it. The only difference is that I call the service when testing, and on production an other company sends measurements to the web service.

I think it might have something to do with the amount of sessions/transactions, but that would not really explain why the variables show up as ? in the error message.

Any ideas? Is there more information I could supply so you can help me with this one?

Edit: InnerExeption is

{"Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}

Answer 1

Solving deadlocks can be a hard matter, especially when using an ORM. Deadlocks usually occurs because locks on database objects are not acquired in the same order by different processes (or threads), causing them to wait for each other.

An ORM does not give you much control on lock acquiring order. You may rework your queries ordering, but this could be tedious. Especially when caching causes some of them to not hit DB. Moreover, it should be done with the same ordering on any other application using the same database.

You may detect deadlock errors and do what the message say: retry the whole process. With NHibernate, this means discarding the current session and retry your whole unit of work.

If your database is SQL Server, there is a default setting which greatly increase deadlocks risk: the disabling of read committed snapshot mode. If it is disabled on your database, you may greatly reduce deadlock risks by enabling it. This mode allows reads under read committed isolation level to cease issuing read locks.

You may check this setting with

select snapshot_isolation_state_desc, is_read_committed_snapshot_on
    from sys.databases 
    where name = 'YourDbName'

You may enable this setting with

alter database YourDbName
    set allow_snapshot_isolation on

alter database YourDbName
    set read_committed_snapshot on

This requires having none running transaction on the target db. And of course, this requires admin rights on DB.

On an application for which I was not having the option to change this setting, I had to go a more quirky way: setting NHibernate default isolation mode (connection.isolation configuration parameter) to ReadUncommitted. My application was mostly read-only, and I was elevating the isolation mode explicitly on the few transactions having to read then write data (using session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted) by example).

You should also check the isolation modes which are currently used by all applications using the database: are some of them using higher isolation level than actually required? (RepeatableRead and Serializable should be avoided if possible.) This is a time consuming process since it requires a good understanding of isolation levels, while studying each use case for determining what is the appropriate minimal isolation level.