Nhibernate：生成insert then update语句，在多线程环境下会导致死锁问题
在将 NHibernate 与 SQL Server 2005 一起使用时，我经历过以下场景。
我的业务流程涉及以下步骤：
- 启动事务
- 创建 nhibernate 映射对象
- 保存 nhibernate 映射对象
- 执行其他业务工作流程步骤
- 在步骤 2 中更新 nhibernate 映射对象
- 提交事务
在单线程环境中，这工作得很好。然而，当运行在不同实体上执行相同用例的多线程负载测试时，我发现遇到了许多死锁。事务隔离级别保留为已提交读的默认设置。
经过调查，我发现问题的原因是 nhibernate 发出了 insert & 错误。步骤 2 中创建的实体的更新语句在第 5 步中更新。通过进一步测试，我了解到更新语句正在锁定整个表并锁定整个表。不仅仅是正在更新的行，当另一个线程尝试访问表进行插入或更新时，这会导致死锁。
Q1：是否可以让 nhibernate 仅锁定正在更新的行，即在发出更新时应用行锁？
到目前为止，我还没有找到一种方法让 nhibernate 只运行单个插入语句，而不是先插入然后更新语句。
Q2：有人知道可以改变这种行为的配置选项吗？
通过在 SQL Server 数据库上打开快照隔离并将事务隔离级别设置为快照，我成功解决了多线程负载测试中出现的死锁问题。
我很想听听是否有人遇到过类似的问题。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你必须照顾好这一点。建议尽可能减少交易（持续时间）。如果不可能，请确保以相同的顺序更新表以避免死锁。
如果确实不可能，请更改配置中的事务隔离设置，但不建议这样做。
You have to take care of that. It is advised to have the smallest transactions possible (in duration). If not possible, ensure that you are updating tables in the same order to avoid deadlocks.
If really not possible, change your transaction isolation setting in the configuration, but it is not advised.