best way to reduce deadlocks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

best way to reduce deadlocks

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 17:12:08

I have a search function, which searches across many tables.

It's a pretty heavy SPROC, I'm wondering in general, what are the best way to reduce deadlocks ? Its used a fair bit, and altho I haven't noticed problems with it myself, there are definately a decent amount of deadlocks showing up in the logfiles.

I've always assumed this is something really difficult, and avoided it like the plague.

Any tips are much appreciated !

thanks,

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:17:05

Which tables holds the locks?
Post the query.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 17:40:21

Hey Peso,

Was just going for general guidelines, but if you are willing to lend your expertise, I am more than happy to accept

Again, I am not sure its possible to write a new query for less locks, but perhaps you can give more detailed guidelines this way.

thanks very much !
mike123

CREATE Procedure [dbo].[UserSearch_New]

@Page int,
@RecsPerPage int,
@GenderID tinyint = NULL,
@NameOnline varchar(15) = NULL, --LIKE ?
@sexualityID tinyint = NULL,
@minAge tinyint = NULL,
@maxAge tinyint = NULL,
@PostalCode varchar(7) = NULL, --LIKE ?
@City varchar(25) = NULL,
@StateProvID tinyint = NULL,
@CountryID tinyint = NULL,
@minRating tinyint = NULL, --VOTES
@maxRating tinyint = NULL,
@statusID tinyint = NULL,
@bodyTypeID tinyint = NULL,
@hairColorID tinyint = NULL,
@eyeColorID tinyint = NULL,
@HeightFeet tinyint = NULL,
@HeightInches tinyint = NULL,
@educationID tinyint = NULL,
@employmentID tinyint = NULL,
@drinkID tinyint = NULL,
@smokeID tinyint = NULL,
@memberSinceDays tinyint = NULL,
@qsZeoFlick char(2) = NULL,
@qsOnline char(2) = NULL,
@qsSalute char(2) = NULL,
@orderBy tinyint

AS

Set Nocount on

Declare @Rows int

Create Table #Paging (
--Declare @Paging Table (
RowID int IDENTITY,
userID int,
NameOnline varchar(15),
age tinyint,
statusID tinyint,
Points int,
Votes int,
userNote varchar(35),
GenderID tinyint,
[date] smalldatetime,
--mainPhotoID int,
zeoExist char,
salute char
)

INSERT INTO #Paging (
userID, NameOnline, age, statusID, Points,
Votes, userNote, GenderID, date, zeoExist, salute)
SELECT
TOP 500
tblUserDetails.userID, tblUserDetails.nameOnline, age, statusID, points,
votes, userNote, genderID, date,

case
when tz.userid IS NOT NULL
then 'Y' Else 'N' end as ZeoExist,
case
when tep.userid IS NOT NULL then 'Y' Else 'N' end as Salute
FROM
tblUserDetails
LEFT JOIN (
SELECT DISTINCT userID
FROM tblActive_Users
) tau ON tau.userid = tblUserDetails.userID
LEFT JOIN (
SELECT DISTINCT userID FROM
tblExtraPhotos
WHERE
photoID = 99
) tep ON tep.userid = tblUserDetails.userid
LEFT JOIN (
SELECT DISTINCT userID
FROM tblZeoCast
WHERE active = 1
) tz ON tz.userid = tblUserDetails.userid

WHERE
tblUserDetails.active = 1 and
( @GenderID IS NULL OR GenderID = @GenderID) and
( @sexualityID IS NULL OR sexualityID = @sexualityID) and
( @NameOnline IS NULL OR NameOnline like '%' + @NameOnline + '%') and
( @minAge IS NULL OR age >= @minAge ) and
( @maxAge IS NULL OR age <= @maxAge ) and
( @PostalCode IS NULL OR PostalCode like @PostalCode + '%' ) and
( @City IS NULL OR City like '%' + @City + '%' ) and
( @StateProvID IS NULL OR StateProvID = @StateProvID ) and
( @CountryID IS NULL OR CountryID = @CountryID ) and
(
@minRating IS NULL OR
(
votes > 0 AND
@minRating <= (points / votes)
)
)
and
(
@maxRating IS NULL OR
(
votes > 0 AND
@maxRating >= (points / votes)
)
) and

( @statusID IS NULL OR statusID = @statusID ) and
( @bodyTypeID IS NULL OR bodyTypeID = @bodyTypeID ) and
( @hairColorID IS NULL OR hairColorID = @hairColorID ) and
( @eyeColorID IS NULL OR eyeColorID = @eyeColorID ) and
( @HeightFeet IS NULL OR HeightFeet = @HeightFeet ) and
( @HeightInches IS NULL OR HeightInches = @HeightInches ) and
( @educationID IS NULL OR educationID = @educationID ) and
( @employmentID IS NULL OR employmentID = @employmentID ) and
( @drinkID IS NULL OR drinkID = @drinkID ) and
( @smokeID IS NULL OR smokeID = @smokeID ) and
(
--@memberSinceDays is NULL OR
--DateDiff(d, date, GetDate()) <= @memberSinceDays

@memberSinceDays is NULL OR
date <= dateadd(day, -@memberSinceDays, GetDate()) -- Now you can utilize index over date column (if present)

) and
( @qsZeoFlick != 'on' OR tz.userid IS NOT NULL ) AND
( @qsOnline != 'on' OR tau.userid IS NOT NULL ) AND
( @qsSalute != 'on' OR tep.userid IS NOT NULL )

ORDER BY
Case @OrderBy
WHEN 1 THEN date
WHEN 3 THEN lastLoggedIn
ELSE NULL END DESC,
Case WHEN @OrderBy = 2 and votes > 0 THEN (points / votes) END DESC

SET @Rows = @@Rowcount

Declare @FirstRow int,
@LastRow int,
@TotalPages int

SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
SET @LastRow = @FirstRow + (@RecsPerPage - 1)
SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

SELECT
RowID, p.userID, NameOnline,
age, statusID, Points, Votes, userNote, GenderID,
[date], zeoExist, salute,
@Rows TotalRows, @TotalPages TotalPages
, ( SELECT Count(*) FROM tblExtraPhotos tep WHERE tep.userid = p.userid) as Thumb_Count

FROM #Paging p
WHERE rowid BETWEEN @FirstRow and @LastRow

Drop Table #Paging

Set Nocount off

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:52:17

First, change the

(
	@memberSinceDays is NULL OR
	date <= dateadd(day, -@memberSinceDays, GetDate()) -- Now you can utilize index over date column (if present)
)

Second, what are you doing with the two derived table tau and tep? Nothing?
I can't see a WHERE tau.userid is null and can't see a WHERE tep.userid is null...

Also see this blog post
http://weblogs.sqlteam.com/peterl/archive/2008/02/19/Efficient-pagination-for-large-set-of-data.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:59:05

[code]CREATE PROCEDURE dbo.UserSearch
(
@Page int,
@RecsPerPage int,
@GenderID tinyint = NULL,
@NameOnline varchar(15) = NULL, --LIKE ?
@sexualityID tinyint = NULL,
@minAge tinyint = NULL,
@maxAge tinyint = NULL,
@PostalCode varchar(7) = NULL, --LIKE ?
@City varchar(25) = NULL,
@StateProvID tinyint = NULL,
@CountryID tinyint = NULL,
@statusID tinyint = NULL,
@hairColorID tinyint = NULL,
@eyeColorID tinyint = NULL,
@HeightFeet tinyint = NULL,
@HeightInches tinyint = NULL,
@educationID tinyint = NULL,
@employmentID tinyint = NULL,
@drinkID tinyint = NULL,
@smokeID tinyint = NULL,
@memberSinceDays tinyint = NULL,
@qsOnline char(2) = NULL,
@orderBy tinyint
)
AS

SET NOCOUNT ON

--THESE TWO LINES ARE TO AVOID THE DIVIDE BY 0 ENCOUNTERED ERROR
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

Declare @Rows int

Create Table #Paging (
--Declare @Paging Table (
RowID int IDENTITY,
userID int,
NameOnline varchar(15),
age tinyint,
statusID tinyint,
GenderID tinyint,
[date] smalldatetime
)

INSERT #Paging
(
userID,
NameOnline,
age,
statusID,
GenderID,
date
)
SELECT TOP 500 ud.userID,
ud.nameOnline,
{Table alias missing here}.age,
{Table alias missing here}.statusID,
{Table alias missing here}.genderID,
{Table alias missing here}.date
FROM tblUserDetails AS ud
LEFT JOIN (
SELECT DISTINCT userID
FROM tblActive_Users
) tau ON tau.userid = ud.userID
LEFT JOIN (
SELECT DISTINCT userID
FROM tblExtraPhotos
WHERE photoID = 99
) tep ON tep.userid = ud.userid
WHERE ud.active = 1 and
( @GenderID IS NULL OR {Table alias missing here}.GenderID = @GenderID) and
( @sexualityID IS NULL OR {Table alias missing here}.sexualityID = @sexualityID) and
( @NameOnline IS NULL OR {Table alias missing here}.NameOnline like '%' + @NameOnline + '%') and
( @minAge IS NULL OR {Table alias missing here}.age >= @minAge ) and
( @maxAge IS NULL OR {Table alias missing here}.age <= @maxAge ) and
( @PostalCode IS NULL OR {Table alias missing here}.PostalCode like @PostalCode + '%' ) and
( @City IS NULL OR {Table alias missing here}.City like '%' + @City + '%' ) and
( @StateProvID IS NULL OR {Table alias missing here}.StateProvID = @StateProvID ) and
( @CountryID IS NULL OR {Table alias missing here}.CountryID = @CountryID ) and
( @statusID IS NULL OR {Table alias missing here}.statusID = @statusID ) and
( @hairColorID IS NULL OR {Table alias missing here}.hairColorID = @hairColorID ) and
( @eyeColorID IS NULL OR {Table alias missing here}.eyeColorID = @eyeColorID ) and
( @HeightFeet IS NULL OR {Table alias missing here}.HeightFeet = @HeightFeet ) and
( @HeightInches IS NULL OR {Table alias missing here}.HeightInches = @HeightInches ) and
( @educationID IS NULL OR {Table alias missing here}.educationID = @educationID ) and
( @employmentID IS NULL OR {Table alias missing here}.employmentID = @employmentID ) and
( @drinkID IS NULL OR {Table alias missing here}.drinkID = @drinkID ) and
( @smokeID IS NULL OR {Table alias missing here}.smokeID = @smokeID ) and
(
@memberSinceDays is NULL OR
{Table alias missing here}.date <= dateadd(day, -@memberSinceDays, GetDate()) -- Now you can utilize index over date column (if present)
)

SET @Rows = @@Rowcount

Declare @FirstRow int,
@LastRow int,
@TotalPages int

SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
SET @LastRow = @FirstRow + (@RecsPerPage - 1)
SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

SELECT {Table alias missing here}.RowID,
p.userID,
{Table alias missing here}.NameOnline,
{Table alias missing here}.age,
{Table alias missing here}.statusID,
{Table alias missing here}.GenderID,
{Table alias missing here}.[date],
@Rows as TotalRows,
@TotalPages as TotalPages,
( SELECT Count(*) FROM tblExtraPhotos as tep WHERE tep.userid = p.userid) as Thumb_Count
FROM #Paging AS p
WHERE {Table alias missing here}.rowid BETWEEN @FirstRow and @LastRow

Drop Table #Paging

Set Nocount off[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:14:44

The general idea is to only select to records you really need, and keep them under lock as short time as possible.
For some systems, using the WITH (NOLOCK) hint is advisable, and not for other systems.

Sometimes the WITH (ROWLOCK) query hint can help because then locks are placed on specific record, not on whole page where record is stored.

The most important thing is to write better code.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 18:21:55

Hi Peso,

Regarding

Second, what are you doing with the two derived table tau and tep? Nothing?

Actually you will hate me but I just posted an updated version of the SPROC as you were replying. It actually uses these tables. I posted the wrong one initially

I still get the idea of what you are saying, and will find all the changes you recommend in depth. Should I be prefixing all my columns with the proper table aliases ? Will this help performance as well as making it easier to read ?

thanks again !!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:41:12

[code]CREATE PROCEDURE dbo.uspUserSearch_New
(
@Page int,
@RecsPerPage int,
@GenderID tinyint = NULL,
@NameOnline varchar(15) = NULL, --LIKE ?
@sexualityID tinyint = NULL,
@minAge tinyint = NULL,
@maxAge tinyint = NULL,
@PostalCode varchar(7) = NULL, --LIKE ?
@City varchar(25) = NULL,
@StateProvID tinyint = NULL,
@CountryID tinyint = NULL,
@minRating tinyint = NULL, --VOTES
@maxRating tinyint = NULL,
@statusID tinyint = NULL,
@bodyTypeID tinyint = NULL,
@hairColorID tinyint = NULL,
@eyeColorID tinyint = NULL,
@HeightFeet tinyint = NULL,
@HeightInches tinyint = NULL,
@educationID tinyint = NULL,
@employmentID tinyint = NULL,
@drinkID tinyint = NULL,
@smokeID tinyint = NULL,
@memberSinceDays tinyint = NULL,
@qsZeoFlick char(2) = NULL,
@qsOnline char(2) = NULL,
@qsSalute char(2) = NULL,
@orderBy tinyint
)
AS

SET NOCOUNT ON

Declare @Rows int

Create Table #Paging
(
RowID int IDENTITY(1, 1), -- make a habit of always put seed and increment. you don't know if another developer have tampered with default settings
userID int,
NameOnline varchar(15),
age tinyint,
statusID tinyint,
Points int,
Votes int,
userNote varchar(35),
GenderID tinyint,
[date] smalldatetime,
zeoExist char,
salute char
)

INSERT #Paging
(
userID,
NameOnline,
age,
statusID,
Points,
Votes,
userNote,
GenderID,
date,
zeoExist,
salute
)
SELECT TOP 500 tblUserDetails.userID,
tblUserDetails.nameOnline,
age,
statusID,
points,
votes,
userNote,
genderID,
date,
case
when tz.userid IS NULL then 'N'
Else 'Y'
end as ZeoExist,
case
when tep.userid IS NULL then 'N'
Else 'Y'
end as Salute
FROM tblUserDetails
LEFT JOIN (
SELECT userID
FROM tblActive_Users
GROUP BY userID
) as tau ON tau.userid = tblUserDetails.userID
LEFT JOIN (
SELECT userID
FROM tblExtraPhotos
WHERE photoID = 99
GROUP BY userID
) as tep ON tep.userid = tblUserDetails.userid
LEFT JOIN (
SELECT userID
FROM tblZeoCast
WHERE active = 1
GROUP BY userID
) as tz ON tz.userid = tblUserDetails.userid
WHERE tblUserDetails.active = 1 and
( @GenderID IS NULL OR GenderID = @GenderID) and
( @sexualityID IS NULL OR sexualityID = @sexualityID) and
( @NameOnline IS NULL OR NameOnline like '%' + @NameOnline + '%') and
( @minAge IS NULL OR age >= @minAge ) and
( @maxAge IS NULL OR age <= @maxAge ) and
( @PostalCode IS NULL OR PostalCode like @PostalCode + '%' ) and
( @City IS NULL OR City like '%' + @City + '%' ) and
( @StateProvID IS NULL OR StateProvID = @StateProvID ) and
( @CountryID IS NULL OR CountryID = @CountryID ) and
(
@minRating IS NULL OR case when votes = 0 then 0 when @minRating <= 1.0 * points / votes then 1 else 0 end = 1 -- to avoid integer math
) and
(
@maxRating IS NULL OR case when votes = 0 then 0 when @maxrating >= 1.0 * points / votes then 1 else 0 end = 1
) and
( @statusID IS NULL OR statusID = @statusID ) and
( @bodyTypeID IS NULL OR bodyTypeID = @bodyTypeID ) and
( @hairColorID IS NULL OR hairColorID = @hairColorID ) and
( @eyeColorID IS NULL OR eyeColorID = @eyeColorID ) and
( @HeightFeet IS NULL OR HeightFeet = @HeightFeet ) and
( @HeightInches IS NULL OR HeightInches = @HeightInches ) and
( @educationID IS NULL OR educationID = @educationID ) and
( @employmentID IS NULL OR employmentID = @employmentID ) and
( @drinkID IS NULL OR drinkID = @drinkID ) and
( @smokeID IS NULL OR smokeID = @smokeID ) and
(
@memberSinceDays is NULL OR
date <= dateadd(day, -@memberSinceDays, GetDate()) -- Now you can utilize index over date column (if present)
) and
( @qsZeoFlick <> 'on' OR tz.userid IS NOT NULL ) AND
( @qsOnline <> 'on' OR tau.userid IS NOT NULL ) AND
( @qsSalute <> 'on' OR tep.userid IS NOT NULL )
ORDER BY Case
WHEN @OrderBy = 1 THEN date
WHEN @OrderBy = 2 and votes > 0 THEN 1.0 * points / votes
WHEN @OrderBy = 3 THEN lastLoggedIn
END DESC

SET @Rows = @@Rowcount

Declare @FirstRow int,
@LastRow int,
@TotalPages int

SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
SET @LastRow = @FirstRow + (@RecsPerPage - 1)
SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

SELECT
RowID, p.userID, NameOnline,
age, statusID, Points, Votes, userNote, GenderID,
[date], zeoExist, salute,
@Rows TotalRows, @TotalPages TotalPages
, ( SELECT Count(*) FROM tblExtraPhotos tep WHERE tep.userid = p.userid) as Thumb_Count

FROM #Paging p
WHERE rowid BETWEEN @FirstRow and @LastRow

Drop Table #Paging

Set Nocount off[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:45:08

I would add a column Rating to the UserDetail table and have a trigger maintaing the value.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-10 : 18:58:06

quote:
Originally posted by Peso

I would add a column Rating to the UserDetail table and have a trigger maintaing the value.

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

Any thoughts on this query in general?

I imagine its pretty tough to get a feel on this query with the limited data I am able to provide here.

I would imagine that 99% of the time we pass values for MIN =0 and MAX = 10 .. perhaps I should change this to nulls so that we dont run this portion of the query when we are searching on those values since they are the floor and ceiling values anyways ? Thoughts?

thanks once again

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources