April 11, 2008 at 9:02 am
I have an application with many processes running simultaneously the query below to verify relations between accounts. As expected this happen quite often – in active moment, this could be 30-40 times a minute.
In this case I get deadlocks on this statement and one of them is selected as victim not allowing the transaction to complete which causes problems that I’m trying to solve.
The example of the table structures and indexes are given below.
I also tried to change the clustered index to be not on the ID column but one of the other 2 indexes – not much better.
In my traces it looks that the deadlocking occurs in between index and data pages as in most cases the data in the IN clause will be the same.
I was wondering for any idea on possible way to optimize the query because at this point I cannot change the table structures.
Thanks a lot for the help.
Mj
select sr.acct_id, sr.related_id, sr.other_filed1, sr.other_filed2
from relations sr , account e where sr.acct_id in (1,2,3,4,5) and sr.related_id = e.acct_id and e.acct_type in (1,2,3)
union all
select sr.related_id, sr.acct_id, sr.other_filed1, sr.other_filed2
from relations sr , account e where sr.related_id in (1,2,3,4,5) and sr.acct_id = e.acct_id and e.acct_type in (1,2,3)
create table relations ( insert_relation_id bigint not null, acct_id bigint not null, related_id bigint not null, other_filed1 varchar (100),other_filed2 varchar (100))
create table account (acct_id bigint not null, acct_type bigint not null, acct_num varchar (100) not null, acct_desc varchar (512))
ALTER TABLE dbo.relations ADD CONSTRAINT PK_RELATION PRIMARY KEY CLUSTERED(related_id)
CREATE UNIQUE INDEX relations1 ON dbo.relations(acct_id, related_id)
CREATE INDEX relations2 ON dbo.relations(related_id,acct_id )
April 11, 2008 at 1:27 pm
Multiple concurrent selects shouldn't cause a deadlock. Are you sure that's what's causing it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 3:21 pm
Absolutely sure - This is the error returned to the applciation:
[Microsoft][SQL Native Client][SQL Server]Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Thanks a lot, mj
April 11, 2008 at 5:11 pm
Any chance you have any trace output from the deadlock? If you enable trace flag 1222 and post the info from the logs here it would be very helpful.
April 13, 2008 at 9:23 am
Can you try with giving WITH NOLOCK with the tables used in the query?
April 14, 2008 at 7:01 am
The reason I think it's something other than the select statement causing this is because of what Books Online says about Select statements and Locks.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
As per the first part of the first sentence, they allow concurrent transactions to Select a resource.
It seems to me that there must be something trying to update/delete the same data at the same time. That's why you need a deadlock trace, to find out what else is going on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 7:52 am
Selects by themselves cannot cause deadlocks at the default isolation level. A deadlock requires that two connections are holding and wanting locks on resources such that each connection wants a conflicting lock on a resource that is locked by the other one.
Selects by default take shared locks and do not conflict with other shared locks.
Are you using an isolation level other than the default (which is read committed)? Are you using implicit transactions?
Please switch either traceflag 1204 or traceflag 1222 on
(either DBCC TRACEON (1204,-1) or DBCC TRACEON (1222,-1))
and let us have a look at teh resulting deadlock graph. It will be written into the SQL error log.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply