Deadlock at primarykey column

Question

Deadlock at primarykey column

k.thanigaivel

Ten Centuries

Points: 1247
More actions
January 17, 2011 at 11:47 pm

#388653

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?
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]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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 Ten Centuries Points: 1247 More actions · Answer 3

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>

<process-list>

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>

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

</process>

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>

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

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 Ten Centuries Points: 1247 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 Ten Centuries Points: 1247 More actions · Answer 7

that table contains 50 rows around 1.5mb.

we need better solution.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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 SSCertifiable Points: 7216 More actions · Answer 9

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

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

LutzM SSC Guru Points: 107049 More actions · Answer 11

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]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

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);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

k.thanigaivel Ten Centuries Points: 1247 More actions · Answer 14

k.thanigaivel

Ten Centuries

Points: 1247

January 18, 2011 at 8:54 pm

#1275080

sorroy that table contains 50 thousand rows.