Help explaining a deadlock
-
My website just reported a number of timeout errors connecting to the database.
ASPCode =
ASPDescription =
Category = Microsoft VBScript runtime
Column = -1
Description = Object required: 'objDataCon.ExecSQL(...)'
File = /jobboard/scripts/classes/banner.class.asp
Line = 299
Number = -2146827864
Source =
Last SQL = EXEC dbo.usp_asp_get_banner_advert @search = '', @SitePk = 36, @GroupPK = 1; = -2147217871 SQL Error: [Microsoft][ODBC SQL Server Driver]Timeout expired
This SQL is always the first piece ran on each page in the site and returns the filename of the appropriate banner to display.
Within the procedure usp_asp_get_banner_advert there is a conditional SQL
If the @search parameter contains a textual value eg 'ASP SQL' the SQL statement joins a few banner related table to a SQL full text catalogue e.g.
SELECT top 1 BannerPK, bgp.PageFK, rank, ba.*, bl.Height, bl.Width
FROM tbl_BANNER_ADVERTS as ba
JOIN tbl_BANNER_GROUPS as bg
ON ba.BannerPK = bg.BannerFK
JOIN tbl_BANNER_GROUP_PAGES as bgp
ON bgp.SiteFK = @SitePK AND bg.GroupFK = bgp.GroupFK AND bgp.PageFK = @PagePK
JOIN CONTAINSTABLE
(tbl_BANNER_ADVERTS, keywords, @search) as k
ON ba.BannerPK = k.
JOIN tbl_BANNER_LOCATIONS as bl
ON bl.SiteFK = @SitePK AND bl.ImgType = ba.ImgType
WHERE ba.SiteFK = @SitePK AND
Live = 1 AND
[FileName] is not null AND
rank=(
select top 1 [rank]as rank
from tbl_BANNER_ADVERTS as ba
join tbl_BANNER_GROUPS as bg
on ba.BannerPK = bg.BannerFK
join tbl_BANNER_GROUP_PAGES as bgp
on bgp.SiteFK = @SitePK AND bg.GroupFK = bgp.GroupFK AND bgp.PageFK = @PagePK
join CONTAINSTABLE
(tbl_BANNER_ADVERTS, keywords, @search) as k
on ba.BannerPK = k.
where ba.SiteFK= @SitePK AND
ba.ImgType = 1 AND
Live = 1 AND
[FileName] is not null AND
PageFK = @PagePK
order by rank desc
 
ORDER BY rank desc, newid()
if however the @search paramter is empty as in ALL CASES reported in this error batch then the SQL is the following:
SELECT top 1 ba.* , bl.Height, bl.Width
FROM tbl_BANNER_ADVERTS as ba
JOIN tbl_BANNER_GROUPS as bg
ON ba.BannerPK = bg.BannerFK AND bg.GroupFK = @GroupPK
JOIN tbl_BANNER_LOCATIONS as bl
ON bl.SiteFK = @SitePK AND bl.ImgType = 1
WHERE ba.siteFK = @SitePk AND
ba.Live = 1 AND
ba.ImgType = 1 AND
[FileName] is not null AND
weight = 1 AND
GroupFK = @GroupPK
ORDER BY newid()
Now when the errors started coming in I went to the Locks/Process ID under current activity.
I could lots of processes (that contained the SQL usp_asp_get_banner_advert in the properties tab)
that were being locked by another process.
I looked at the properties of this blocking process and the SQL was the following:
SELECT customValidationASP, customValidationJS
FROM tbl_CUSTOM_VALIDATION
WHERE siteFK = 33 AND pageFK = 79;
This statement does not return any records at all.
There is a PK clustered index on SiteFk, PageFK. I don't know whether an index is necessary because of the small
size of the table though.
Now this table only has 5 records in it and is totally unrelated to the banner code. It is not dependant
on any other table and I never use it in any joined SQL statement. In fact the 5 records in the table have been there for over 1 year
and records are only added manually into this table (no process could have been updating/inserting a record at the time)
I really can't understand how this sql statement could be blocking the other statements.
Once we killed this blocking process everything started working again.
I am hoping someone can explain why this situation may have occurred and if there's anything I can do to prevent it in future.
I have been told to add with (nolock) on all the tables in the banner sql.
Any help would be much appreciated
-
sorry, a bit in a hurry.
check if your scripts fetches all data and are not holding transactions.
You might check this one out
-
Your title of 'deadlock' is a bit misleading. You are getting lock timeout messages due to blocking whick is not the same thing as a deadlock. You need to do as Joe suggested and investigate the transaction and see what databases resources it is locking. Run a SQLDiag while the blocking is happening and analyze the results file.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply