Re: Select statement Causing Table Locks
- From: John Couch <JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 17 Sep 2009 15:37:02 -0700
Ok, it is technically a blocking chain, but this query does not continue. It
is blocking updates to the two tables from other users. It holds exclusive
locks on the tables themselves.
objectowner objectname indid indexname locktype resource lockreq status
dbo Task 1 TASKRECID Page 1:1029887 Exclusive Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Exclusive Granted
dbo Task 1 TASKRECID Page 1:1230305 Exclusive Granted
dbo Task 1 TASKRECID Key (ca0180e2e8e9) Schema modification Granted
dbo Task 0 Table Exclusive Granted
dbo Task 0 Table Exclusive Granted
dbo Task 0 Table Exclusive Granted
dbo Task 1 TASKRECID Page 1:1029887 Exclusive Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Exclusive Granted
dbo Task 1 TASKRECID Page 1:1230305 Exclusive Granted
dbo Task 1 TASKRECID Key (ca0180e2e8e9) Schema modification Granted
dbo Task 0 Table Exclusive Granted
dbo Task 0 Table Exclusive Granted
dbo Task 0 Table Exclusive Granted
dbo Task 1 TASKRECID Page 1:1029887 Exclusive Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Schema modification Granted
dbo Incident 0 Table Exclusive Granted
"Erland Sommarskog" wrote:
John Couch (JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:.
I have a database on a sql server 2005 instance running under
compatability mode. A user is executng the below statement, and it is
causing deadlocks by holding table locks on all the tables in the from
clause. I tried using nolock, but it doesn't prevent it. Any thoughts?
Deadlocks? Who is the deadlock victim? This query or someone else?
To be precise, a deadlock is when two or more processes all block each
other, so that no one can continue.
SQL Server will detect deadlock situations, and will terminate requests
until the deadlock has been resolved. This usually takes a couple of
seconds.
On the other hand, if this goes on forever, then it's just a blocking
situation. Which certainly can be nasty, but it is not a deadlock.
SELECT Incident.IncidentNumber,
Incident.Company,
Incident.Priority,
Incident.TypeOfIncident,
Incident.Subject,
Task.AssignmentQue,
Incident.CreatedDateTime,
Task.CreatedDateTime,
Incident.ResolvedDateandTime,
Incident.Category,
Task.AssignmentGroup
FROM {oj ITSM.dbo.Task Task with (ROWLOCK) LEFT OUTER JOIN
ITSM.dbo.Incident
Incident with (ROWLOCK)
Where you have ROWLOCK and not NOLOCK.
NOLOCK may not be the best solution, as it could result in incomplete
data, or errors due to data movement.
Have you considered to put the database in READ COMMITTED SNAPSHOT?
(This should work even if the database in compat mode 80.)
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
- Follow-Ups:
- Re: Select statement Causing Table Locks
- From: Erland Sommarskog
- Re: Select statement Causing Table Locks
- References:
- Select statement Causing Table Locks
- From: John Couch
- Re: Select statement Causing Table Locks
- From: Erland Sommarskog
- Select statement Causing Table Locks
- Prev by Date: Re: Linked Server Insert Slow
- Next by Date: RE: Teradata
- Previous by thread: Re: Select statement Causing Table Locks
- Next by thread: Re: Select statement Causing Table Locks
- Index(es):
Relevant Pages
|
