July 5, 2012 at 2:42 am
I have one procedure which is called parallely multiple times. Now there is one SELECT [READ] statement from table TableA and other INSERT [WRITE] on the same table TableA. The insert statement is a result of joins with, say, 5 tables named tA, tB, tC, tD, tE. Insert affect just one row.
Now all my operations that is SELECT as well as INSERT are inside a transaction [begin tran] and getting commited or rollback on the basis of @@error.
I am frequently getting deadlocks when running multiple thread of this proc in parallel. In such a short proc with not doing much of processing, what can be the possiblity ?
July 5, 2012 at 2:51 am
What is the reason to keep the select in the transaction? This might be causing the issue.It should be outside of the transaction. What is the isolation level?
Can you post the proc text?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 2:57 am
Also provide the deadlock graph if you have it, if not enable traceflag 1222 or use extendend events or a WMI query to capture the deadlock graph.
July 5, 2012 at 3:03 am
The Isolation level is default that is read committed. I will consider moving the table out of transaction scope. Can't trace now because i'm not sure when the deadlock will occur. It may occur two times a day or once in a month or six months.
July 5, 2012 at 3:09 am
try modifying your select your query with hint as "nolock".
----------
Ashish
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply