4

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]
Improve this question
5
  • 2
    Unindexed foreign keys can cause deadlocks (at least in Oracle - my sql server knowledge is poo) - maybe that's an avenue to explore?
    – Philᵀᴹ
    Feb 6, 2013 at 0:14
  • 3
    Can you capture the deadlock data and post it? That will be the true testament of what exactly happened. See the bottom of this reference for ways to get this information: msdn.microsoft.com/en-us/library/ms178104(v=SQL.105).aspx
    – Thomas Stringer
    Feb 6, 2013 at 0:53
  • 3
    Please post the table schema and the two UPDATE statements. Is there a 3rd table involved that is common to the two tables being updated?
    – Jon Seigel
    Feb 6, 2013 at 4:04
  • Is it possible for you to post the definitions of the two ZAT_x tables?
    – Thomas Stringer
    Feb 6, 2013 at 15:34
  • @ThomasStringer, all added.
    – Yishai
    Feb 6, 2013 at 15:41

1 Answer 1

Reset to default
7
  • T1 SPID 93 processec49b8: UPDATE kid SET activityByID=@P0, activityDate=@P1 WHERE kidID=@P2 triggers INSERT INTO ZAT_KID with (PAGLOCK)
  • T2 SPID 64 processedb6d8: insert into Image ... triggers INSERT 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.

Improve this answer
2
  • 1
    To be clear: INSERT WITH (PAGLOCK) on a clustered identity, as I suspected your case is, is in effect a statement 'I have inserted a row and no other transaction can insert another row until I commit'. I have never seen this hint used with INSERT, and for good reason.
    – Remus Rusanu
    Feb 6, 2013 at 16:00
  • that person left the company a long time ago. Thanks for the help!
    – Yishai
    Feb 6, 2013 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.