表索引抵制死锁发生 [英] Table Indexes to resist Dead lock occurances

问题描述

我是SQL-SERVER Traniee



我对死锁有疑问。我们系统每天至少发现2-5个死锁。

这个数字太大了......？



即使它是不，我开始知道在表上使用正确的索引可以减少死锁发生的次数。但是'正确的索引'这个词定义了什么。

几乎所有的表都有聚簇索引和一些非聚集索引。

任何人都可以清楚我的疑问'可以减少死锁事件的正确索引'



是否可以拥有无​​死锁系统。

Hi,
I am SQL-SERVER Traniee

I got a doubt about deadlocks. at least 2-5 deadlocks are found daily in our system.
Is that too big number...?

Even if it is not, I came to know that having Proper Indexes on the tables can reduce the number of deadlock occurrences.But what does the word 'Proper Indexes' define.
almost all the tables got clustered indexes and some non clustered indexes.
Can any one clear my doubt 'Proper Indexes that can reduce the deadlock occurrences'

Is it possible to have a deadlock free system.

推荐答案

首先，由于应用程序设计，通常会发生死锁。在完美的世界中，所有锁定操作都以相同的顺序执行其相应的资源，因此不会发生死锁。但是，这个世界并不完美:)



我不可能说2-5死锁是否太多但对我来说听起来相当高。



正确索引可以减少死锁事件，在这种情况下加速操作，以便发生死锁的可能性更小。



但是，请记住，这并不一定意味着您应该添加索引。情况可能相反。例如，如果您有一个只添加新数据的表，则索引会降低插入操作的速度，从而导致发生死锁的可能性增加。另一方面，可以通过添加支持这些操作的where子句的索引来实现加速删除或更新。



所以正确的索引实际上意味着找到一个很好的平衡对数据库执行的所有操作。

First of all, very often a deadlock occurs because of application design. In a perfect world all the locking operations are executed in the same order for their corresponding resources so no deadlocks can occur. However, the world is not perfect :)

It's quite impossible to say if 2-5 deadlocks is too much but it sounds quite high to me.

Proper indexing may reduce deadlock occurences in such case when they speed up operations so that the probability of a deadlock is smaller.

However, keep in mind that this doesn't necessarily mean that you should add indexes. The situation may be vice versa. For example if you have a table where you just add new data, an index slows down the insert operation so the chance of a deadlock to occur rises. On the other hand speeding up deletions or updates can be achieved by adding indexes that support the where clauses of these operations.

So proper indexing actually means finding a good balance between all of the operations that are executed against the database.

这篇关于表索引抵制死锁发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！