Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: How to Avoid Deadlocks

    I am conducting stress testing for my website and keep getting deadlocks 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!

  2. #2
    Join Date
    Oct 2003
    Newcastle, Australia
    The bad news - 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. Are you perhaps incrementing 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

    Also, posting your SQL may help

  3. #3
    Join Date
    Aug 2002
    --Satya SKJ
    Microsoft SQL Server MVP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts