March 9, 2015 at 6:22 am
Hi,
I am getting following deadlock message. Can any one explain what is happening in below deadlock?? Also please suggest what can I do to get rid of this deadlock?
March 10, 2015 at 12:27 am
Please help me
March 10, 2015 at 3:34 am
Resolving deadlocks is usually a long-duration task that requires from you to overview more aspects.
What is the number of records that are you deleting in some of the tables? it does matter especially when you have a concurrent system. If you delete big number of rows than one good approach is to do the delete operations in batches each processing small amount of data.
I'm sending you an example how it looks like on another environment:
if object_id('tempdb..#tmp_delete_data') is not null
drop table #tmp_delete_data
create table #tmp_delete_data(Id int identity(1,1) primary key, MatchId int, PlayerId int)
declare @cnt int=0
declare @maxCnt int=0
insert into #tmp_delete_data(MatchId,PlayerId)
select mpo.MatchID,mpo.PlayerID
from dbo.MatchPlayerOdds mpo
where exists (select * from dbo.Matches (nolock) m where m.MatchID=mpo.MatchID and MatchDate < (getutcdate() - 7))
select @maxCnt = count(*) from #tmp_delete_data
while (@cnt<@maxCnt)
begin
set @cnt +=100
delete top(100) mpo
from dbo.MatchPlayerOdds mpo
inner join #tmp_delete_data t on mpo.MatchID = t.MatchId and t.PlayerId = mpo.PlayerID
where t.Id between @cnt-100 and @cnt
end
if object_id('tempdb..#tmp_delete_data') is not null
drop table #tmp_delete_data
Igor Micev,My blog: www.igormicev.com
March 10, 2015 at 10:24 am
Thanks for the reply, no I am not deleting large number of rows. This SP deletes only 59 rows.
I am seeing this in profiler
Parallel qury worker thread was involvd in a deadlock
March 10, 2015 at 1:47 pm
This link is one very useful - https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/.
Igor Micev,My blog: www.igormicev.com
March 10, 2015 at 2:34 pm
Often a multi-threaded app may cause very frequent deadlock conditions. In this case the app needs rewritten. Deadlocks are a normal and expected condition and are not errors. Apps must be written to detect and resubmit the transaction that was rolled back due to deadlocking.
March 11, 2015 at 12:21 am
Thank you so much for the reply Bill
but can you please explain me what does this message means?
Transaction (Process ID 51) was deadlocked on lock | comunication buffer resorces with another process and has been chosen as the deadlock victim. Rerun the transaction.
March 11, 2015 at 10:01 am
A deadlock is pretty simple to understand. If you and I want to rent two movies, Matrix1 and Matrix2, and each of us grabs one of the movies, we will be at a standoff. A librarian needs to step in and take a movie from one of us and give it to the other person. One person wins and one loses. After a person watches both movies, then the other can retry to get both movies.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply