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]
UPDATE
statements. Is there a 3rd table involved that is common to the two tables being updated?ZAT_x
tables?