Avatar of maltomeal8
maltomeal8

asked on 

Two SELECT queries deadlocking each other

I am using SQL Server 2005.  I am running the deadlock graph in SQL Server Profiler and trying to track down the cause of the deadlocks I'm seeing.  This one is baffling me.

I see two instances of the same SELECT query in deadlock.  I have attached a screenshot of the output from the deadlock graph.  When I hover over each of the circles I see the same query.  It looks like:

SELECT pw_id AS cw_id
FROM pw
WHERE pb_id = @pb_id
    AND m_id = @m_id

1. why are they both getting X locks?  they are just selects.
2. how can I fix this?  I could try using WITH (NOLOCK) but I still don't understand why it's doing this.
deadlock.JPG
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]
Avatar of pdandrea
pdandrea

Try the following:

SELECT pw_id AS cw_id
FROM pw (nolock)
WHERE pb_id = @pb_id
    AND m_id = @m_id
ASKER CERTIFIED SOLUTION
Avatar of pdandrea
pdandrea

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo