0

I have a peculiar situation. I have tables that are constantly accessed by different parts of our code and thousands of clients and so we employed the use of transactions when doing simple updates and inserts on our tables. Problem is we keep getting deadlock errors. Anyone have any idea how I can alleviate this problem?

A-K
  • 16,804
  • 8
  • 54
  • 74
Kobojunkie
  • 6,375
  • 31
  • 109
  • 164
  • 2
    if you have "thousands of clients" I suggest you pay someone to help you fix your problems – Mitch Wheat Oct 23 '10 at 17:17
  • You give too abstract description for your problem I think. How you detect what deadlock happens in-place? How you access to database (LINQToSQL, DBAdapters or whatever)? What do you use: do you implement syncronized access or go with optimistic concurrency? Give some more details. – Nick Martyshchenko Oct 23 '10 at 17:20
  • My Accept rate? I need suggestions on how to tackle deadlock issues please. I am working on fixing the problem but running into walls and in need of help on what else to probably do to tackle the issue. – Kobojunkie Oct 23 '10 at 17:21
  • Could you please help me with answers to my question? – Kobojunkie Oct 23 '10 at 17:22
  • 2
    @Kobo: I think Mitch's answer was "No". – Dave Markle Oct 23 '10 at 17:24
  • 2
    I've looked at some of your questions. Why do you think you get so few answers? Why should people spend time answering your questions if you can't spend some time to write a decent description of your problem? – jgauffin Oct 23 '10 at 18:16
  • Do you get an ego boost of some sort from the points or something? If the answers don't help, do you simply want the person to check yours so you can feel better about yourself or something? I mean what is with the focus on whether a person chooses an answer or not? – Kobojunkie Oct 25 '10 at 22:56

2 Answers2

5

Deadlocks can arise for many reasons and combinations thereof:

  • Poor schema design

  • Incorrect indexes for your query workload

  • Poorly written TSQL

  • Aggressive transaction isolation levels and/or long running open transactions

  • Poor application access patterns

  • Low spec or incorrectly configured hardware

All of these are common.

I suggest you read

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Will work with this. I did not write the code,but I am to debug it and that is why I am in need of help in this. I tried using the thread.sleep approach, employing a transactionscope object but that did not help much is reason why I am asking for help from those who are more familiar with handling this situation – Kobojunkie Oct 25 '10 at 22:19
  • I do have a question, what about turning on Row Versioning? WIll this help? – Kobojunkie Oct 25 '10 at 23:11
4

This problem isn't too peculiar -- it's typical when developers don't know much about how locking works, and just think of transactions as 'black boxes' and expect their solutions to scale.

Mitch is right in the comments about paying someone who is an expert -- this is a problem that's too big for any solution on SO. You are going to need to be armed with traces of queries causing deadlocks and you're going to have to analyze everything from your indexes to your table design, to your transaction isolation levels, to your query patterns.

I suggest starting with SQL Server Profiler and setting up a trace which will generate a deadlock graph. That will at least identify your problem queries and the resources which are deadlocking. Set up another trace looking for slow queries (> say, 100ms) and speed those up, too. The longer your queries run, the higher the probability of lock contention.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170