July 16, 2014 at 11:32 pm
I have a procedure, which process multiple documents and finance postings. So when i execute it will take 1 mins to complete.All the queries in procedure have proper index and well tuned.My problem is when multiple users access the procedure parallel, dead lock happens.How do i avoid dead lock ?
July 17, 2014 at 1:20 am
Deadlocks are usually caused by a combination of poor performance and problematic coding choices. Not being able to see your structures, code or the output of the deadlock graph, all I can do is make some suggestions. Make sure you're accessing the objects in your database in the same order every time. If you SELECT from TableA,then UPDATE TableB, if you also SELECT from TableB and UPDATE TableA, you might see a deadlock situation arise. Tune the queries. I know you say it's tuned, but there are frequently additional tuning mechanisms available to make it even faster.
If you look at the system_health extended event session you can get the deadlock graph which will tell you where the deadlock is occurring. That will help you narrow down where the problem lies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2014 at 2:39 am
Thank u for your replay.
As you said, in my proc TABLE A or TABLE B frequently update or selected.
when user1 runs the proc, TABLE A or TABLE B inserted/update/selected on guid1.
when user2 runs the proc, TABLE A or TABLE B inserted/update/selected on guid2.
even both users refers to inserted/update/selected on separate guid, i am getting dead lock.
July 17, 2014 at 4:55 am
Can you post the deadlock graph from the system_health extended event or by switching on traceflag 1222
dbcc traceon (1222,-1)
July 17, 2014 at 4:57 am
You don't have to turn on a traceflag any more with SQL Server for deadlocks. The full deadlock graph is available in the system_health extended event session.
Posting the deadlock graph will help us point out where the problems lie.
Just because two users are accessing two different rows doesn't mean that they're not locking on the page of the index/table where the deadlock is occurring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply