April 11, 2012 at 2:57 am
Hi All
We have the deadlock below happening a number of times a day.
I have read through the XML a number of times and cant pinpoint it down so just wanting a second pair of eyes.
Looking through the graph it seems to be around page 2932849 on both processes which ties back to index tblContentCategory_PK, but the update doesnt go near this table, the only thing is the FK which is on the content category table but thats not referencing any cascade updates
table defs, index defs, sql plans, deadlock graph all below.
thanks in advance
CREATE TABLE [dbo].[tblPublishedResource](
[ContentID] [int] NOT NULL,
[Title] [nvarchar](1024) NULL,
[MetaKeywords] [nvarchar](2048) NULL,
[MetaDescription] [nvarchar](2048) NULL,
[OneLineOverview] [nvarchar](2048) NULL,
[Teaser] [nvarchar](512) NULL,
[Introduction] [nvarchar](2048) NULL,
[Description] [nvarchar](max) NULL,
[Features] [nvarchar](max) NULL,
[Requirements] [nvarchar](max) NULL,
[Tab1Title] [nvarchar](100) NULL,
[Tab1Content] [nvarchar](max) NULL,
[Tab2Title] [nvarchar](100) NULL,
[Tab2Content] [nvarchar](max) NULL,
[UserID] [int] NULL,
[Acknowledgements] [nvarchar](max) NULL,
[LearningTime] [nvarchar](100) NULL,
[PublisherID] [int] NULL,
[LegacyID] [int] NULL,
[ImagePath] [nvarchar](512) NULL,
[CreatorNotes] [nvarchar](2048) NULL,
[ResourceType] [int] NULL,
[PublishedDate] [datetime] NULL,
[Filename] [nvarchar](256) NULL,
[ResourceUrl] [nvarchar](510) NULL,
[WeblinkUrl] [nvarchar](510) NULL,
[DownloadCount] [int] NOT NULL,
[ViewCount] [int] NOT NULL,
[IsActive] [bit] NOT NULL,
[Filesize] [int] NOT NULL,
[Reviewable] [bit] NOT NULL,
[PartnerID] [int] NULL,
[SubmittedSiteID] [int] NULL,
CONSTRAINT [tblPublishedResource_PK] PRIMARY KEY CLUSTERED
(
[ContentID] 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
CREATE NONCLUSTERED INDEX [idx_tblPublishedResource_UserID_ResourceType_PartnerID] ON [dbo].[tblPublishedResource]
(
[UserID] ASC,
[ResourceType] ASC,
[PartnerID] ASC
)
INCLUDE ( [ContentID],
[PublishedDate])
GO
CREATE NONCLUSTERED INDEX [IDX_tblPublishedResource_ResourceType_IsActive_INC_LotsOfColumns] ON [dbo].[tblPublishedResource]
(
[ResourceType] ASC,
[IsActive] ASC
)
INCLUDE ( [ContentID],
[Title],
[MetaKeywords],
[MetaDescription],
[OneLineOverview],
[Teaser],
[Introduction],
[Description],
[Features],
[Requirements],
[Tab1Title],
[Tab1Content],
[Tab2Title],
[Tab2Content],
[UserID],
[Acknowledgements],
[LearningTime],
[PublisherID],
[LegacyID],
[ImagePath],
[CreatorNotes],
[PublishedDate],
[Filename],
[ResourceUrl],
[WeblinkUrl],
[DownloadCount],
[ViewCount],
[Filesize],
[Reviewable],
[PartnerID])
GO
CREATE NONCLUSTERED INDEX [Idx_tblPublishedResource_IsActive] ON [dbo].[tblPublishedResource]
(
[IsActive] ASC
)
GO
CREATE TABLE [dbo].[tblContentCategory](
[ContentID] [int] NOT NULL,
[CategoryID] [int] NOT NULL,
CONSTRAINT [tblContentCategory_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC,
[ContentID] 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
ALTER TABLE [dbo].[tblContentCategory] WITH NOCHECK ADD CONSTRAINT [tblPublishedResource_tblContentCategory_FK1] FOREIGN KEY([ContentID])
REFERENCES [dbo].[tblPublishedResource] ([ContentID])
GO
ALTER TABLE [dbo].[tblContentCategory] NOCHECK CONSTRAINT [tblPublishedResource_tblContentCategory_FK1]
GO
<TextData><deadlock-list>
<deadlock victim="process4ba7288">
<process-list>
<process id="process4ba7288" taskpriority="0" logused="0" waitresource="PAGE: 5:1:2932849" waittime="3307" ownerId="10942713094" transactionname="SELECT" lasttranstarted="2012-04-11T08:37:52.353" XDES="0x1e43c76c0" lockMode="S" schedulerid="4" kpid="53144" status="suspended" spid="268" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-04-11T08:37:52.220" lastbatchcompleted="2012-04-11T08:37:52.220" clientapp="PlanetTasks Program" hostname="S607976CH4VW05" hostpid="4260" loginname="planet" isolationlevel="read committed (2)" xactid="10942713094" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x020000009a4a7c17089c62d384f231c0f69b4c9d2bf1d80b">
SELECT [t0].[ContentID], [t0].[Title], [t0].[MetaKeywords], [t0].[MetaDescription], [t0].[OneLineOverview], [t0].[Teaser], [t0].[Introduction], [t0].[Description], [t0].[Features], [t0].[Requirements], [t0].[Tab1Title], [t0].[Tab1Content], [t0].[Tab2Title], [t0].[Tab2Content], [t0].[UserID], [t0].[Acknowledgements], [t0].[LearningTime], [t0].[PublisherID], [t0].[LegacyID], [t0].[ImagePath], [t0].[CreatorNotes], [t0].[ResourceType], [t0].[PublishedDate], [t0].[Filename], [t0].[ResourceUrl], [t0].[WeblinkUrl], [t0].[DownloadCount], [t0].[ViewCount], [t0].[IsActive], [t0].[Filesize] FROM [dbo].[tblPublishedResource] AS [t0] INNER JOIN [dbo].[tblPublishedResourceFiles] AS [t1] ON [t0].[ContentID] = [t1].[ContentID] INNER JOIN [dbo].[tblContentCategory] AS [t2] ON [t0].[ContentID] = [t2].[ContentID]
WHERE ([t0].[Filesize] = @p0) AND ([t0].[IsActive] = 1) AND ([t2].[CategoryID] = @p1) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 int,@p1 int)SELECT [t0].[ContentID], [t0].[Title], [t0].[MetaKeywords], [t0].[MetaDescription], [t0].[OneLineOverview], [t0].[Teaser], [t0].[Introduction], [t0].[Description], [t0].[Features], [t0].[Requirements], [t0].[Tab1Title], [t0].[Tab1Content], [t0].[Tab2Title], [t0].[Tab2Content], [t0].[UserID], [t0].[Acknowledgements], [t0].[LearningTime], [t0].[PublisherID], [t0].[LegacyID], [t0].[ImagePath], [t0].[CreatorNotes], [t0].[ResourceType], [t0].[PublishedDate], [t0].[Filename], [t0].[ResourceUrl], [t0].[WeblinkUrl], [t0].[DownloadCount], [t0].[ViewCount], [t0].[IsActive], [t0].[Filesize] FROM [dbo].[tblPublishedResource] AS [t0] INNER JOIN [dbo].[tblPublishedResourceFiles] AS [t1] ON [t0].[ContentID] = [t1].[ContentID] INNER JOIN [dbo].[tblContentCategory] AS [t2] ON [t0].[ContentID] = [t2].[ContentID]
WHERE ([t0].[Filesize] = @p0) AND ([t0].[IsActive] = 1) AND ([t2].[CategoryID] = @p1) </inputbuf>
</process>
<process id="process452ebc8" taskpriority="0" logused="5740" waitresource="OBJECT: 5:1768393369:0 " waittime="3861" ownerId="10942713477" transactionname="implicit_transaction" lasttranstarted="2012-04-11T08:37:53.023" XDES="0x2ab98f970" lockMode="IX" schedulerid="7" kpid="2800" status="suspended" spid="82" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-11T08:37:53.100" lastbatchcompleted="2012-04-11T08:37:53.097" clientapp="jTDS" hostname="S607976CH4VW07" hostpid="123" loginname="planet" isolationlevel="read committed (2)" xactid="10942713477" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="954" sqlhandle="0x020000006caee7116a1df95b9c62c32160f39554e1a4d48a">
UPDATE dbo.tblPublishedResource SET OneLineOverview= @P0 , PublishedDate= @P1 , UserID= @P2 , Filename= @P3 , MetaKeywords= @P4 , SubmittedSiteID= @P5 , LearningTime= @P6 , ResourceType= @P7 , Introduction= @P8 , IsActive= @P9 , LegacyID= @P10 , CreatorNotes= @P11 , WeblinkUrl= @P12 , PartnerID= @P13 , PublisherID= @P14 , Description= @P15 , Teaser= @P16 , Requirements= @P17 , Tab1Title= @P18 , MetaDescription= @P19 , Title= @P20 , Features= @P21 , Acknowledgements= @P22 , Tab1Content= @P23 , Tab2Title= @P24 , ResourceUrl= @P25 , ImagePath= @P26 , Tab2Content= @P27 WHERE ContentID= @P28 </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 datetime,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 int,@P6 nvarchar(4000),@P7 int,@P8 nvarchar(4000),@P9 bit,@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 int,@P14 int,@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 nvarchar(4000),@P21 nvarchar(4000),@P22 nvarchar(4000),@P23 nvarchar(4000),@P24 nvarchar(4000),@P25 nvarchar(4000),@P26 nvarchar(4000),@P27 nvarchar(4000),@P28 int)UPDATE dbo.tblPublishedResource SET OneLineOverview= @P0 , PublishedDate= @P1 , UserID= @P2 , Filename= @P3 , MetaKeywords= @P4 , SubmittedSiteID= @P5 , LearningTime= @P6 , ResourceType= @P7 , Introduction= @P8 , IsActive= @P9 , LegacyID= @P10 , CreatorNotes= @P11 , WeblinkUrl= @P12 , PartnerID= @P13 , PublisherID= @P14 , Description= @P15 , Teaser= @P16 , Requirements= @P17 , Tab1Title= @P18 , MetaDescription= @P19 , Title= @P20 , Features= @P21 , Acknowledgements= @P22 , Tab1Content= @P23 , Tab2Title= @P24 , ResourceUrl= @P25 , ImagePath= </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="2932849" dbid="5" objectname="PrometheanPlanet.dbo.tblContentCategory" id="lock810cd000" mode="IX" associatedObjectId="72057594309246976">
<owner-list>
<owner id="process452ebc8" mode="IX"></owner>
</owner-list>
<waiter-list>
<waiter id="process4ba7288" mode="S" requestType="wait"></waiter>
</waiter-list>
</pagelock>
<objectlock lockPartition="0" objid="1768393369" subresource="FULL" dbid="5" objectname="PrometheanPlanet.dbo.tblPublishedResource" id="lock109b81200" mode="S" associatedObjectId="1768393369">
<owner-list>
<owner id="process4ba7288" mode="S"></owner>
</owner-list>
<waiter-list>
<waiter id="process452ebc8" mode="IX" requestType="wait"></waiter>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
April 11, 2012 at 4:21 am
the second script bombs as there is a table missing.
Have you considered implementing a more suitable isolation level?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 4:30 am
Hi Perry
The second script will fail as its referencing other tables which are not in the deadlock graph so I didnt include them, thanks for pointing that out, I will modify the post.
The app is all written in entity framework so would mean a change to the connection strings to change the isolation level as its not procedure based. But staying on topic what would you recommend as the isolation level
April 11, 2012 at 4:56 am
There are a couple of options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. Both acquire no page or row locks when reading, check BOL for more info on these.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 6:06 am
Thanks Perry, I was thinking of isolation levels at the connection level not at the database level. Read committed snapshot seems like the one to go down, just the initial change will need to be done in our maintenance window due to the limitations of setting this while there are active connections on going.
April 11, 2012 at 6:16 am
anthony.green (4/11/2012)
I was thinking of isolation levels at the connection level not at the database level.
You have to enable them at the database level to use them in the query.
For example
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Select mycolumn from biguglytable
Will only be possible once you use
ALTER DATABASE biguglyDWdb SET ALLOW_SNAPSHOT_ISOLATION ON
Keep an eye on TempDB too 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 6:21 am
yeah especially with the row version store, had issues with that in the past on a dynamics AX implimentation which as per best practise says to set the DB into allow_snapshot_isolation, and as AX uses connection pooling the SPID's didnt terminate leaving the active rows in the row store which was a pain.
I was thinking of using read_committed_snapshot due to this from BOL
In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
it would mean that no changes to the app is required as its using read committed as default
April 11, 2012 at 6:27 am
sure, you just need to enable the database option
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 30, 2012 at 2:49 am
following on from this, we had our quarterly maintenance window this weekend and made this change.
so far so good, no deadlocks have been reported so far.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply