Author Topic: How to Avoid Deadlocks  (Read 190 times)

0 Members and 1 Guest are viewing this topic.

Offline GBTalk

  • Full Member
  • *
  • Posts: 184
  • Karma: +0/-0
    • View Profile
How to Avoid Deadlocks
« on: August 24, 2008, 08:35:42 AM »
We are currently conducting stress testing for a website and we are getting deadlock errors with the following message when one process is adding about 100 records per second and another process is trying to access the data:

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

What do I need to do in my stored procedures to avoid this?


I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?

Any advise would be much appreciated. Thanks!

GB

Techronnati | where technology never sleeps

How to Avoid Deadlocks
« on: August 24, 2008, 08:35:42 AM »

Mountain View

Offline h2obubbler

  • Full Member
  • *
  • Posts: 228
  • Karma: +0/-0
  • Life is like a box of chocolate.
    • View Profile
Re: How to Avoid Deadlocks
« Reply #1 on: August 24, 2008, 04:13:17 PM »
What i know is that you cant completely stop deadlocks

A few things to think about
- why arent you using sql server\'s IDENTITY feature for primary key generation? I\'m pretty sure you\'ll have less deadlocks this way than by using your own custom table to store/distribute primary key values

- try to keep your transactions as short as possible. Did you attemt to increment the value in a row at the start of the procedure, doing some more sql for a while, and then commiting the transaction?

- it may be viable to use the NOLOCK table hint for your process that reads data. It depends on whether this is OK for your app. Have you explicitly set the transaction isolation for your database from READ_COMMITTED to something higher? This will cause more deadlocks.

You might want to search this forum and the net for SQL+Server+deadlock. Lots of people heaps smarter than me have had some fairly exhaustive discussions about limiting deadlocks.

 

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.