So I have two tables that create a deadlock. The application literally do nothing but update two different tables in two different transactions. There is a foreign key constraint (not cascading, just to enforce integrity) between these two tables, otherwise there is no relationship between the two in terms of how each transaction is happening. Can that be the whole source of the deadlock? And if so, how do you design foreign key constraints to avoid this issue?
UPDATE: I should point out (for those who land up on this question) that the underlying issue ended up not being about the foreign key constraint at all, however according to this it is possible for a foreign key constraint to cause deadlocks if the other side only has a clustered index for the primary key.
I also learned that the deadlock XML report doesn't capture the whole transaction up to that point (which my incorrect assumption that it did lead to the incorrect question), which is annoying.
Edit: The two tables are a table called KID and a table called Image (there is also ZAT_KID and ZAT_Image which are populated by a trigger as an audit trail, they reference nothing else). The Image's KIDID field is a foreign key for the KID's primary key (clustered index) of KIDID. I should also add that the initial select 1 at the beginning of each stack is the database pool ensuring the connection is still valid before it does anything else.
Here is the deadlock XML:
<deadlock-list>
<deadlock victim="processedb6d8">
<process-list>
<process id="processec49b8" taskpriority="0" logused="2672" waitresource="PAGE: 7:1:295182" waittime="2609" ownerId="2771483341" transactionname="implicit_transaction" lasttranstarted="2013-02-05T15:32:48.150" XDES="0x804da410" lockMode="X" schedulerid="2" kpid="10180" status="suspended" spid="93" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-02-05T15:32:56.980" lastbatchcompleted="2013-02-05T15:32:56.963" clientapp="Microsoft JDBC Driver for SQL Server" hostname="newappserver" hostpid="0" loginname="DatabaseUser" isolationlevel="read committed (2)" xactid="2771483341" currentdb="7" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128058">
<executionStack>
<frame procname="Database.dbo.T_Audit_KID" line="67" stmtstart="3936" stmtend="5062" sqlhandle="0x030007005776f42cc93b87015aa100000000000000000000">
INSERT INTO ZAT_KID with (PAGLOCK) (KIDID, KID, KosherStatusID, restrictionText,expirationDate,issuedDate,name,brand,void,rabbiSigner,agencyID,vendorID, ActivityDate, ActivityByID,Mode ,Extended,TempOwner,SecLevel,Source,Dataless,Overridden,UKDAgencyCode,UKDAgencyUniqueID)
VALUES ( @KIDID, @KID, @KosherStatusID, @restrictionText,@expirationDate,@issuedDate,@name,@brand,@void,@rabbiSigner,@agencyID,@vendorID, @ActivityDate, @ActivityByID,@Mode, @Extended,@TempOwner, @SecLevel,@Source,@Dataless,@Overridden,@UKDAgencyCode,@UKDAgencyUniqueID) </frame>
<frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x02000000285d0519fdc120b4e4bbeca4c48d20fa089a34b5">
UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
Select 1 </inputbuf>
</process>
<process id="processedb6d8" taskpriority="0" logused="1728" waitresource="PAGE: 7:1:295211" waittime="3781" ownerId="2771489653" transactionname="implicit_transaction" lasttranstarted="2013-02-05T15:32:55.683" XDES="0x2ce89f710" lockMode="X" schedulerid="3" kpid="9452" status="suspended" spid="64" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-02-05T15:32:55.807" lastbatchcompleted="2013-02-05T15:32:55.713" clientapp="Microsoft JDBC Driver for SQL Server" hostname="newappserver" hostpid="0" loginname="DatabaseUser" isolationlevel="read committed (2)" xactid="2771489653" currentdb="7" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128058">
<executionStack>
<frame procname="Database.dbo.T_Audit_Image" line="49" stmtstart="2178" stmtend="2612" sqlhandle="0x030007003c4d5a39f33b87015aa100000000000000000000">
INSERT INTO ZAT_Image with (PAGLOCK) (ImageID, KIDID, ExpirationDate, Exclude, ActivityByID, ActivityDate,Mode)
VALUES ( @ImageID, @KIDID,@ExpirationDate, @Exclude, @ActivityByID, @ActivityDate,@Mode) </frame>
<frame procname="adhoc" line="1" stmtstart="174" sqlhandle="0x020000006641f2279ebb048f51cb23b67af52ff7c3599d05">
insert into Image (ImageStoreKey, ActivityByID, ActivityDate, exclude, ExpirationDate, KIDID, ImageID) values (@P0, @P1, @P2, @P3, @P4, @P5, @P6) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
Select 1 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="295182" dbid="7" objectname="Database.dbo.ZAT_KID" id="lock16ec04700" mode="X" associatedObjectId="72057594481606656">
<owner-list>
<owner id="processedb6d8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processec49b8" mode="X" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="295211" dbid="7" objectname="Database.dbo.ZAT_Image" id="lock6e16cea00" mode="X" associatedObjectId="72057594441498624">
<owner-list>
<owner id="processec49b8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processedb6d8" mode="X" requestType="wait"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
Further Edit: Here are the two table schemas:
CREATE TABLE [dbo].[Image](
[ImageID] [int] NOT NULL,
[KIDID] [int] NULL,
[ExpirationDate] [datetime] NULL,
[Exclude] [nchar](1) NULL,
[ActivityDate] [datetime] NULL,
[ActivityByID] [int] NULL,
[ImageStoreKey] [nvarchar](255) NULL,
CONSTRAINT [PK_Image2] PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Image] WITH NOCHECK ADD CONSTRAINT [FK_Image_KID] FOREIGN KEY([KIDID])
REFERENCES [dbo].[KID] ([KIDID])
GO
ALTER TABLE [dbo].[Image] CHECK CONSTRAINT [FK_Image_KID]
GO
And the KID table:
CREATE TABLE [dbo].[KID](
[KIDID] [int] NOT NULL,
[KID] [nchar](7) NULL,
[KosherStatusID] [int] NULL,
[RestrictionText] [nvarchar](255) NULL,
[ExpirationDate] [datetime] NULL,
[IssuedDate] [datetime] NULL,
[Name] [nvarchar](255) NULL,
[Brand] [nvarchar](255) NULL,
[Void] [char](1) NULL,
[RabbiSigner] [nvarchar](255) NULL,
[Extended] [nchar](1) NULL,
[AgencyID] [int] NULL,
[VendorID] [int] NULL,
[PublicVisible] [nchar](1) NULL,
[TempOwner] [nvarchar](20) NULL,
[Source] [nvarchar](20) NULL,
[Dataless] [nvarchar](1) NULL,
[Overridden] [nchar](1) NULL,
[ActivityDate] [datetime] NULL,
[ActivityByID] [int] NULL,
[UKDAgencyCode] [nchar](12) NULL,
[UKDAgencyUniqueID] [nvarchar](50) NULL,
[SecLevel] [int] NOT NULL,
CONSTRAINT [PK_KID] PRIMARY KEY CLUSTERED
(
[KIDID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
The ZAT tables:
CREATE TABLE [dbo].[ZAT_KID](
[ZAT_KID_ID] [int] IDENTITY(1,1) NOT NULL,
[KIDID] [int] NOT NULL,
[KID] [nchar](7) NULL,
[KosherStatusID] [int] NULL,
[RestrictionText] [nvarchar](255) NULL,
[ExpirationDate] [datetime] NULL,
[IssuedDate] [datetime] NULL,
[Name] [nvarchar](255) NULL,
[Brand] [nvarchar](255) NULL,
[Void] [char](1) NULL,
[RabbiSigner] [nvarchar](255) NULL,
[Extended] [nchar](1) NULL,
[AgencyID] [int] NULL,
[VendorID] [int] NULL,
[PublicVisible] [nchar](1) NULL,
[TempOwner] [nvarchar](20) NULL,
[Source] [nvarchar](20) NULL,
[Dataless] [nvarchar](1) NULL,
[Overridden] [nchar](1) NULL,
[ActivityDate] [datetime] NULL,
[ActivityByID] [int] NULL,
[Mode] [nvarchar](20) NULL,
[UKDAgencyCode] [nchar](12) NULL,
[UKDAgencyUniqueID] [nvarchar](50) NULL,
[SecLevel] [int] NULL,
CONSTRAINT [PK_ZAT_KID] PRIMARY KEY CLUSTERED
(
[ZAT_KID_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ZAT_Image](
[ZAT_Image_ID] [int] IDENTITY(1,1) NOT NULL,
[ImageID] [int] NOT NULL,
[KIDID] [int] NULL,
[ActivityDate] [datetime] NULL,
[ActivityByID] [int] NULL,
[Mode] [nvarchar](20) NULL,
[ExpirationDate] [datetime] NULL,
[Exclude] [nchar](1) NULL,
CONSTRAINT [PK_ZAT_ImageID] PRIMARY KEY CLUSTERED
(
[ZAT_Image_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Best Answer
UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2triggersINSERT INTO ZAT_KID with (PAGLOCK)insert into Image ...triggersINSERT INTO ZAT_Image with (PAGLOCK)T1 wants X page 295182 (ZAT_KID) and has X page 295211 (ZAT_Image). T2 wants X page 295211 (ZAT_Image) and has X on 295182 (ZAT_KID). This indicates that previous activity of the two spids involved is at play here: T1 had previously locked a page on ZAT_Image, but current stack is not associated with that table. Similarly the T2 had previously locked a page on ZAT_Image but current stack is not associated with it.
The scenario is pretty clear: spurious use of poorly understood hints. T1 has locked the insert point of ZAT_Image while T2 has locked the insert point of ZAT_KID. A deadlock is unavoidable.
INSERT WITH (PAGLOCK)is the culprit. You are playing with fire and you got burned. Stop using hints you do not understand. Remove the PAGLOCK hint, let the engine decide the proper strategy. Leave the insert point free in your audit tables free for further inserts. Beat the person that added PAGLOCK to INSERT with a stick.