Table Indexes to resist Dead lock occurances

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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.

Posted 22-Jan-15 1:38am

K.Y.S.Phaneendra

Add a Solution

Comments

Sergey Alexandrovich Kryukov 22-Jan-15 11:42am

Even one deadlock during single lifetime is impossibly too many. I am even starting to suspect that you mean not deadlock, but something else.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-01-22T01:56:00

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.