Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deadlock at primarykey column Expand / Collapse
Author
Message
Posted Monday, January 17, 2011 11:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, 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?

Post #1049077
Posted Tuesday, January 18, 2011 1:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1049116
Posted Tuesday, January 18, 2011 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1049131
Posted Tuesday, January 18, 2011 2:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, 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>

Post #1049144
Posted Tuesday, January 18, 2011 3:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
Table and index definitions please (crate table, create index)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1049156
Posted Tuesday, January 18, 2011 4:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, 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

Post #1049189
Posted Tuesday, January 18, 2011 4:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
Consider a nonclustered index on (JobCardID, StageID, SubJobID, IsActive, CompletedDate, QueueID)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1049209
Posted Tuesday, January 18, 2011 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852
that table contains 50 rows around 1.5mb.
we need better solution.
Post #1049279
Posted Tuesday, January 18, 2011 6:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1049281
Posted Tuesday, January 18, 2011 6:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 2,667, Visits: 5,907
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



Post #1049284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse