Deadlock at primarykey column

  • Hi,

    I am facing deadlock in update query. i found the reason is Clustered index scan takes more time.

    the query likes below example.

    update tbl1 set b=10 where c=x and d=y

    here a,b,c,d are columns and a is the primary key of tbl1.

    any solution to reduce clustered index scan without introducing non-clustered index?

  • kthanigaivel (1/17/2011)


    Hi,

    I am facing deadlock in update query. i found the reason is Clustered index scan takes more time.

    the query likes below example.

    update tbl1 set b=10 where c=x and d=y

    here a,b,c,d are columns and a is the primary key of tbl1.

    any solution to reduce clustered index scan without introducing non-clustered index?

    Since A isn't listed in your WHERE clause anywhere, it's forced to scan.

    If C/D is the most common way into tbl1, you should set your primary key to non-clustered, and make C/D your clustered index.

    Without real ddl and evaluation of the actual database though, I can't tell you if this is your best idea or not. It would fix *this* query. I have no idea if it will foul the rest of your optimization.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • kthanigaivel (1/17/2011)


    any solution to reduce clustered index scan without introducing non-clustered index?

    No. Why don't you want a non-clustered index?

    Turn traceflag 1222 on. That will write a deadlock graph into the error log when a deadlock is encountered. Post the graph here along with the definition of the table and the indexes on it.

    DBCC TRACEON(1222,-1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the quick replay

    Update Trn_GJobDet set CompletedDate=getdate() , CompletedEmpId=4335

    Where QueueId < 2 and JobCardId=116111 and StageId=53 and

    SubJobId=971218 and CompletedDate is Null and IsActive=1

    this is the actual query.Gjobid is the primary key.

    and the deadlock graph is as follows.

    <deadlock-list>

    <deadlock victim="processfef288">

    <process-list>

    <process id="processfef288" taskpriority="0" logused="276" waitresource="KEY: 7:72057594542686208 (dc00100960d3)" waittime="2072" ownerId="19382043303" transactionname="user_transaction" lasttranstarted="2010-12-10T19:40:10.057" XDES="0xbb2683b0" lockMode="U" schedulerid="3" kpid="10768" status="suspended" spid="799" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-12-10T19:40:10.077" lastbatchcompleted="2010-12-10T19:40:10.057" clientapp="iTracksWeb" hostname="INTEGRA-NET" hostpid="24068" loginname="itracksuser" isolationlevel="read uncommitted (1)" xactid="19382043303" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    <frame procname="Itracks.dbo.GLJSInsert" line="86" stmtstart="6146" stmtend="6638" sqlhandle="0x030007009c2aed23fddcd000489e00000100000000000000">

    Update Trn_GJobDet set CompletedDate=getdate() , CompletedEmpId=@CreatedEmpId

    Where QueueId < @InsertQId and JobCardId=@JobCardId and StageId=@StageId and

    SubJobId=@SubJobId and CompletedDate is Null and IsActive=1 </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 7 Object Id = 602745500] </inputbuf>

    </process>

    <process id="process1e8772bc8" taskpriority="0" logused="1924" waitresource="KEY: 7:72057594542686208 (db00a931b74e)" waittime="2064" ownerId="19382043526" transactionname="user_transaction" lasttranstarted="2010-12-10T19:40:10.227" XDES="0xe77df970" lockMode="U" schedulerid="4" kpid="6976" status="suspended" spid="154" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-12-10T19:40:10.250" lastbatchcompleted="2010-12-10T19:40:10.247" clientapp="iTracksWeb" hostname="INTEGRA-NET" hostpid="24068" loginname="itracksuser" isolationlevel="read uncommitted (1)" xactid="19382043526" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    <frame procname="Itracks.dbo.GLJSInsert" line="80" stmtstart="5524" stmtend="6054" sqlhandle="0x030007009c2aed23fddcd000489e00000100000000000000">

    Update Trn_GJobDet set CompletedDate=getdate() , CompletedEmpId=@CreatedEmpId

    Where QueueId = @RejectedQueueId + 1 and JobCardId=@JobCardId and

    StageId=@StageId and SubJobId=@SubJobId and CompletedDate is Null and IsActive=1 </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 7 Object Id = 602745500] </inputbuf>

    </process>

    </process-list>

    <resource-list>

    <keylock hobtid="72057594542686208" dbid="7" objectname="Itracks.dbo.Trn_GJobDet" indexname="PK_trn_GJobDet1" id="locked3c8d00" mode="X" associatedObjectId="72057594542686208">

    <owner-list>

    <owner id="process1e8772bc8" mode="X"/>

    </owner-list>

    <waiter-list>

    <waiter id="processfef288" mode="U" requestType="wait"/>

    </waiter-list>

    </keylock>

    <keylock hobtid="72057594542686208" dbid="7" objectname="Itracks.dbo.Trn_GJobDet" indexname="PK_trn_GJobDet1" id="lockc57e9080" mode="X" associatedObjectId="72057594542686208">

    <owner-list>

    <owner id="processfef288" mode="X"/>

    </owner-list>

    <waiter-list>

    <waiter id="process1e8772bc8" mode="U" requestType="wait"/>

    </waiter-list>

    </keylock>

    </resource-list>

    </deadlock>

    </deadlock-list>

  • Table and index definitions please (crate table, create index)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • we have one default clustered index PK_trn_GJobDet1 alone and the table definition is below

    CREATE TABLE [dbo].[Trn_GJobDet](

    [GJobId] [int] IDENTITY(1,1) NOT NULL,

    [JobCardId] [int] NOT NULL,

    [StageId] [smallint] NOT NULL,

    [SubJobId] [int] NOT NULL,

    [ProcessId] [smallint] NOT NULL,

    [QueueId] [tinyint] NOT NULL,

    [CreatedDate] [datetime] NOT NULL,

    [StartedDate] [datetime] NULL,

    [CompletedDate] [datetime] NULL,

    [CreatedEmpId] [smallint] NOT NULL,

    [StartedEmpId] [smallint] NULL,

    [CompletedEmpId] [smallint] NULL,

    [IsActive] [bit] NULL,

    CONSTRAINT [PK_trn_GJobDet1] PRIMARY KEY CLUSTERED

    (

    [GJobId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • Consider a nonclustered index on (JobCardID, StageID, SubJobID, IsActive, CompletedDate, QueueID)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • that table contains 50 rows around 1.5mb.

    we need better solution.

  • Um, nonclustered indexes ARE the better solution. Unless you like all queries against your tiny table (50 rows? Seriously?) to execute with a table scan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Do you really mean 50 rows and 1.5Mb? That seems so small as to make the cost of adding an index trivial.

    In what way do you mean "better" I expect the Index Gail has suggested will support the query you have well and stop the table scan, and therefore stop the deadlock.

    You could look at forcing an exclusive table level lock on the update I suppose (see tablockx query hints) since if you force it to be that lock I do not believe you will see a deadlock - but it will stop anybody else touching the table while this update runs. Somewhat brute force really, and not a nice idea normally.

    What is wrong with adding an index or two?

    Mike

  • kthanigaivel (1/18/2011)


    that table contains 50 rows around 1.5mb.

    we need better solution.

    50 Rows? At 1.5 MB? That's... (counts off on his fingers, removes a shoe...) 30k per row?

    That's LOB data, which runs by different rules for the majority of the LOB stuff, but not the direct information.

    The index that Gail gave you *is* the better solution, unless you want to take your system apart to find out the perfect one.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • GilaMonster (1/18/2011)


    Consider a nonclustered index on (JobCardID, StageID, SubJobID, IsActive, CompletedDate, QueueID)

    Gail, is there any specific reason to use all columns in the index instead of moving some to the include section? E.g. nonclustered index on (JobCardID, StageID, SubJobID) INCLUDE(IsActive, CompletedDate, QueueID). Ok, it's just 9 byte + 1 bit, so the influence to the index storage size most probably is not that significant.

    It's just a question if you'd decide against it...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I have a question or two...

    1) How many rows does that UPDATE query affect? Is it updating 1 row or more?

    2) Is there some reason why the application that is requesting the update cannot know the [GJobId] (s) that it needs to update?

    I ask this because it smells like you have a queue of jobs waiting to be completed which may be presented to the users for them to select the ones they have completed.

    If this is the case, could you not include the PK [GJobId] column in the select that presents them with their work queue and then use it for the updates?

    If I have guessed wrong, feel free to throw something at me...

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • LutzM (1/18/2011)


    Gail, is there any specific reason to use all columns in the index instead of moving some to the include section?

    Yes. They're all in the where clause. Hence they're all needed as seek predicates for the index seek, hence they're all key columns. Remember include columns can't be used in index seeks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sorroy that table contains 50 thousand rows.

  • Viewing 15 posts - 1 through 15 (of 16 total)

    You must be logged in to reply to this topic. Login to reply