Results 1 to 2 of 2

Thread: deadlock !!

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: deadlock !!

    Below is the trace which was printed in the error logs after adding -T1222 as startup parameter to MSSQL database,

    2010-07-12 16:01:16.91 spid7s Recovery is complete. This is an informational message only. No user action is required.
    2010-07-12 18:39:59.60 spid13s deadlock-list
    2010-07-12 18:39:59.60 spid13s deadlock victim=process1934c8
    2010-07-12 18:39:59.60 spid13s process-list
    2010-07-12 18:39:59.60 spid13s process id=process1934c8 taskpriority=0 logused=0 waitresource=OBJECT: 7:1953442033:0 waittime=2398 ownerId=1721972 transactionname=SELECT lasttranstarted=2010-07-12T18:39:57.160 XDES=0x80003940 lockMode=IS schedulerid=3 kpid=5408 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2010-07-12T18:39:57.160 lastbatchcompleted=2010-07-12T18:39:57.143 clientapp=jTDS hostname=FELOG-W2K8 hostpid=123 loginname=sa isolationlevel=read committed (2) xactid=1721972 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2010-07-12 18:39:59.60 spid13s executionStack
    2010-07-12 18:39:59.60 spid13s frame procname=adhoc line=1 sqlhandle=0x02000000ffb4a521d61b887a9289ef153c7a8a 2bd14dc5e2
    2010-07-12 18:39:59.60 spid13s SELECT "R1"."Column1","R1".Column2","RToRC"."C1","RToRC". "C2" FROM "R1" LEFT JOIN "RTRC" ON "R1"."C1"="RTRC"."C3"
    2010-07-12 18:39:59.60 spid13s inputbuf
    2010-07-12 18:39:59.60 spid13s SELECT "R1"."Column1","R1".Column2","RToRC"."C1","RToRC". "C2" FROM "R1" LEFT JOIN "RTRC" ON "R1"."C1"="RTRC"."C3"
    2010-07-12 18:39:59.60 spid13s process id=process179948 taskpriority=0 logused=78648 waitresource=OBJECT: 7:516248944:0 waittime=2382 ownerId=1721968 transactionname=implicit_transaction lasttranstarted=2010-07-12T18:39:56.210 XDES=0x89dfd970 lockMode=Sch-M schedulerid=2 kpid=7708 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-07-12T18:39:57.160 lastbatchcompleted=2010-07-12T18:39:57.160 clientapp=jTDS hostname=FELOG-W2K8 hostpid=123 loginname=sa isolationlevel=read committed (2) xactid=1721968 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
    2010-07-12 18:39:59.60 spid13s executionStack
    2010-07-12 18:39:59.60 spid13s frame procname=adhoc line=2 stmtstart=1796 sqlhandle=0x02000000f91a6829896bc6386fa0cee4eac444 3075bb30d6
    2010-07-12 18:39:59.60 spid13s ALTER TABLE RS ADD CONSTRAINT RS_FK2 FOREIGN KEY ("RID") REFERENCES R1("C1") ON DELETE CASCADE;
    2010-07-12 18:39:59.60 spid13s inputbuf
    2010-07-12 18:39:59.60 spid13s
    IF NOT EXISTS (select * from sysobjects where name='RS' and xtype='U') CREATE TABLE RS ( "DAY" datetime NOT NULL, "RSID" bigint NOT NULL, "C1" int DEFAULT ('0') , "C2" int DEFAULT ('0') , "C3" int DEFAULT ('0') , "C4" int DEFAULT ('0') ); ALTER TABLE RS ADD PRIMARY KEY ("DAY","RSID");CREATE INDEX RS_IDX0 ON RS ( "RSID" );ALTER TABLE RS ADD CONSTRAINT RS_FK2 FOREIGN KEY ("RSID") REFERENCES R1("c1") ON DELETE C
    2010-07-12 18:39:59.60 spid13s resource-list
    2010-07-12 18:39:59.60 spid13s objectlock lockPartition=0 objid=1953442033 subresource=FULL dbid=7 objectname=fefirewall.dbo.R1 id=lockb9ca0f80 mode=Sch-M associatedObjectId=1953442033
    2010-07-12 18:39:59.60 spid13s owner-list
    2010-07-12 18:39:59.60 spid13s owner id=process179948 mode=Sch-M
    2010-07-12 18:39:59.60 spid13s waiter-list
    2010-07-12 18:39:59.60 spid13s waiter id=process1934c8 mode=IS requestType=wait
    2010-07-12 18:39:59.60 spid13s objectlock lockPartition=0 objid=516248944 subresource=FULL dbid=7 objectname=fefirewall.dbo.RTRC id=lock100d9b780 mode=IS associatedObjectId=516248944
    2010-07-12 18:39:59.60 spid13s owner-list
    2010-07-12 18:39:59.60 spid13s owner id=process1934c8 mode=IS
    2010-07-12 18:39:59.60 spid13s waiter-list
    2010-07-12 18:39:59.60 spid13s waiter id=process179948 mode=Sch-M requestType=wait


    If you see, one query is just " select * from Table1" and other is re-creating a table, adding primary key and then adding a foreign key referencing "Table1".

    Not sure why "deadlock" occurs in this case !!

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The connection characteristics for SPID52 (SELECT) include isolation level READ_COMMITTED, which translates to first IS, which is Intent/Shared. This means that memory pages that are intended to be accessed will first acquire a latch. Those pages are already locked with an actual lock AND a controlling latch with Sch-M (schema modification) by SPID55. It would have been very natural to see SPID52 blocked by SPID55 for the duration of the latter's transaction, unless SPID52 "sneaked in" just a little earlier, and started placing its IS latches, when a higher priority operation issued by SPID55 "kicked" SPID52 into the pool of deadlock victims.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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