April 23, 2009 at 4:08 am
Am stuck can you help. We have ~40 writer threads all inserting rows into a table, and a reader thread that basically polls (50ms interval) to see if the rows it cares about have been added yet. And that's it - just simultaneous one-row INSERTs and SELECT DISTINCT ... WHERE Quadkey IN ()s. No triggers, no SPs. But we often see SQL deadlocks - have captured trace, below. The table has a BLOB, but otherwise looks very plain:
CREATE TABLE VETile (
VETileID INT IDENTITY(1,1) NOT NULL,
Quadkey VARCHAR(256) NULL,
TileBA IMAGE NULL,
Created DATETIME NULL
DataLength BIGINT NULL,
PRIMARY KEY CLUSTERED
(
VETileID ASC
) )
Can anyone advise on where we might possibly be going wrong? From what I dimly remember from school about CRUD the INSERTs and SELECTs should effectively be working in serial, so I don't see how/why this could deadlock, and yet.
Any advice/pointers much appreciated.
Cheers
Bunt
---
2009-04-17 16:46:54.86 spid4s ----------------------------------
2009-04-17 16:46:54.86 spid4s Starting deadlock search 69109
2009-04-17 16:46:54.86 spid4s Target Resource Owner:
2009-04-17 16:46:54.86 spid4s ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0
2009-04-17 16:46:54.86 spid4s 0:Insert new node: Node:1 ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0
2009-04-17 16:46:54.86 spid4s 1:SearchOR Considering new blocker - task: 00AF05C8, Worker 3B2620E8
2009-04-17 16:46:54.86 spid4s 2:Insert new node: Node:2 ResType:LockOwner Stype:'OR'Xdes:0x36AF28D8 Mode: S SPID:110 BatchID:0 ECID:0 TaskProxy:(0x36B14374) Value:0xcf3d860
2009-04-17 16:46:54.86 spid4s 3:SearchOR Considering new blocker - task: 006E86B8, Worker 16DBC0E8
2009-04-17 16:46:54.86 spid4s 4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0]
2009-04-17 16:46:54.87 spid4s 4:InsertKnown search result: Deadlock found (blocking owner is on a stack)
2009-04-17 16:46:54.87 spid4s 3:SearchOR search result: Deadlock found (cycle on this level or before)
2009-04-17 16:46:54.87 spid4s 1:SearchOR search result: Deadlock found (cycle on this level or before)
2009-04-17 16:46:54.87 spid4s
2009-04-17 16:46:54.87 spid4s Deadlock cycle was encountered .... verifying cycle
2009-04-17 16:46:54.87 spid4s 0:Insert new node: Node:1 ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0 Cost:(0/2472)
2009-04-17 16:46:54.87 spid4s 1:SearchOR Considering new blocker - task: 00AF05C8, Worker 3B2620E8
2009-04-17 16:46:54.87 spid4s 2:Insert new node: Node:2 ResType:LockOwner Stype:'OR'Xdes:0x36AF28D8 Mode: S SPID:110 BatchID:0 ECID:0 TaskProxy:(0x36B14374) Value:0xcf3d860 Cost:(0/0)
2009-04-17 16:46:54.87 spid4s 3:SearchOR Considering new blocker - task: 006E86B8, Worker 16DBC0E8
2009-04-17 16:46:54.87 spid4s 4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0 Cost:(0/2472)] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0 Cost:(0/2472)]
2009-04-17 16:46:54.87 spid4s 4:InsertKnown search result: Deadlock found (blocking owner is on a stack)
2009-04-17 16:46:54.87 spid4s 3:SearchOR search result: Deadlock found (cycle on this level or before)
2009-04-17 16:46:54.87 spid4s 1:SearchOR search result: Deadlock found (cycle on this level or before)
2009-04-17 16:46:54.87 spid4s Deadlock encountered .... Printing deadlock information
2009-04-17 16:46:54.87 spid4s Wait-for graph
2009-04-17 16:46:54.87 spid4s
2009-04-17 16:46:54.87 spid4s Node:1
2009-04-17 16:46:54.89 spid4s PAGE: 6:1:1230142 CleanCnt:3 Mode:S Flags: 0x2
2009-04-17 16:46:54.89 spid4s Grant List 1:
2009-04-17 16:46:54.89 spid4s Owner:0x0CF4B8C0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:110 ECID:0 XactLockInfo: 0x36AF28FC
2009-04-17 16:46:54.90 spid4s SPID: 110 ECID: 0 Statement Type: SELECT Line #: 1
2009-04-17 16:46:54.90 spid4s Input Buf: Language Event: SELECT PTAB_PREF_VETile.VETileID AS 'PTAB_PREF_VETile_VETileID',
PTAB_PREF_VETile.TileBA AS 'PTAB_PREF_VETile_TileBA',
PTAB_PREF_VETile.Created AS 'PTAB_PREF_VETile_Created',
PTAB_PREF_VETile.Quadkey AS 'PTAB_PREF_VETile_Quadkey'
FROM [VETile] PTAB_
2009-04-17 16:46:54.90 spid4s Requested By:
2009-04-17 16:46:54.90 spid4s ResType:LockOwner Stype:'OR'Xdes:0x5D370450 Mode: IX SPID:114 BatchID:0 ECID:0 TaskProxy:(0x5D47C374) Value:0xf00e2c0 Cost:(0/2472)
2009-04-17 16:46:54.90 spid4s
2009-04-17 16:46:54.90 spid4s Node:2
2009-04-17 16:46:54.90 spid4s PAGE: 6:1:1239903 CleanCnt:2 Mode:IX Flags: 0x2
2009-04-17 16:46:54.90 spid4s Grant List 0:
2009-04-17 16:46:54.90 spid4s Owner:0x411B6600 Mode: IX Flg:0x0 Ref:0 Life:04000000 SPID:114 ECID:0 XactLockInfo: 0x5D370474
2009-04-17 16:46:54.90 spid4s SPID: 114 ECID: 0 Statement Type: INSERT Line #: 1
2009-04-17 16:46:54.90 spid4s Input Buf: Language Event: (@p0 varbinary(1709))INSERT INTO [VETile] (TileBA,Created,Quadkey) VALUES(@p0,'04/17/2009 4:46:51 PM',N'023010220103')
2009-04-17 16:46:54.90 spid4s Requested By:
2009-04-17 16:46:54.90 spid4s ResType:LockOwner Stype:'OR'Xdes:0x36AF28D8 Mode: S SPID:110 BatchID:0 ECID:0 TaskProxy:(0x36B14374) Value:0xcf3d860 Cost:(0/0)
2009-04-17 16:46:54.90 spid4s
2009-04-17 16:46:54.90 spid4s Victim Resource Owner:
2009-04-17 16:46:54.90 spid4s ResType:LockOwner Stype:'OR'Xdes:0x36AF28D8 Mode: S SPID:110 BatchID:0 ECID:0 TaskProxy:(0x36B14374) Value:0xcf3d860 Cost:(0/0)
2009-04-17 16:46:54.90 spid4s End deadlock search 69109 ... a deadlock was found.
2009-04-17 16:46:54.90 spid4s ----------------------------------
April 23, 2009 at 5:02 am
brentww (4/23/2009)
Am stuck can you help. We have ~40 writer threads all inserting rows into a table, and a reader thread that basically polls (50ms interval) to see if the rows it cares about have been added yet. And that's it - just simultaneous one-row INSERTs and SELECT DISTINCT ... WHERE Quadkey IN ()s
.
CREATE TABLE VETile (
VETileID INT IDENTITY(1,1) NOT NULL,
Quadkey VARCHAR(256) NULL,
TileBA IMAGE NULL,
Created DATETIME NULL
DataLength BIGINT NULL,
PRIMARY KEY CLUSTERED
(
VETileID ASC
) )
What is the size of the table? Based on the select query mentioned above, it is fetching data based on Quadkey. Is index defined on this column? When you exeucte the same query and see the plan what exactly it is showing, is it doing a table scan or doing index seek?
April 23, 2009 at 7:48 am
Can you run a trace and include the deadlock graph event? Then attach the trace file to the thread (zip and attach).
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 8:35 am
> What is the size of the table? Based on the select query mentioned above, it is fetching data based
> on Quadkey. Is index defined on this column? When you exeucte the same query and see the plan
> what exactly it is showing, is it doing a table scan or doing index seek?
Table 15K rows and grows few thousand per day, will cap it around 100K. CREATE TABLE is as shown, so no index on quadkey (obviously it should have one, but deadlocks are what's really bugging me, not performance). Here's a dummy representative query:
select distinct quadkey from vetile where quadkey in ('3303', '1203','11203','111203','1222203','12303',
'a','b','c','d','e','f','g','h','i','j','jk','aa','bb','cc','cc','dd','ee','ff')
and here's the query plan:
|--Sort(DISTINCT ORDER BY:([AxisQAS].[dbo].[VETile].[Quadkey] ASC))
|--Filter(WHERE:([AxisQAS].[dbo].[VETile].[Quadkey]='111203' OR [AxisQAS].[dbo].[VETile].[Quadkey]='11203' OR [AxisQAS].[dbo].[VETile].[Quadkey]='1203' OR [AxisQAS].[dbo].[VETile].[Quadkey]='1222203' OR [AxisQAS].[dbo].[VETile].[Quadkey]='12303' OR [AxisQAS].[dbo].[VETile].[Quadkey]='3303' OR [AxisQAS].[dbo].[VETile].[Quadkey]='a' OR [AxisQAS].[dbo].[VETile].[Quadkey]='aa' OR [AxisQAS].[dbo].[VETile].[Quadkey]='b' OR [AxisQAS].[dbo].[VETile].[Quadkey]='bb' OR [AxisQAS].[dbo].[VETile].[Quadkey]='c' OR [AxisQAS].[dbo].[VETile].[Quadkey]='cc' OR [AxisQAS].[dbo].[VETile].[Quadkey]='d' OR [AxisQAS].[dbo].[VETile].[Quadkey]='dd' OR [AxisQAS].[dbo].[VETile].[Quadkey]='e' OR [AxisQAS].[dbo].[VETile].[Quadkey]='ee' OR [AxisQAS].[dbo].[VETile].[Quadkey]='f' OR [AxisQAS].[dbo].[VETile].[Quadkey]='ff' OR [AxisQAS].[dbo].[VETile].[Quadkey]='g' OR [AxisQAS].[dbo].[VETile].[Quadkey]='h' OR [AxisQAS].[dbo].[VETile].[Quadkey]='i' OR [AxisQAS].[dbo].[VETile].[Quadkey]='j' OR [AxisQAS].[dbo].[VETile].[Quadkey]='jk'))
|--Clustered Index Scan(OBJECT:([AxisQAS].[dbo].[VETile].[PK__VETile__1998EE04]))
This I'm guessing means it's doing a table scan, which is what I would expect. But with or without index, how can simple INSERT and SELECTs on an ordinary table possibly deadlock?
- Bunt
April 23, 2009 at 9:00 am
I think this blog post probably gets to the heart of your issue. It isn't exactly the issue you are seeing, but the clustered index scan is definitely causing an issue.
Have you looked at sys.dm_tran_locks to see what types of locks are being taken?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 3:11 am
I checked out article, the deadlock there was between two indices and I've only got one. I couldn't actually try the covering index they suggested (though that seems like a good idea) as I've got an IMAGE column (which can't be covered). Regardless, I did find a coding error whereby my SELECT was more inclusive than it should have been, and correcting this alone seems to have eliminated the deadlock. Still a little fuzzy on why it was failing even with the old select, but as it seems to be working now I'll leave it be.
Thanks all for the advice/suggestions.
Cheers
Bunt
Jack Corbett (4/23/2009)
I think this blog post probably gets to the heart of your issue. It isn't exactly the issue you are seeing, but the clustered index scan is definitely causing an issue.Have you looked at sys.dm_tran_locks to see what types of locks are being taken?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply