January 24, 2013 at 7:49 am
I am having some problems with frequent deadlocks in a vendor-supplied database and I have been analyzing the database and working with the vendor to try and resolve these deadlocks. I have found multiple issues that needed to be addressed but I still have not been able to remove these deadlocks. I keep running up against this one issue that I just can’t resolve. The following is an explanation of the issue followed by a modified (for security) scripting of the tables and one procedure involved in these deadlocks. (If you find something that doesn’t make sense in my scripts, please let me know. I tried my best to change most things to a generic setup but I may have missed something.)
The procedure scripted below, spUpdateObjectHeader is involved in all of the deadlocks. The other procedures don’t really matter because the issue is always the same, I am going to explain the issue, and my problem has to do with this procedure more than the others.
As you will see, all this procedure is doing is executing an UPDATE on TableA. The only criteria used to identify the record to update is the Primary Key column¸ ObjectID. When this procedure is involved in the deadlock, this is why the deadlock occurs.
The procedure gets an EXCLUSIVE (X) Key Lock on the Primary Key of TableB. It then tries to get an EXCLUSIVE (X) Key Lock on the TABLEA_IDXCB index on TableA. The other procedures already have a SHARED (S) Key Lock on the TABLEA_IDXCB index on TableA and they want to get a SHARED(S) Key Lock on the Primary Key of TableB. The procedure shown below (the UPDATE) is always the victor in these deadlocks and the other procedures (all SELECTs) are always the victims.
We did try, and it did help, to use the NOLOCK query hint on the SELECT procedures but we would really prefer not to do that because of the nature of the process and we don’t want to risk any dirty reads.
MY QUESTION IS: Why is the procedure below, which is only updating TableA, putting an EXCLUSIVE lock on TableB? My guess is that it has something to do with the Foreign Key constraint from TableB to TableA but I don’t know how to confirm that without simply removing the Foreign Key constraint. (I don’t really have a good setup, in another environment, to mimic the operations causing the deadlocks at this time.)
If this is caused by the FK, does anyone have any suggestions on what I might be able to do to resolve these deadlocks? Could we remove the FK and use triggers to keep the relationship intact? Any other suggestions?
I appreciate all of your help!
Here are the scripts:
/****** Object: Table [dbo].[TableA] Script Date: 01/24/2013 08:05:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[ObjectID] [int] NOT NULL,
[ColA] [uniqueidentifier] NULL,
[ColB] [uniqueidentifier] NOT NULL,
[ColC] [bigint] NULL,
[ColD] [datetime] NULL,
[ColE] [bigint] NOT NULL,
[ColF] [tinyint] NULL,
[ColG] [varchar](64) NOT NULL,
[ColH] [varchar](64) NULL,
[ColI] [varchar](64) NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[ObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uk_TableA_id] UNIQUE NONCLUSTERED
(
[ColB] ASC,
[ColC] 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
SET ANSI_PADDING OFF
GO
/****** Object: Index [TABLEA_IDXCB] Script Date: 01/24/2013 08:05:36 ******/
CREATE NONCLUSTERED INDEX [TABLEA_IDXCB] ON [dbo].[TableA]
(
[ColC] ASC,
[ColB] ASC
)
INCLUDE ( [ColD],
[ColF],
[ColE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [TABLEA_IDXAB] Script Date: 01/24/2013 08:05:36 ******/
CREATE NONCLUSTERED INDEX [TABLEA_IDXAB] ON [dbo].[TableA]
(
[ColA] ASC,
[ColB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TableB] Script Date: 01/24/2013 07:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableB](
[ObjectID] [int] NOT NULL,
[Data] [xml] NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[ObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
/****** Object: Index [idxXMLData] Script Date: 01/24/2013 07:49:09 ******/
CREATE PRIMARY XML INDEX [idxXMLData] ON [dbo].[TableB]
(
[Data]
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
ALTER TABLE [dbo].[TableB] WITH CHECK ADD CONSTRAINT [FK_TableB_TableA] FOREIGN KEY([ObjectID])
REFERENCES [dbo].[TableA] ([ObjectID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [FK_TableB_TableA]
GO
/****** Object: StoredProcedure [dbo].[spUpdateObjectHeader] Script Date: 01/24/2013 07:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateObjectHeader]
@objectID int,
@ColG VARCHAR(64),
@ColH VARCHAR(64),
@ColI VARCHAR(64),
@ColD datetime
AS
Update TableA
Set [ColG]=@ColG,
[ColH]=@ColH,
[ColI]=@ColI,
[ColD]=@ColD
Where [ObjectID]=@objectID
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 26, 2013 at 6:35 am
When I try your code as-is and execute the proc I am not seeing any locks opened against TableB which is what I would expect since no FK columns are being affected. Did you maybe pare down the proc too much? Does the actual proc also contain ObjectID in the SET-column-list?
After changing your sample proc in this way I was able to start seeing locks opened on TableB due to the FK with ON DELETE CASCADE.
ALTER PROCEDURE [dbo].[spUpdateObjectHeader]
@objectID INT,
@ColG VARCHAR(64),
@ColH VARCHAR(64),
@ColI VARCHAR(64),
@ColD DATETIME
AS
UPDATE TableA
SET [ColG] = @ColG,
[ColH] = @ColH,
[ColI] = @ColI,
[ColD] = @ColD,
[ObjectID] = @objectID
WHERE [ObjectID] = @objectID;
PS You may want to update your original post and remove a USE statement that still contains what looks like real info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 28, 2013 at 7:10 am
opc.three, first, thanks for pointing out the USE statement. I simply missed that. Second, I just re-checked and the procedure is correct.
I'll admit, in all my testing, I failed to look at the execution plan of the procedure. I was looking at the actual deadlock graphs and information coming from the deadlocks and trace information. I looked at the plan both ways and I do see the difference.
Things make even less since this morning. We removed the Foreign Key constraint and we are still getting deadlocks because the UDPATE on TableA is still putting an Exclusive Key Lock on the Primary Key of TableB.
I am going to make sure everything has recompiled and all statistics have been updated as well as digging deeper into the database and see if there are any other hooks. I just knew it was the FK somehow BUT it seems that I was wrong.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 28, 2013 at 7:16 am
Are you sure there no DML triggers in play? Which exact build of SQL Server are you using?
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 28, 2013 at 8:03 am
Standard Edition
10.0.4000.0
SP2
There are no DML triggers. I have scripted out everything as well as checking the included objects, in multiple ways.
I cannot figure this out. I recompiled both procedures and there is absolutely no reason (that I can find) that the Update procedure should get any kind of lock on the TableB table.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 31, 2013 at 11:49 am
I just wanted to follow-up with what happened with this issue.
After nearly bringing the system to its knees by running Profiler traces to find ALL of the locking, and other background, information for a deadlock, I was able to determine that the issue is not in the database but rather in the client code. In this situation, TableA is "header" table for the "detail" records in TableB. In the client code, an explicit transaction has been opened and, within that transaction, the code updates TableB then it updates the header information in TableA. By trying to avoid having the data out of sync, even for the briefest of moments, this is all done in one transaction so the lock on TableB is held longer than just the time needed to perform the update. Because this system is so heavily used, this often causes the deadlocks we are seeing.
Because this is a third-party app, I don't have any insight into the client code. So, I sent everything I found to the vendor's support staff and told them it was up to them to determine how to proceed.
I'm interested to see how they handle this one.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 31, 2013 at 3:18 pm
Thanks for posting back! This thread bugged me because the symptoms simply did not compute based on the information on hand. Nice detective work. It's nice to know there were other forces (besides black magic) in play and you found out what they were. Best of luck to you getting the vendor to react, that can be tough sometimes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply