I've experienced the following scenario when using NHibernate with SQL Server 2005.
I have a business process which involves the following steps:
- Start transaction
- Create nhibernate mapped object
- Save nhibernate mapped object
- Perform other business workflow steps
- Update nhibernate mapped object in step 2
- Commit transaction
In a single threaded environment this works fine. However when running a multithreaded load test which performs the same use case on separate entities I found that I encountered a number of deadlocks. The transaction isolation level was left on the default setting of read committed.
Upon investigation i've found that the cause of the problem is that nhibernate issues an insert & update statement for the entity created in step 2 & updated in step 5. From further testing I understand that the update statement is locking the whole table & not just the rows being updated, this then leads to deadlocks when another thread tries to access the table for an insert or update.
Q1: Is it possible to get nhibernate to just lock the rows it is updating i.e apply a row lock when issuing an update?
As yet I've not found a way of getting nhibernate to just run a single insert statement as opposed to an insert and then update statement.
Q2: Does anyone know of a configuration option to alter this behaviour?
I've managed to get over the problem of deadlocks occuring on the multithreaded load test by switching on snapshot isolation on my SQL Server database and setting the transaction isolation level to snapshot.
I'd be interested to hear if anyone has experienced any similar issues.