October 24, 2008 at 9:36 am
Our application experienced a deadlock yesterday that I cannot explain from the deadlock graph.
It appears that 2 processes call a proc which updates a single row in a table. This table has 2 columns - tablename (varchar 35) and value (int), with the only index being a clustered primary key on tablename. It contains approximately 80 rows. I believe that the graph shows that each process was able to obtain an update lock on the same record but deadlocked when they both attempted to update to an exclusive lock - the problem being that 2 processes shouldn't be able to get an update lock simultaneously on the same resource.
I don't have a great deal of experience in investigating deadlocks so I'd be grateful if someone could confirm my thoughts or point me in the right direction.
October 24, 2008 at 11:59 am
They didn't have locks on the same key. If you look at the resource, you'll see that while the file and partition are the same, the hash of the key values are different. Also, from what I can see, the exclusive locks were what the processes had, the update locks is what they tried to get,
Spid 77 had a X lock on KEY: 5:368872461762560 (9600d55236ca). Spid 82 wanted an U lock on the same key
Spid 82 had a X lock on KEY: 5:368872461762560 (9600a25106e9). Spid 77 wanted an U lock on the same key
The value in brackets is the hash of the key values for the particular row.
What does the proc dbo.GET_NEXT_INC_BLOCK do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 12:31 pm
Thanks Gail, I see how you are reading the graph now (the articles I looked at were not entirely clear).
The proc is executing the following code
DECLARE @LastValue INT
SET NOCOUNT ON
UPDATE SYS_UNIQUE_INC
SET @LastValue = Value = Value + @Quantity
WHERE Table_Name = @Table_Name
SET @Value = @LastValue - @Quantity
where @table_name and @quantity are input parameters and @value is an output parameter ... in other words it adds @quantity to the value column of the appropriate unique record in the table and returns the old value.
The table definition is as follows:
/****** Object: Table [dbo].[SYS_UNIQUE_INC] Script Date: 10/24/2008 13:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYS_UNIQUE_INC](
[TABLE_NAME] [varchar](35) NOT NULL,
[VALUE] [int] NOT NULL,
CONSTRAINT [PK_SYS_UNIQUE_INC] PRIMARY KEY CLUSTERED
(
[TABLE_NAME] 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]
GO
SET ANSI_PADDING OFF
GO
My understanding is that the update lock will be taken on the appropriate record as the table scan is performed, which is then converted to an exclusive lock to make the change. I can't see a reason why they would want to get anything other than an update lock on a single record, which converts to an exclusive lock on the same record.
October 24, 2008 at 3:08 pm
Is that all that's in that proc? The deadlock graph refers to an update on Line 15.
Also, the deadlock details indicate that the processes both had exclusive locks and tried to aquire update locks. With an update statement, it happens the other way around, update lock first, exclusive after. That seems to indicate that some locks were already in place when the update started. Could there have been an open transaction when that proc was called? Where does it get called from?
Just to be sure, what does SELECT object_name(1228583465) return when run in the DB with an Id of 5?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 3:16 pm
That query returns GET_NEXT_INC_BLOCK as expected.
That is the entire content of the proc after I strip out the parameters and the comments.
It's possible that this was part of a larger transaction, but I wasn't running a trace at the time and this is the first deadlock I've seen in the app in a very long time - it won't be easy to figure out.
October 24, 2008 at 3:26 pm
In that case, to figure this out you're going to need to figure out where it's getting called from. If that's the only query in the proc, then the processes already have the exclusive locks as they enter the procedure.
Is this run often? Profiler should give you the info, but it may take some work filtering things. Try the SP:Started and SP:Completed events. It should let you track things back.
Or search the sys.sql_modules view and see if this proc is referenced anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 3:31 pm
Hmm, was editing as you posted ... I'll have to profile. Thanks for your time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply