Setting the right transaction isolation mode with NHibernate, how to?

We have switched from sqlite to FireBird embedded server, because FB seems to support concurrent updates of the database, but we sometimes have these exception coming from it:

20开发者_StackOverflow10-10-28 15:49:31,242 [56] ERROR NetworkCatcher.Entities.Agent.Server.RunResultManager - Failed to send result to server 32W2K3SP2VM-DEV. NHibernate.Exceptions.GenericADOException: could not update: ExecutionEntry#89_19_32W2K3SP2VM-DEV][SQL: UPDATE Run SET ExecutionId = ?, Source = ?, Destination = ?, ProtocolId = ?, Duration = ?, SampleCount = ?, StartTime = ?, ServerHostName = ?, SamplesSentToServer = ?, SampleInterval = ?, Parameters = ? WHERE Id = ?] ---> FirebirdSql.Data.FirebirdClient.FbException: deadlock
update conflicts with concurrent update
concurrent transaction number is 31632 --->
FirebirdSql.Data.Common.IscException: deadlock
update conflicts with concurrent update
concurrent transaction number is 31632
   at FirebirdSql.Data.Client.Native.FesDatabase.ParseStatusVector(IntPtr[]
statusVector)
   at FirebirdSql.Data.Client.Native.FesStatement.Execute()
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior
behavior, Boolean returnsSet)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior
behavior)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteNonQuery()
.
.
.

The FB response to this was “Why do you think it's a bug? It's a regular update conflict causing by two transactions updating the same record simultaneously. What is your transaction isolation mode?“

This phrase has puzzled me twice – once, because I was unpleasantly surprised to discover that I may write the same record concurrently and the second time – I have no idea what is my transaction isolation mode and how do I use it to serialize writes to the same record.

The mapping of the object, being updated is:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-
import="true">
  <class name="NetworkCatcher.Entities.Agent.Server.ExecutionManager+ExecutionEntry,NC.Entities.Agent.Server" lazy="false" table="Run" entity-name="ExecutionEntry">
    <id name="Id" column="Id" type="string" >
      <generator class="assigned"/>
    </id>
    <property name="ExecutionId"/>
    <property name="Source"/>
    <property name="Destination"/>
    <property name="ProtocolId" type="string"/>
    <property name="Duration"/>
    <property name="SampleCount"/>
    <property name="StartTime"/>
    <property name="ServerHostName"/>
    <property name="m_samplesSentToServer" column="SamplesSentToServer" type="int" access="field" />
    <property name="SampleInterval"/>
    <property name="Parameters" type="binary"/>
  </class>
</hibernate-mapping>

I am sure there is a good samaritan out there, who knows the answer to my problem. Please, please, please share from your wisdom...

Thanks.

---

Transaction isolation mode is usually set in your hibernatem.cfg.xml file:

<property name="connection.isolation">ReadCommitted</property>

http://www.nhforge.org/doc/nh/en/index.html#configuration-hibernatejdbc

You can find a list of valid values and descriptions of each in the MSDN documentation for System.Data.IsolationLevel:

http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx

You'll have to check the FireBird docs to see which ones it supports.

With respect to your exception, you're hitting a deadlock when updating records and this is expected in relational databases. You should be prepared to catch the deadlock exception and re-try the operation. This has nothing specific to do with NHibernate and everything to do with how relational databases support transactions. Basically you ran into a situation where you tried to update the same two records, A and B, in two different transactions. One transaction has a lock on A and the other transaction has a lock on B. Each transaction needs a lock on the other record to complete. The database engine picks a deadlock victim, rolls back its transaction, tosses it a deadlock exception, and allows the other transaction to complete. If it didn't do this, both transactions would be waiting forever (or the transaction timeout) for the other transaction to complete. (It could be a more complicated cycle of records, r1..rN, and multiple transactions, but the same ideas apply.) The net result is that as an application developer, you have to be prepared to re-try operations that deadlock, whether you're using NHibernate, raw ADO.NET, or any other technology that utilizes a relational database.

---

In addition to the default isolation level as explained by James, you can also set the isolation level for individual transactions by using the overload of ISession.BeginTransaction that takes an IsolationLevel.

Note that this isn't a NH-only thing: it's a standard ADO.NET concept, so you can read more about it in MSDN.