Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    114

    Unanswered: Too many deadlocks

    Hi,

    I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
    During past year, the number of users has grow (from 100 before to 500 or 700 now)


    *** Deadlock Detected ***
    - Requested by: SPID 360 ECID 0 Mode "S"
    - Held by: SPID 113 ECID 0 Mode "S"
    Index: aaaaa_PK
    Table: TABLE_1
    Database: MYDB
    == Lock: KEY: 22:325576198:1 (ff009ae5078d)
    - Requested by: SPID 113 ECID 0 Mode "S"
    - Held by: SPID 374 ECID 0 Mode "X"
    Index: aaaaa_PK
    Table: TABLE_1
    Database: MYDB
    == Lock: KEY: 22:325576198:1 (ff009ae5078d)
    - Requested by: SPID 374 ECID 0 Mode "IX"
    - Held by: SPID 360 ECID 0 Mode "S"
    Table: TABLE_2
    Database: MYDB
    == Lock: PAG: 22:1:2428
    == Deadlock Lock participant information:
    Input Buf: S E L E C T the_rest_of_the_query
    SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
    Input Buf: s p _ e x e c u t e 8
    Input Buf: s p _ c u r s o r 8\ B 8 8f @ Table I
    Input Buf: S E L E C T the_rest_of_the_query
    SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
    == Session participant information:
    == Deadlock Detected at:
    ==> Process 360 chosen as deadlock victim


    I have done :
    - rebuild indexes on all tables (fillfactor 90)
    - analysed memory activity

    Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?

    Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.


    Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700


    Thanks for any help.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Rewrite the application! You are experiencing scalability problems.

    Here's an example:
    Spid x is holding an update lock on table a and wants to issue a read lock on table b.
    Spid y is holding an update lock on table b and wants to issue a read lock on table a.

    These processes are engaged in what's called "a deadly embrace" and it's up to SQ Server to break the deadlock. So it does. It choses one of the processes as the "victim", rolls back it's updates, so that the other one can complete it's work.

    No amount of reindexing is going to solve the problem. No amount of memory is going solve the problem. Removing the contention ** is ** going to solve the problem. You want your processes to access the tables in the same order (a,b,c) so that the processes move forward like the lines at Disney World, and not helter skelter like the freeway at rush hour.

  3. #3
    Join Date
    Mar 2004
    Posts
    114
    Ok, I have thought to this solution.

    But I am astonished by this point : the application has 4 years, it has run well. I have 5 SQL servers (same config). Some have many deadlocks, while others have few deadlocks. It is the same application.

    Moreover, I was talking about indexes, because on one of these servers, a sqlmaint job has not run for several months. This job was rebuilding all indexes. The server had few deadlocks (less than 10 a day). I have repaired the job, and it has run during a week-end. On monday, the were 358 deadlocks during the day.

    I agree with your answer, but some observations are contradictory.

    This problem is going to turn me mad !

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Deadlocks are pretty much a function of the number of spids (users) accessing code that doesn't avoid deadlocking. As the quality of the code degrades, the deadlocks increase. As the quantity of spids (users) increases, the deadlocks increase.

    A poorly performing system tends to force most of its use to the office "peon" because the higher level employees won't waste time on it. If the system is slow enough, only one person (the lowest level employee in the office) will actually be using it because everyone else will delegate the task to them.

    It is extremely rare for one user to deadlock themselves. That can only happen when they've got more than one spid (database connection).

    If you drastically improved the application's performance by fixing the problems with the sqlmaint job, you might have actually increased the number of users which then brought on the problems with the deadlocking!

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    114
    Right. I finally think that the cause comes from the number of spid. Sometimes, there is more than 800 simultaneous connections. And I have heard yesterday that the editor made the apps for about 500 users.

    Thank you for your help PatP and tomh53 !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •