Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Deadlocks (I think)

    Hi folks,

    I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

    I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
    Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

    SELECT spid, waittime, lastwaittype, waitresource
    FROM master..sysprocesses
    WHERE waittime > 10000
    AND spid > 50

    SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
    FROM master..sysprocesses p
    WHERE open_tran > 0
    AND spid > 50
    AND datediff (s, last_batch, getdate ()) > 30
    ANd EXISTS (SELECT * FROM master..syslockinfo l
    WHERE req_spid = p.spid AND rsc_type <> 2)

    I get:

    55 860978 LCK_M_X PAG: 13:1:2573

    54 AWAITING COMMAND sleeping sa 1 1499
    55 UPDATE sleeping sa 2 1499


    respectively. Any help would be welcome.

    Thanks in advance,
    Don

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The queries will most likely not show you a deadlock situation. When SQL server detects a deadlock situation, one of the connections is immediately killed. Try this command, then run the deadlocking process again:

    dbcc traceon (-1, 1204, 1205)

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    I agree that the queries will only show hanging processes but that's what I found, if I am interpreting the results correctly. I did run the TRACEON command although only for 1204 but I cannot find any output from it in the Error log, in fact the last line in the log is:

    2004-02-16 12:22:34.81 spid52 DBCC TRACEON 1204, server process ID (SPID) 52

    Am I looking in the wrong spot?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If I remember correctly, the -1 flag indicates that this flag is to be applied to all spids. If you do not specify the -1, and a deadlock occurs on a separate spid, then the deadlock is not recorded.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    I took your advice, I also discovered that flag 3605 will send that info to the error log. Every five seconds I get an entry to advise me there is no deadlock, but I am still getting the same freezing behaviour in my application.

    I would make the assumption that the problem lies in the application however those queries I mentioned before are returning results to indicate my application SPID is waiting on a resource and the same code base operates on Oracle.

    Any ideas?

  6. #6
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    On the microsoft site there is a sp_blocker procedure which may help you - there is one there for sql 7 and 2000. I don't know how supported it is though!

    The procedure will show you the SQL statments causing the blocking lock and the I also think is shows you the blocked SQL statement as well.

    You would be wise to test it thoughly though before letting it loose on your prod sys though.
    Regards
    Dbabren

  7. #7
    Join Date
    Feb 2004
    Posts
    4

    Thanks, just one last question

    First, thanks to MCrowley and dbabren. I appreciate your prompt and informative replies. It turns out that my problem was due to a select made just a few statements before the update that was holding a lock on that page.

    Please forgive my ignorance in SQLServer but I have run accross many applications running on other DB's that perform a select as a page is entered to display the data to be modified and an update when the changes are submitted. It seems very strange that SQLServer would require me to lace the code with NOLOCK in order to prevent the initial selects from interfering with the subsequent updates. Admittedly the selects are more complicated than necessary due to an antiquated schema but I fail to see how SQLServer could remain competitive in the market with these types of oversights.

    I must be missing something!

  8. #8
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    I think you need to commit your select statements - that will release the shared locks for you. It sounds like the select and update statements are part of the same transaction - which makes sense if you are selecting for update purposes.

    or

    set the isolation level for each session to READ UNCOMMITTED which will allow dirty reads - it will also mean that your selects will take no shared lock. This is dangerous though, as data you are updating may be updated by another session at the same time - and one of the updates will be lost.
    Regards
    Dbabren

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This is a common problem in making the transfer from Oracle to SQL Server. In SQL Server, readers block writers. In Oracle, Readers just get outdated information. I am sure you could get a raging flame-war started on which is a better solution to the problem of concurrency.

    Basic rule of thumb for all platforms, though, is get in and get out as quick as possible. With both reads, and writes.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by dbabren
    set the isolation level for each session to READ UNCOMMITTED
    No


    it will also mean that your selects will take no shared lock. This is dangerous though
    Yes


    Basic rule of thumb for all platforms, though, is get in and get out as quick as possible.
    Yes Yes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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