Frequent deadlocks incidents without a clue

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

Transact-SQL (2005)

Frequent deadlocks incidents without a clue

Author

Topic

chandu2708
Starting Member

2 Posts

Posted - 2009-04-09 : 07:26:55

Hi People,

Recently after pushing an app into live i've been spending sleep less nights over the deadlock incidents that keep comming without a clue over how its happening. i double checked to see if there are any circular dependencies causing the dead lock but there are none.

Here is the error im getting

Transaction (Process ID 146) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

and here are the details of the process

the error cause is from this operation "updateIdeaRatingById"

update IdeaRatings set
IdeaID = xxxxx,UserID = xxxxx,Rating = xxxxx,CreationDate = xxxxx,
ModificationDate = xxxxxx where ID = xxxxx

this table has a trigger attached to it

TRIGGER [Tr_IdeaRatings_Avg] ON [IdeaRatings]
for INSERT,UPDATE
AS
BEGIN
declare @count int;
declare @rating int;
declare @total_rating float;
set @total_rating = (SELECT SUM(RATING) FROM IdeaRatings with(NOLOCK) where IdeaID = (select IdeaID from inserted));
set @rating = (select RATING from inserted);
set @count = (select count(*) from IdeaRatings with(NOLOCK) where IdeaID = (select IdeaID from inserted));
if(@count <= 1)
BEGIN
update Idea
set rating = @rating
where id = (select IdeaID from inserted)
END
else
BEGIN
update Idea
set rating = @total_rating / @count
where id = (select IdeaID from inserted)
END
BEGIN
update Idea
set PolledVotes = @count
where id = (select IdeaID from inserted)
END
END

the above trigger updates the Idea table which has trigger to insert some log in a table called Log.

Im not sure wheather there is possibility of the dead lock here.

also, wondering weather only circular dependency will cause dead lock? or blocking without a circular dependency could cause a dead lock?

Any help would be appriciated.

Thank you,
chandu

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-04-09 : 07:50:03

Circular dependancies could defuinately cause this problem. What is your maintenance policy for the data? Are you reindexing etc?
Were you getting those problems in the development environment?

Jack Vamvas
--------------------
http://www.ITjobfeed.com

chandu2708
Starting Member

2 Posts

Posted - 2009-04-09 : 08:29:21

quote:
Originally posted by jackv

Circular dependancies could defuinately cause this problem. What is your maintenance policy for the data? Are you reindexing etc?
Were you getting those problems in the development environment?

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Hi Jack,

Im getting these issues not in dev environment but in live. if circular dependencies are causing the problem could you provide me a possiblility on this case here please.

also we dont have much data in the system totally we have not more than 500 records in Ideas table.

Thank you,
chandu.

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-09 : 08:38:37

some ideas you can get from the below links

http://www.pedautreppe.com/post/Detecting-Deadlocks-in-SQL-Server.aspx

http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

Regards
Thiyagarajan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources