用NHibernate设置正确的事务隔离模式，如何？ [英] Setting the right transaction isolation mode with NHibernate, how to?

问题描述

我们已经从sqlite切换到FireBird嵌入式服务器，因为FB
似乎支持数据库的并发更新，但我们有时
有这些异常：

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:

2010-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()
.
.
.

FB的回应是
你为什么认为这是一个错误？这是一个定期更新冲突导致
由两个事务同时更新同一记录。什么是
你的事务隔离模式？

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>

我相信有一个很好的samaritan在那里，谁知道
的答案问题。请，请分享你的智慧...

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

谢谢。

推荐答案

事务隔离模式通常在hibernatem.cfg.xml文件中设置：

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

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

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

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

您可以在MSDN文档中找到System.Data.IsolationLevel的有效值列表和描述：

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

您必须检查FireBird文档以查看它支持哪些文档。

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

对于你的异常，当更新记录时，你会遇到死锁，这在关系数据库中是需要的。您应该准备好捕获死锁异常并重试该操作。这与NHibernate没有什么关系，一切都与关系数据库如何支持事务有关。基本上你碰到一种情况，你试图更新两个不同的事务中相同的两个记录A和B.一个事务在A上有锁，而另一个事务在B上有一个锁。每个事务需要在另一个记录上完成锁定。数据库引擎选择死锁牺牲品，回滚其事务，将其抛弃为死锁异常，并允许其他事务完成。如果它没有这样做，两个事务将永远等待（或事务超时）其他事务完成。 （它可能是一个更复杂的记录循环，r1..rN和多个事务，但是相同的想法适用。）最终结果是作为应用程序开发人员，您必须准备重新尝试死锁，无论您是使用NHibernate，原始ADO.NET还是任何其他利用关系数据库的技术。

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.

这篇关于用NHibernate设置正确的事务隔离模式，如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！