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?
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