SQL Server Performance

deciphering deadlock info in errorlog

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by mst, Apr 21, 2008.

  1. mst New Member

    I've started tracking deadlocks on a newly implemented system (sql2005 sp2) and have a question.
    I thought I generally understood the logged information-- but a few things don't appear to make sense. Under RESOURCE-LIST, I see 2 KEYLOCK lines. But to me they don't look like they should be in conflict since one is on table1 and the other is on table2. Unless my more speculative ??? interpretations are right... (if they are I think this becomes a case of ROUTINE 1 - processes table1 then table2 and ROUTINE 2 - processes table2 then table1)
    Am I on the right track or out in the weeds?
    From ErrorlogMy interpretation
    deadlock-list
    deadlock victim=processa647978 Ok, we know which was victim
    process-list
    process id=processa647978 taskpriority=0 logused=0 waitresource=KEY: 19:72057594073055232 (43009cc6f1f0) waittime=3970 ownerId=203661558 transactionname=SELECT lasttranstarted=2008-04-21T09:19:09.823 XDES=0xe2bf48bd8 lockMode=S schedulerid=6 kpid=8704 sThe victimized process
    executionStack
    frame procname=adhoc line=1 sqlhandle=0x02000000f6de65093f68813f7692f07d77811a52fb9b365b
    select top 8 contractor0_.PartyID as CO1_4_, contractor0_2_.CO_RowVersionNum as CO2_4_, contractor0_2_.FullName as FullName4_, contractor0_2_.EmailAddress as EmailAdd4_4_, contractor0_2_.ActivatedDate as Activate5_4_, contractor0_2_.InactiveReason as InactThe code for the victim
    frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
    unknown
    inputbuf
    IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off
    process id=processae5cc58 taskpriority=0 logused=300 waitresource=KEY: 19:72057594073186304 (43009cc6f1f0) waittime=2720 ownerId=203660802 transactionname=implicit_transaction lasttranstarted=2008-04-21T09:19:08.443 XDES=0x3d01d3ad0 lockMode=X schedulerThis was the other process- it gets to finish
    executionStack
    frame procname=adhoc line=1 stmtstart=62 sqlhandle=0x020000001dfb663b5c9449e16fa12639911c8126356a8a8e
    update CO_Partner set [email protected] where [email protected] Another line for the finisher
    frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
    unknown
    inputbuf
    (@P0 bigint)select fieldservi0_.CO_ID as CO1_4_0_, fieldservi0_.CO_RowVersionNum as CO2_4_0_, fieldservi0_.CO_CreatedBy as CO11_4_0_, fieldservi0_.CO_CreatedDT as CO12_4_0_, fieldservi0_.EmailAddress as EmailAdd4_4_0_, fieldservi0_.FullName as FullName4_0_And another line for the finisher
    resource-list
    keylock hobtid=72057594073186304 dbid=19 objectname=FALCON.dbo.CO_Partner indexname=CO_Partner_PK id=lock447552d00 mode=S associatedObjectId=72057594073186304 I think this is the lock held by the victim (2 rows below)
    owner-list
    owner id=processa647978 mode=S ? or is this held by the victim—and
    waiter-list ? being waited for by the finisher?
    waiter id=processae5cc58 mode=X requestType=wait
    keylock hobtid=72057594073055232 dbid=19 objectname=FALCON.dbo.CO_Party indexname=CO_Party_PK id=locke25f81d00 mode=X associatedObjectId=72057594073055232 I think this is the lock held by the finisher (2 rows below)
    owner-list
    owner id=processae5cc58 mode=X ? and this held by the finisher
    waiter-list
    waiter id=processa647978 mode=S requestType=wait ? and victim is waiting
  2. satya Moderator

    I don't see any issue as both of them looking into different indexes used in that query.
  3. mst New Member

    That's the reason for my OP! If these locks are not in conflict then WHY did SQL detect a deadlock and choose a deadlock victim?
  4. satya Moderator

    Can you check the relation between the objectid referred on both of them>
  5. mst New Member

    Ok- that has to be the answer (and explanation for the deadlock in the first place): There's a FK in partner referencing the PK in party (note 3rd yellow-highlighted block in code).
    So I guess the mechanics of this are: the FK in partner tries to tie to party.PK; and party.PK is blocked...

Share This Page