如何避免SQL僵局？ [英] How to avoid sql deadlock?

问题描述

我使用MS-SQL 2008和正在使用C＃在Web项目中的编码。

I am using MS-SQL 2008 and for coding am using C# in a web project.

目前，我被困在SQL僵局。

Currently, I got stuck with sql deadlock.

我有2个交易中，两个一个是长事务。

I have 2 transactions, one of two is a long transaction.

当交易双方同时运行，发生死锁和短期交易将选择自动杀死。

When both transactions run simultaneously, deadlock occurs and short transaction will be chose to be killed automatically.

我目前的解决方案，我赶上死锁的异常，并重新进行短事务，它运行良好，只是需要一定的时间。

My Current solution, I am catching the exception of deadlock and redo the short transaction, it run well, just take some time.

不过，我想知道，如果有任何解决方案，以避免从根源僵局？

But, I wonder that if there is any solution to avoid deadlock from the root cause?

推荐答案

没有，你不能避免死锁完全，只能减轻他们。

No, you can't avoid deadlocks entirely, only mitigate them.

• 使用<一个href=\"http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/\">RCSI隔离模式。这基本上是 MVCC 的，但你必须提防SQL Server将的仍然使用之际（DBA.SE答案）的ANSI ACID模式。此模式将停止作家阻止读者，但不会与作家，作家相互帮助块

• Using RCSI isolation mode. This is basically MVCC, but you have to beware that SQL Server will still use the ANSI ACID modes on occasion (DBA.SE answer). This mode stops writers blocking readers but will not help with writer-writer mutual blocks.

客户端重试逻辑。经典方法

Client retry logic. The classic way

尝试做相同的顺序如T1表更新 - > T2总是

Try to do table updates in the same order eg T1 -> T2 always

避免长时间交易：是客户端圆跳闸了很多，保持交易开放的例子。您可以使用存储过程，更好的索引或者干脆调查询缓解这个

Avoid long transactions: is the client round tripping a lot and keeping the transaction open for example. You can mitigate this with stored procedures, better indexes or simply tune the query

这篇关于如何避免SQL僵局？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！