I'm having a deadlock issue with a SQL Express database and a stored procedure call that ends up adding rows to three different tables, with the following basic heirarchy:
[Top]
|
+--[Mid]
|
+[End]
This was originally setup with auto-generated keys at each level, and the psuedo code for the stored procedure that's being called is basically:
begin transaction
insert into TopTable values from openXML
select @.topID = scope_identity()
insert into MidTable values from openXML and @.topID
insert into EndTable values from openXML and (select ID from MidTable that was just inserted)
commit transaction
Using the profiler there are a few places where the deadlocks occur between the 2nd and 3rd insert, it's always an index lock on the TopTable primary key or the MidTable primary key. The deadlock even occurs if the 3rd insert is taken out. We've tried changing from using autogenerated values to using natural keys, but have a very similar deadlock. Because time was short, we put an application lock in for the production code - it fixes it but testing shows it won't scale very well. The only two other things we've been able to get to work is putting a table lock on each table, or by using natural keys and relaxing the foreign key constraints. The table lock is a no-go because an SSIS package needs to read from the data periodically, and relaxing of the foreign key constraints has it's own problems - though that is where we're currently leaning if we can't come up with some other solution.
My collegues and I have been searching out the issue and working on the problem off and on for the last several days, but we're still a bit stuck. Is there something we've missed? Any pointers to a possible solution?
Following is the deadlock graph data from one of the deadlocks:
Deadlock graph 1379 1 sa 0X01 19 Z014719 2007-03-20 10:10:20.527 <deadlock-list>
<deadlock victim="process5b9c48">
<process-list>
<process id="process5b8c58" taskpriority="0" logused="1268" waitresource="KEY: 7:72057594039304192 (f60073ab7d7b)" waittime="4062" ownerId="48709" transactionname="user_transaction" lasttranstarted="2007-03-20T10:10:16.400" XDES="0x5b8d190" lockMode="S" schedulerid="1" kpid="3640" status="suspended" spid="56" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-03-20T10:10:16.370" lastbatchcompleted="2007-03-20T10:10:16.370" clientapp=".Net SqlClient Data Provider" hostname="Z014719" hostpid="3040" loginname="HTCH\CON000307" isolationlevel="read committed (2)" xactid="48709" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="DeadlockTest.dbo.spWriteTable" line="59" stmtstart="2758" stmtend="4272" sqlhandle="0x030007003ec4c60b2a379700f79800000100000000000000">
insert MidTable (
TopID,
[Name],
[Value])
select
TopID = @.TopTableId,
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@.hdoc,'/data/top/mid', 1)
WITH ([Name] VARCHAR(50), [Value] int)r</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 197575742] </inputbuf>
</process>
<process id="process5b9c48" taskpriority="0" logused="624" waitresource="KEY: 7:72057594039304192 (f5009d04c869)" waittime="4062" ownerId="48726" transactionname="user_transaction" lasttranstarted="2007-03-20T10:10:16.400" XDES="0x5bd8860" lockMode="S" schedulerid="1" kpid="3600" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-03-20T10:10:16.370" lastbatchcompleted="2007-03-20T10:10:16.370" clientapp=".Net SqlClient Data Provider" hostname="Z014719" hostpid="3040" loginname="HTCH\CON000307" isolationlevel="read committed (2)" xactid="48726" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="DeadlockTest.dbo.spWriteTable" line="59" stmtstart="2758" stmtend="4272" sqlhandle="0x030007003ec4c60b2a379700f79800000100000000000000">
insert MidTable (
TopID,
[Name],
[Value])
select
TopID = @.TopTableId,
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@.hdoc,'/data/top/mid', 1)
WITH ([Name] VARCHAR(50), [Value] int)r</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 197575742] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594039304192" dbid="7" objectname="DeadlockTest.dbo.TopTable" indexname="PK_TopTable" id="lock35dc680" mode="X" associatedObjectId="72057594039304192">
<owner-list>
<owner id="process5b8c58" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5b9c48" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594039304192" dbid="7" objectname="DeadlockTest.dbo.TopTable" indexname="PK_TopTable" id="lock35dcb00" mode="X" associatedObjectId="72057594039304192">
<owner-list>
<owner id="process5b9c48" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5b8c58" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Have you considered using nested transaction and committing each insert individually?
declare @.topid int
create table test1..t1(t1c1 int identity, c2 datetime)
create table test1..t2(t2c1 int identity, t1c1 int, c2 datetime)
create table test1..t3(t3c1 int identity, t2c1 int, c2 datetime)
begin tran
begin tran
insert t1(c2)
select getdate()
commit tran
select @.topID = scope_identity()
begin tran
insert t2(t1c1,c2)
select @.topID,getdate()
commit tran
select @.topID = scope_identity()
begin tran
insert t3(t2c1,c2)
select @.topID,getdate() -- or select max(t2c1), getdate() from t2
commit tran
commit tran
|||
I hadn't thought about that - I'll try that out a little later this afternoon. I'm a bit worried about how it will perform, but if it works I'm sure it will be better than using the application lock.
I'll post back with what I find out.
|||
I was excited by it, so I thought I'd try out out right away.
Unfortunately it didn't work, the same issue is happening. What it looks like is the deadlock is actually occuring when looking up the key for the foreign key relationships - so any insert will force a lookup into the other table for the constraint imposed by the foreign key, so it doesn't matter if there are nested transactions.
|||
Whilst obfuscation and preserving of private information is very good, I don't think we can help without more information.
We need to know basic table structures (mainly primary key, identity, and foreign key constraints) and the SQL that is causing the problem. The best thing would be if you could create a repro set that was table (and constraint) DDL, population code (if needed - it may happen on empty tables) and a SQL procedure or batch that causes the lockup.
They don't have to be the production information. Simplified tables (the key columns and possibly one data column) and dummy data is good enough. But without seeing the SQL, and if possible reproducing the problem, it is difficult to give more than general advice.
|||
That was actually one of the first things I did, and the table names I specified above are from the dummy tables (TopTable, MidTable and EndTable are the actual table names - of my dummy database anyway). The below SQL script creates a dummy database with tables that have a similar structure and indexes as the actual production database, as well as a sproc that is similar to the actual sproc causing the error (and of course causes the same error). If you're interested, I even have a throw-away test project that calls the sproc from multiple threads to simulate the production environment and cause the error very quickly.
BTW: The XML string the sproc takes is of a format something like:
<data>
<top>
<mid Name="SomeName" Value="1234">
<end Name="Something" Value="32" />
<end Name="Something Else" Value="33" />
</mid>
<mid Name="SomeNewName" Value="1234">
<end Name="Something" Value="32" />
<end Name="Something Else" Value="33" />
</mid>
</top>
</data>
The restrictions are there must be only one top value, the mid name must be unique within this xml string, and the end name must be unique within the containing mid node. There can be any number of mid nodes, and any number of end nodes inside of a mid node.
USE master
GO
CREATE DATABASE [DeadlockTest]
GO
USE [DeadlockTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TopTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TopTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Value] [int] NOT NULL,
[Number] [int] NOT NULL,
[CollectedUTS] [datetime] NOT NULL,
CONSTRAINT [PK_TopTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TopTable]') AND name = N'TopTable_ALT_KEY')
CREATE UNIQUE NONCLUSTERED INDEX [TopTable_ALT_KEY] ON [dbo].[TopTable]
(
[Name] ASC,
[Value] ASC,
[Number] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MidTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MidTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TopID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Value] [int] NOT NULL,
CONSTRAINT [PK_MidTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MidTable]') AND name = N'MidTable_ALT_KEY')
CREATE UNIQUE NONCLUSTERED INDEX [MidTable_ALT_KEY] ON [dbo].[MidTable]
(
[Name] ASC,
[TopID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EndTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EndTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MidID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Value] [int] NOT NULL,
CONSTRAINT [PK_EndTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [EndTable_ALT_KEY] UNIQUE NONCLUSTERED
(
[Name] ASC,
[MidID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWriteTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE procedure [dbo].[spWriteTable]
@.xml_string nText
AS
begin
set nocount on
declare @.TopName varchar(30)
declare @.TopValue int
declare @.Number int
declare @.TopTableId int
declare @.DefectID int
declare @.CollectedUTS datetime
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT, @.xml_string
select
@.TopName = r.[Name],
@.TopValue = r.[Value],
@.CollectedUTS = r.CollectedUTS
from OPENXML (@.hdoc,''/data/top'', 1)
WITH ([Name] NCHAR(5),
[Value] VARCHAR(30),
CollectedUTS datetime)r
set @.Number = null
set xact_abort on
begin tran
select top 1 @.Number = Number
from TopTable
where
[Name] = @.TopName and
[Value] = @.TopValue
order by Number DESC
if @.Number is null
set @.Number = 1
else
set @.Number = @.Number + 1
insert TopTable with (ROWLOCK) (
[Name],
[Value],
Number,
CollectedUTS)
select
[Name] = @.TopName,
[Value] = @.TopValue,
Number = @.Number,
CollectedUTS = @.CollectedUTS
set @.TopTableId = scope_identity()
insert MidTable with (ROWLOCK) (
TopID,
[Name],
[Value])
select
TopID = @.TopTableId,
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@.hdoc,''/data/top/mid'', 1)
WITH ([Name] VARCHAR(50), [Value] int)r
insert EndTable(
MidID,
[Name],
[Value])
select
MidID = (select ID from MidTable where TopID = @.TopTableId and [Value] = r.MidValue),
[Name] = r.[Name],
[Value] = r.[Value]
from OPENXML (@.hdoc,''/data/top/mid/end'', 1)
WITH ([Name] VARCHAR(50),
[Value] int,
MidValue int ''../@.Value'')r
commit
EXEC sp_xml_removedocument @.hDoc
end
'
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MidTable_TopTable]') AND parent_object_id = OBJECT_ID(N'[dbo].[MidTable]'))
ALTER TABLE [dbo].[MidTable] WITH CHECK ADD CONSTRAINT [FK_MidTable_TopTable] FOREIGN KEY([TopID])
REFERENCES [dbo].[TopTable] ([ID])
GO
ALTER TABLE [dbo].[MidTable] CHECK CONSTRAINT [FK_MidTable_TopTable]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EndTable_MidTable]') AND parent_object_id = OBJECT_ID(N'[dbo].[EndTable]'))
ALTER TABLE [dbo].[EndTable] WITH CHECK ADD CONSTRAINT [FK_EndTable_MidTable] FOREIGN KEY([MidID])
REFERENCES [dbo].[MidTable] ([ID])
GO
ALTER TABLE [dbo].[EndTable] CHECK CONSTRAINT [FK_EndTable_MidTable]
|||I have similar problem with insert into multiple tables linked by foreign keys. Thought this is very generic and common sql server issue -- never met this kind of issue with oracle. If you find any solution, please let me know.|||
We ended up with no other option but to use natural keys and drop the foreign key constraints. The application lock simply wouldn't work for the scale of writes to the tables (too much overhead), and the only other thing that worked (exclusive table locks) was a no go as well because an SSIS package eventually needed to read from those tables, which it wouldn't be able to do if the writers had an exclusive lock on them.
On the up side with the change we finally went with, besides the lack of deadlocks, is that the writes now perform much faster - about 5x faster under load. In the case of the caller of this particular sproc, speed really matters so this was huge. The speed increase was due almost exclusively to the dropped constraints.
On the downside, there is now no forced constraint between the tables, which can lead to some data integrity issues. However, for our case, writes to the 3 tables in question are done only through a single sproc, and we did a lot of testing around that sproc to make sure it's doing what it is supposed to, so we are pretty confident with the solution. It's not ideal, but it seems to do the job.
|||mmm.... We may have the constraints enabled in test environment to detect any corruption issues early on and disable them in production environment. A workaround hard to swallow.|||Are there any other better solutions - without having to get away with foreign key constraints.|||
I have posted a similar post @. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1440838&SiteID=1
Can someone explain the clause of deadlock.
Thanks,
Loonysan
0x576E3580)