Locking issues

Question

Locking issues

martin-210793

Ten Centuries

Points: 1083
More actions
February 19, 2008 at 5:11 am

#154137

Hi Folks,
I am in the middle of changing a process that serially processes orders to one that processes them in a concurrent manner. However one of the steps is causing a deadlock.
The step selects all the items from the order, loads them into a table variable performing validation etc. and then inserts the contents from the table variable into the permanent table. However, this step is causing a deadlock in my code.
There are currently four instances of the stored procedure performing this for an order at the same time and out of a record set of 30 a deadlock is being caused on about 25% - 40% of the records.
The step of creating the order (i.e. creating a new order ) is working fine, along with other steps of creating billing information. Its just this one step of having to gain a lock for multiple record inserts that is causing the issue.
Has anyone come across this before. Could anyone share some light on how I can go to trying to fix this. I have tried encapsulating the insert into a try catch which is in turn within an inline function, thus in my catch statement if I catch and error of 1205(deadlock) I am trying to call the function again and resubmit the insertion. However when I go to do this I get the error - The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.. Error number 3930. Which baffles me. if anyone has some information on this I would be very grateful. thanks in advance.
Martin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 1

Why don’t you use SQL Server profiler to identify the deadlock first? Then analyze the deadlock graph and you can see what the causes are. For more details refer below articles;

For SQL Server 2000,

http://www.code-magazine.com/article.aspx?quickid=0309101&page=4

For SQL Server 2005,

http://www.informit.com/articles/article.aspx?p=686168&seqNum=6

Susantha

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Refer the following link:

http://support.microsoft.com/kb/271509

http://support.microsoft.com/kb/832524

http://support.microsoft.com/kb/q169960/

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 3

What kind of indexes do you use on the order table? Is the identity field (OrderID, I suppose) clustered or non-clustered? I think the reason is contention on page allocation when multiple processes inserting records. Again, using profiler to capture lock:deadlock chain, lock:deadlock and deadlock graph will be insightful.

martin-210793 Ten Centuries Points: 1083 More actions · Answer 4

Hi Folks,

Thanks for the responses. There are a number of indexes on this table 7 in total so thats why its prob taking so long. When I run this using two instances its running ok. I think for now I will use two and then maybe further down the line try and solve this locking issue.

I haven't had the chance to run the profiler yet. I hope to do this at some point today.

Thanks once again for the responses folks,

Martin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

7 indexes shouldn't slow an insert down that much.

Can you post the code for that step, the table structure and the index definitions please? Also check to see if there are any triggers on the table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Deadlock troubleshooting via forums is a painful and often fruitless pursuit. Too much back-and-forth and incomplete information transferal. Have your best database resource review the 3 blog posts started here: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. That is the DEFINITIVE TEXT on deadlock investigations as far as I am concerned.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

martin-210793 Ten Centuries Points: 1083 More actions · Answer 7

Hi Folks,

I have resolved this by taking all of the steps out of a transaction that would perform an antomic update covering all the individual steps. I know that this may lead to dirty data but I have a status flag that gets set when everything finishes. if the insertion of the purchaseorder items fails I am going to cascade the deletes and notify the required personnel so that they can resubmit the order once they fix the changes.

THanks again for all the replies. Keep up the good work.

M