SQL Server 2008 和 ADO.NET 的死锁问题
在我们的应用程序中，我们在过程中不使用 ADO.NET 事务或 SQL Server 事务，现在当多人使用时，我们的网站会出现以下错误。
事务（进程 ID 73）在锁定 | 时陷入死锁与另一个进程通信缓冲区资源并已被选为死锁受害者。重新运行事务
这个错误是由于缺少交易造成的吗？我认为一致性将由数据库本身来处理。
我注意到 SQLCommand.Timeout 属性已设置为 10000。这会是错误的问题吗？
我正在努力尽快解决这个问题。请帮忙。
编辑
我看到了ADO.NET事务的Isolationlevel属性，所以如果我使用具有适当隔离级别属性的ADO.NET事务，例如读取期间的“ReadUncommissed”和写入期间的“Serializable”？
In our applications we don't use either ADO.NET transaction or SQL Server transactions in procedures and now we are getting the below error in our website when multiple people are using.
Transaction (Process ID 73) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Is this error due to the lack of transactions? I thought the consistency will be handled by the DB itself.
And one thing I noticed that SQLCommand.Timeout property has set to 10000. Will this be an issue for the error?
I am trying to solve this issue ASAP. Please help.
EDIT
I saw the Isolationlevel property of ADO.NET transaction, so if I use ADO.NET transaction with proper isolationlevel property like "ReadUncommitted" during reading and "Serializable" during writing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
每个 SQL DML（INSERT、UPDATE、DELETE）或 DQL (SELECT) 语句都在事务内运行。 SQL Server 的默认行为是打开一个新事务（如果不存在），如果语句完成且没有错误，则自动提交该事务。
Sidharth 提到的 IMPLICIT_TRANSACTIONS 行为基本上让 SQL Server 在某种程度上改变了它的行为 - 它在语句完成时使事务保持打开状态。
要在 SQL Server 错误日志中获取更好的信息，您可以打开 跟踪标志。然后，这将告诉您哪些连接涉及死锁（不仅仅是被杀死的连接），以及涉及哪些资源。然后，您也许能够确定哪种行为模式导致了僵局。
如果您无法确定根本原因，则可能需要向应用程序添加一些额外的代码 - 捕获由于死锁而导致的 sql 错误，并多次重试该命令。这通常是最后的手段 - 最好确定涉及哪些表/索引，并首先制定避免死锁的策略。
Every SQL DML (INSERT, UPDATE, DELETE) or DQL (SELECT) statement runs inside a transaction. The default behaviour for SQL Server is for it to open a new transaction (if one doesn't exist), and if the statement completes without errors, to automatically commit the transaction.
The IMPLICIT_TRANSACTIONS behaviour that Sidharth mentions basically gets SQL Server to change it's behaviour somewhat - it leaves the transaction open when the statement completes.
To get better information in the SQL Server error log, you can turn on a trace flag. This will then tell you which connections were involved in the deadlock (not just the one that got killed), and which resources were involved. You may then be able to determine what pattern of behaviour is leading to the deadlocks.
If you're unable to determine the underlying cause, you may have to add some additional code to your application - that catches sql errors due to deadlocks, and retries the command multiple times. This is usually the last resort - it's better to determine which tables/indexes are involved, and work out a strategy that avoids the deadlocks in the first place.
IsolationLevel 是您最好的选择。事务的默认序列化级别是“可序列化”，这是最严格的，如果在此级别存在循环引用，则死锁的可能性非常高。读取时将其设置为 ReadComfilled，写入时将其设置为 Serialized。
Sql 服务器可以使用隐式事务，这在您的情况下可能会发生。尝试将其关闭：
在此处阅读相关信息：http://msdn.microsoft。 com/en-us/library/ms190230.aspx
IsolationLevel is your best bet. Default serialization level of transactions is "Serializable" which is the most stringent and if at this level there is a circular reference chances of deadlock are very high. Set it to ReadCommitted while reading and let it be Serializable while writing.
Sql server can use implicit transactions which is what might be happening in your case. Try setting it off:
Read about it here: http://msdn.microsoft.com/en-us/library/ms190230.aspx