使用IsolationLevel.RepeatableRead进行死锁 [英] Dead lock by using IsolationLevel.RepeatableRead

问题描述

HI全部，

最近我的团队在asp.net c＃上完成了一个项目。它是为现有项目添加功能，由其他公司完成。



它是一个购物车。当用户在非常罕见的情况下下订单时，我们遇到死锁情况，数据库连接完全丢失，这是通过entripise库建立的



错误：< b>事务（进程ID 89）在锁资源上与另一个进程死锁，并被选为死锁牺牲品。重新运行交易。



所以我们搜索死锁发生的各种原因最后我们才知道死锁的确切原因


DBWrapper wrapper = new DBWrapper（true，System.Data.IsolationLevel.RepeatableRead） ）;



使用 IsolationLevel.RepeatableRead 导致死锁问题。



所以我对sql中的Locks概念知之甚少。



预期的sloutions：



1）计划是改变隔离级别，这将与 IsolationLevel.RepeatableRead 类似，并防止死锁发生。





2）或者在代码中保持相同的隔离级别并使用任何锁定进行sql查询

概念并使其成功运行。





请将它作为我们的首要问题...您的想法将受到高度赞赏。



请建议我为我的预期解决方案做些什么。

HI All,
Recently my team done a project on asp.net c# . It is to add the features to the exisiting project which is done by some other company.

Its a shopping cart. When the user places an order in very rare cases we are running into dead lock situation and the Database connections are being completely lost which are established through entripise library

Error : Transaction (Process ID 89) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So we searched for various reasons for the occurance of dead lock finally we came to know the exact reaosn for the dead lock

Reason :

DBWrapper wrapper = new DBWrapper(true,System.Data.IsolationLevel.RepeatableRead);

Using of IsolationLevel.RepeatableRead causing the dead lock issue.

So I have bit poor knowledge on Locks concept in sql.

Expected sloutions:

1) Plan is to change the isolation level which will be similar behaviour to IsolationLevel.RepeatableRead and prevent dead lock occurance.


2) Or else keeping the same isolation level in the code and make sql query with any lock
concept and make it work successful.


Please its a high priority issue for us... Your thoughts will be highly appreciated.

Please suggest me what to do for my expected solution .

推荐答案

此评论很长，可能回答你的问题：



IsolationLevel.RepeatableRead是非常严格的。它确保在读取时锁定任何数据。您拥有的任何选定交易也将锁定该行，直到交易完成。

所以（A）读，（B）试图读但死锁，（A）完成。 （B）将被丢弃，因为它拥有较少的资源。



当我有更大的交易时，我更喜欢IsolationLevel.Snapshot。它有风险，但它适用于我的情况：

（A）读取，（B）读取，（A）写入，（B）不知道数据的平均时间发生变化因此（B）写一遍。



我几乎从来没有两个交易按设计写入相同的行。他们可以做到这一点并不重要，我很高兴让一个人覆盖另一个。我会经常从快速更新的行中读取，但因为我经常轮询我的数据，所以我不介意我的数据是否过时了一两秒。



研究隔离级别： - 考虑对特定事务使用特定隔离级别： - 可能需要此隔离级别，在这种情况下添加重试过程。

This comment was long and may answer your question:

IsolationLevel.RepeatableRead is very restrictive. It ensures that any data is locked at the point of being read. Any select transactions you have will also lock the row until the transaction has been completed.
so (A) reads, (B) tried to read but deadlocks, (A) completes. (B) will be discarded as it holds fewer resources.

I prefer IsolationLevel.Snapshot when I have larger transactions. It's risky but it work in my case:
(A) reads, (B) reads, (A) writes, (B) is unaware that the data has changes in the mean time so (B) writes over it.

I will almost never have two transactions writing to the same rows by design. Where they can do this is not critical and I'm happy to let one overwrite the other. I will often read from rows that are being rapidly updated but because I poll my data very often I don't mind if my data is out-of-date by a second or two.

Research the isolation levels:- Consider using specific isolation levels for specific transactions:- It may be that you require this isolation level in which case add a retry process.

这篇关于使用IsolationLevel.RepeatableRead进行死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！