Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock at primarykey column


Deadlock at primarykey column

Author
Message
k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
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?
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5709 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
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


k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
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>
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
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


k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
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


k.thanigaivel
k.thanigaivel
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 852
that table contains 50 rows around 1.5mb.
we need better solution.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392
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


Mike John
Mike John
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2674 Visits: 5958
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search