January 11, 2010 at 11:35 pm
Hi Friends
I'm in a trouble with a dead lock situation. I have deployed an application onto live and its working fine. Recently i have my client coming back saying getting a dead lock issue in the application.
1) I have configured the query profiler and found one of my sp is throwing the issue
2) Lets say i have my table as MyTable and i have a sp called AddUpdateMyTable which accepts five input parameters
3)I have the following logic wound around the Stored Procedure
a) Select query against MyTable which returns a Count1 based on some conditions
b) Select query against MyTable which returns a Count2 based on some conditions
c)i have a if else condition
IF(Count1>0
BEGIN
-- Update logic onto MyTable
END
ELSE
BEGIN
IF(Count2>0)
BEGIN
-- Update logic onto MyTable
END
ELSE
BEGIN
-- Insert logic onto MyTable
END
END
4) The above mentioned is my logic against Add/Update onto MyTable
5) This procedure is called from a button click on a page and may have to call tgousand times or more in a moment.
6)This sp was working fine as i can see around 5 lakhs records in the database inserted using this sp.
7)With reference to my Query profiler output im having the issue here in this sp itself
Can you guys help me out with this would be much appreciable
Thank You
SUJITH PV
January 12, 2010 at 1:21 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
January 12, 2010 at 3:13 am
Hi
Pls have a glance at the attached doc.
January 12, 2010 at 11:02 am
Hi,
I'm not an expert analysing DeadLock logs, but i think that those two update blocks are causing the Dead Lock.
Process: process8dd1f8, Procname=AddBookingAvailabilitySettingCoverSize, Line: 82
UPDATE [BookingAvailabilitySettingCoverSize] SET
[BookingAvailabilitySettingID] = @prmBookingAvailabilitySettingID,
[RestaurantSessionSittingTimeID] = @prmRestaurantSessionSittingTimeID ,
[TableCoverSize] = @prmTableCoverSize ,
[AvailableCoverSize] = @prmAvailableCoverSize ,
[TableCoverStatus] = @prmTableCoverStatus ,
[TableSpecialComments] = @prmTableSpecialComments
WHERE [BookingAvailabilitySettingCoverSizeID] = @prmBookingAvailabilitySettingCoverSizeID
AND [BookingAvailabilitySettingID] = @prmBookingAvailabilitySettingID
AND [TableCoverSize] = @prmTableCoverSize
Process: process8dcc58, Procname=AddBookingAvailabilitySettingCoverSize, Line: 113
UPDATE [BookingAvailabilitySettingCoverSize] SET
[BookingAvailabilitySettingID] = @prmBookingAvailabilitySettingID,
[RestaurantSessionSittingTimeID] = @prmRestaurantSessionSittingTimeID ,
[TableCoverSize] = @prmTableCoverSize ,
[AvailableCoverSize] = @prmAvailableCoverSize ,
[TableCoverStatus] = @prmTableCoverStatus ,
[TableSpecialComments] = @prmTableSpecialComments
WHERE [BookingAvailabilitySettingID] = @prmBookingAvailabilitySettingID
AND [TableCoverSize] = @prmTableCoverSize
AND [RestaurantSessionSittingTimeID] = @prmRestaurantSessionSittingTimeID
these two process are blocking each other an the processa that was chosen to be the deadlock victim was:
Process: process8dd5b8, Procname=AddBookingAvailabilitySettingCoverSize, Line: 47
SELECT @BookingAvailabilitySettingCount = COUNT([BookingAvailabilitySettingCoverSizeID])
FROM [BookingAvailabilitySettingCoverSize]
WHERE [BookingAvailabilitySettingID] = @prmBookingAvailabilitySettingID
AND [TableCoverSize] = @prmTableCoverSize
AND [RestaurantSessionSittingTimeID] = @prmRestaurantSessionSittingTimeID
Could you provide the definition of the BookingAvailabilitySettingCoverSize table? Including indexes definition on this table.
How many records does this table has?
José Cruz
January 16, 2010 at 3:56 am
Interesting, a 3-process deadlock. Not the most common type.
Can you post some more information please. I need to see the definition of the stored proc "AddBookingAvailabilitySettingCoverSize" and the definition (with all indexes) of the table BookingAvailabilitySettingCoverSize
Thanks
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply