Trying to resolve a deadlock issue (TRY-CATCH OR with (nolock) ?)
-
I have a recurring issue with deadlocks in one part of my web based system. The system allows users to upload banner
adverts to their site and in the banner manager against each advert is the number of hits/views each banner has had.
These figures need to be real time and when I have tried to make them non-real time have had lots of complaints so the
current set up is this.
I have the main table BANNER_ADVERTS that has BannerPK int as the PK, and the other fields hold info such as the image path,
image dimensions, expiry date etc.
I then have a table BANNER_HITS which holds all the stats for the current day only. When either a banner is loaded to a page or
clicked on I insert a record into this table that holds: BannerFK, HitType char(1) (H = hit or click through, V = viewed), Stamp datetime
default = getDate() (I record this in case the nightly job fails for whatever reason)
I then have a nightly job that collates this information for each banner and stores the banner statistic history in another table
BANNER_HITS_HISTORY which has the following columns: BannerFK FK, Stamp = datetime in day format only eg 23/10/2007,
Hits = total no hits for that day, Views = total no of views for that day.
However looking at my daily error log file I was getting reports of deadlocks on the followin procedure which is used to record a hit
or view into the daily stats table.
EXEC dbo.usp_asp_update_banner_hit @BannerPK = '1492,1189,488,2041,1829,1856,1344,1982,2276,1013,2045,1340,2088',@HitType= 'V';
SQL Error: -2147467259; [Microsoft][ODBC SQL Server Driver][SQL Server]
Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.;
[dbo].[usp_asp_update_banner_hit]
@BannerPK varchar(100),
@CounterType char(1) = 'V'
as
if @HitType = 'V' --update views
begin
INSERT INTO tbl_BANNER_HITS
(BannerFK, HitType)
SELECT
d.[VALUE], 'V'
FROM
dbo.udf_SPLIT(@BannerPK,',') as d --this function splits a csv string of IDs eg 122,3445,556,445 using a table variable in a user defined function
end
else
begin --update hits
INSERT INTO tbl_BANNER_HITS
(BannerFk, HitType)
VALUES
(@BannerPK, 'H')
end
The only place in the system that retrieves information from this daily stats table (and so could cause a conflicting lock)
is in the banner manager which needs to show total amount of hits / views
for each advert since it was made live. The SQL needs to retrieve stats from both the daily and the history table and the current SQL is:
SELECT ba.BannerPK, ba.BannerTitle,
(SELECT SUM(a.Views) FROM
(SELECT count(BannerFK)as Views FROM BANNER_HITS WHERE BannerFK=ba.BannerPK AND HitType='V'
UNION
SELECT sum(Views)as Views FROM BANNER_HITS_HISTORY WHERE BannerFK=ba.BannerPK )as a
 
as Views,(SELECT SUM(b.Hits) FROM
(SELECT count(BannerFK)as Hits FROM BANNER_HITS WHERE BannerFK=ba.BannerPK AND HitType='H'
UNION
SELECT sum(Hits)as Hits FROM BANNER_HITS_HISTORY WHERE BannerFK=ba.BannerPK )as b
 
as HitsFROM BANNER_ADVERTS as ba
WHERE SiteID = 8 AND ImageType=2
ORDER BY CreateDate DESC
I have covering clustered indexes on BANNER_HITS (BannerFk, Stamp, HitType) and on BANNER_HITS_HISTORY (BannerFk, Stamp, Views, Hits).
Both of these indexes currently have a fillfactor of 70%, on a side note is this a reasonable setting for a table that just accepts inserts and
no updates but is also used in the reporting Select? Currently both indexes have hardly any fragmentation.
Now these deadlock errors don't happen all the time but I would like to handle them or avoid them if possible. I have two ideas.
I presume the deadlock is happening because someone is trying to report on the banner stats at the same time as the constant inserts into the table.
So on the report SELECT statement use with (nolock) hint on the sub-select union statements.
The data in those tables is never going to be updated as the table just accepts inserts throughout the day so dirty reads aren't a
worry really.
OR as I am using SQL 2005 re-write the insert procedure to something like:
ALTER PROCEDURE [dbo].[usp_asp_update_banner_hit]
@BannerPK varchar(100),
@CounterType char(1) = 'V'
as
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION
BEGIN TRY
IF @CounterType = 'V'
BEGIN
INSERT INTO BANNER_HITS
(BannerFK, HitType)
SELECT
d.[VALUE], 'V'
FROM
dbo.udf_SPLIT(@BannerPK,',') as d
END
ELSE
BEGIN
INSERT INTO BANNER_HITS
(BannerFk, HitType)
VALUES
(@BannerPK, 'H')
END
--if we are here its been successful ie no deadlock
COMMIT
BREAK
END TRY
BEGIN CATCH
ROLLBACK --always rollback
--if its a deadlock then wait and try again
IF ERROR_NUMBER() = 1205
BEGIN
WAITFOR DELAY '00:00:02' --wait 2 seconds
END
SET @Tries = @Tries + 1 --increment and try again
CONTINUE
END CATCH;
END
Now I have read that with (nolock) should be used as a last resort but maybe in this situation it might be worth doing.
Or maybe I should do both. Does anyone have any suggestions of what the best course of action would be and is the TRY CATCH
worth doing if this INSERT PROC is always being chosen as the deadlock victim?
Thanks for any advice or help in advance.
-
I'd suggest you run profiler and catche the deadlock graph event. That will give you a graphical view of what was caused the deadlock, both the one that was picked as the victim and the one that continued.
I don't think you can try-catch a deadlock, as SQL will terminate the connection of the victim as soon as it detects the deadlock.
Once you've found the two halves of the deadlock, try running them through management studio with the execution plan on. See if you can optimise the query.
I';ve often found that I can fix a deadlock by adding or rebuilding an index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi, I was using the TRY CATCH method described in this article http://msdn2.microsoft.com/en-us/library/aa175791(SQL.80).aspx which suggests that you can use TRY CATCH to catch a deadlock and prevent your procedure from aborting the connection. Apparently its a new feature in 2005.
-
Interesting. I wasn't aware of that.
I would still suggest that you try and find the cause of the deadlock and eliminate it, rather than handling and trying again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I do want to try and resolve the deadlock issue but this seemed a nice way of trying to handle it until I can find out whats causing it and actually fix the issue. I thought it must be the conflict between the multiple inserts into the BANNER table (which occurs constantly throughout the day) and at the same time the admin requesting a select off that table.
I have not had much experience with running traces before but there are a couple of issues I would like to look for if I do run a trace and hopefully not create too much of a massive log file at the same time. Can you give me advice on the options I should switch on for looking for this deadlock issue and also we are experiencing lots of these SQL errors:
SQL Error: 3709; The connection cannot be used to perform this operation. It is either closed or invalid in this context.;
We have just moved to dedicated web & DB servers and experiencing lots of these errors which suggest a temporary connection drop between the web server and db server. Whenever I try and run the webpage that logged the error it runs perfectly well so whatever is causing the error seems to be very intermittent.
So what options in the profile trace should I switch on to find out the relevant information that will help me diagnose and fix:
-Deadlocks or circular locks that cause timeout errors (but no deadlock error reported)
-These temporary connection drops.
Thanks for your advice and help.
-
if anyone wants i can post the scripts we use. we have a blocking table on each server that has selecte rows from sysprocesses. and a job runs every 2 minutes to check for deadlocks. if one is found it writes to the table and sends an email
-
Hi,
Can you please send the script for this..
Thnx,
Ezhilan
-
/****** Object: StoredProcedure [dbo].[check_blocking] Script Date: 07/17/2007 11:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[check_blocking]
as
/*
select distinct a.spid, substring(a.status,1,10) status, a.blocked, substring(a.NT_Login,1,16) NT_Login,
substring(a.program_name,1,24) program_name, a.real_time, substring(a.query,1,100) query
from billing..blocking a with (NOLOCK), billing..blocking b with (NOLOCK) where a.real_time > dateadd(mi,- 2,getdate())
--and and a.tabId = b.TabId
and ((a.blocked = 0 or b.spid=b.blocked))
*/
-- If exists (select * from master.dbo.sysprocesses where blocked > 0 and datediff(minute, last_batch, getdate()) > 1 )
If exists (select * from master.dbo.sysprocesses where blocked > 0 and blockedspid and (waittime > 60000 or datediff(minute, last_batch, getdate()) > 2 ))
begin
declare spid_cursor cursor
for select distinct blocked from master.dbo.sysprocesses (nolock)
open spid_cursor
declare @query varchar(255), @spid int, @sql varchar(50)
fetch next from spid_cursor into @spid
while @@fetch_status = 0
begin
create table #dbcc (EventType varchar(30), Parameters int, EventInfo varchar(600))
set @sql = 'dbcc inputbuffer(' + convert(varchar(4),@spid) + ')'
insert into #dbcc exec (@sql)
set @query = (select EventInfo from #dbcc)
drop table #dbcc
INSERT into billing..blocking
SELECT spid, status, blocked, substring(NT_username,1,25) NT_Login,
substring(hostname,1,30) hostname, substring(loginame,1,30) SQLlogin ,login_time , last_batch, @query, program_name, getdate()
FROM master.dbo.sysprocesses where spid = @spid
fetch next from spid_cursor into @spid
end
close spid_cursor
deallocate spid_cursor
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dba',
@recipients = 'dba@yourcompany.com',
@body= 'There is a blocking on SQLServer! Please be patient, this lock will be released momentarily. The blocking statement is in Billing..blocking table',
@subject = 'SQL blocking !'
end
-
/****** Object: Table [dbo].[blocking] Script Date: 07/17/2007 11:34:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[blocking](
[tabID] [int] IDENTITY(1,1) NOT NULL,
[spid] [smallint] NOT NULL,
[status] [varchar](50) NOT NULL,
[blocked] [smallint] NOT NULL,
[NT_Login] [varchar](50) NULL,
[hostname] [varchar](50) NULL,
[SQLlogin] [varchar](50) NULL,
[login_time] [datetime] NULL,
[last_batch] [datetime] NULL,
[query] [varchar](1000) NULL,
[program_name] [varchar](255) NULL,
[real_time] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-
Thanks...
It was really usefull..
Kindest Regards,
Ezhilan
-
I've succesfully used TRY..CATCH to handle deadlocks. Works great. Even set it upto record it when they happened, with how many retries etc. I was thinking about putting a time delay into it, but tried it first without. Only 3 times in 6 months have I seen it have to do more than one retry before it succesfully saved the record.
I found it easier to use than the graph. After looking at enough of the records I found the worst culprit causing it. All I needed to fix the deadlock was one extra column indexed in a table and most of them went away.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply