Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: Replication Deadlock...I think

    I am running a select cursor on spid:61 and an update statement (on a table common with the select in spid:61) on spid:51. While running, I experience a deadlock. The Enterprise Manager reports that spid:61 (Blocking) and spid:51 (Blocked by 61). SP_LOCK reports the following (with object_name resolved):


    object_name spid mode status
    -------------------- ----------- ---------- ------
    NULL 51 S GRANT
    sp_MSaddpub_snapshot 51 IX WAIT
    sp_replflush 61 IS GRANT
    sp_replcounters 61 IS GRANT
    sp_changesubscriptio 61 IS GRANT
    sp_MSscript_sync_del 61 IS GRANT
    sp_MSscript_sync_ins 61 IS GRANT
    sp_replpostschema 61 IS GRANT
    sp_articleview 61 IS GRANT
    sp_MSaddschemaarticl 61 IS GRANT
    sp_changemergepublic 61 IS GRANT
    sp_MSmakeconflicttab 61 IS GRANT
    sp_MScopyscriptfile 61 IS GRANT
    sp_MSuniquecolname 61 IS GRANT
    sp_MSget_synctran_co 61 IS GRANT
    sp_MShelptranconflic 61 IS GRANT
    sp_replsetsyncstatus 61 IS GRANT
    sp_MSaddpub_snapshot 61 S GRANT
    NULL 61 S GRANT

    (19 row(s) affected)

    I see that the two SPIDs have the sp_MSaddpub_snapshot in common. This stored procedure attempts to update the syspublications table however I fail to see why this would generate a deadlock. I have rebooted the server, disabled and re-enabled replication. Any help would be appreciated.

    --Other info--

    Microsoft SQL Server 2000 Enterprise sp3 (active-passive cluster)
    4 x 1.4ghz Xeon, 8gig (/3gb /pae)
    Microsoft Windows 2000 Advanced Server sp3 (Identical active-passive nodes)

    Database connection thru Trifox's "Vortex" DBlib linked client-server driver.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What type of replication are you using ? Can you post what you are doing with the select cursor/update ?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Also, does the deadlock occur immediately ?

  4. #4
    Join Date
    Dec 2003
    Posts
    7

    deadlock

    >>What kind of replication?
    The server was initially set up with trans replication. I have since rebooted and removed replication alltogether and selectivly readded components, reproducing the deadlock behavior at every step.

    >>does it happen immediately?
    There are a number of other SELECT statements between the (Blocking) SELECT statement and when the UPDATE fires (approx 60-90 sec later). I can see the SQLTMTStarting in the Profiler for the UPDATE statement however it never completes before the one hour connection timeout from the Vortex driver. I can KILL the SPID for the (Blocking) SELECT statement and then the (Blocked) UPDATE will fire. . . however I am not done fetching records from the SELECT recordset and the "fetch next row" from that cursor will fail (ConnectionWrite(Send())) or something like that -- typical MS message when trying to work with a connection killed at the server level. The SP_LOCK information indicates that the initial SELECT does not have any table locks and only low level child REPLICATION stored procedures are holding any locks.

    >>What are the statements?
    The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.
    The UPDATE statement is trival e.g.

    UPDATE owner.table
    set column_a = 1
    where column_b = 'value'

    I appreciate your time.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried adding readuncommitted/nolock to your select statement (because this sounds like nested transactions that are attempting to lock the same resource) ? Realize that this will give you dirty records - but test it to see if you still have the problem.

  6. #6
    Join Date
    Dec 2003
    Posts
    7
    I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Makallus
    I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.
    Yeah, and let's you read uncommited transactions....

    Expect miracles to start happening....
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: deadlock

    Originally posted by Makallus
    >>>>What are the statements?
    The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.
    What do you do with the result set? ARe youdragging thousands of rows to the client?

    Is it done in a sproc?

    Are you placing the results in table variables?

    How does your update coorelate to the thousands of rows?

    Why do the SELECT at all? Why not make the UPDATE a JOIN?
    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.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    I would look at your logic and modify it to prevent the lock escalation. Depending on your database/applications, reading dirty records could cause some major problems.

  10. #10
    Join Date
    Dec 2003
    Posts
    7
    All excellent replies. The reasons we (the company) do things this way come directly from Dilbert comic strips.

    Brett, ever code in Dibol? What's better than coding in Dibol with database access is the fantasy we will be database independent. The idea being we can slap in any database the customer likes with no impact on the application. Shall I go on? However I understand your questions and I believe in time the application will behave more like it was written for a database than an ISAM file.

    Rnealejr, this problem only occurs at a site where replication is enabled . . . given that there is no replication I do not appear to exceed the lock threshold.

    As for doing things right. . . all good things in time.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    The lock escalation issue may appear in non-replication scenarios as well - but be intermittent. I just wanted to make sure that you understood the ramifications.

Posting Permissions

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