I have a table that gets inserted, updated and selected from thousands of times within one second. I am having deadlock issues though.

  1. The database has 2-5 simultaneous 1000+ row inserts using Linq to Sql.
  2. 40 times per second, there is also a select statement from that table, and if a condition is true (95% of the time it is), an update happens with code similar to this:

    create procedure AccessFile (@code, @admin) AS

    declare @id int, @access datetime, @file string

    select @id=Id, @accessed = accessed, @file = file from files where code = @code

    IF @admin<> 0 IF @accessed is null begin


    update files set accessed = getdate() where id = @id



    select @id as Id, @file as File

It seems as though it is the updates conflicting with the inserts that are causing the deadlocks.

The example is a 1 to 1 with the stored procedure, only difference is the names. Assume the 1 and 2, regardless of the stored procedures names.

  • 1
    Can you give us your table structure? Columns, keys, indexes? Jan 21, 2011 at 22:57
  • is it SQL Server? Oracle? MySQL?
    – eiefai
    Jan 21, 2011 at 23:02
  • @eric - Very simple: ID (PK; bigint), Code (Index, Unique;varchar(16)), Accessed (datetime), Created (datetime), File (varchar(48)) Jan 21, 2011 at 23:02
  • @eiefai - MSSQL 2008 R2 Jan 21, 2011 at 23:03
  • 2
    You can get some clue here, in this question: dba.stackexchange.com/questions/126/… It's about deadlocks and how to catch and treat them.
    – Marian
    Jan 21, 2011 at 23:05

3 Answers 3


Change the updates to use WITH (ROWLOCK). That will reduce the locks from page level to row level. You can try the same on the inserts as well.

  • I have updated both the inserts and updates to use with(rowlock), but it hasn't decreased the amount of deadlocks. If i kill the inserts (turn off the button that allows our users to insert), the deadlocks disappear. Jan 24, 2011 at 15:16
  • 2
    Try giving snapshot isolation level a try. I'll take same changes to the code that is doing the inserts (unless they are done by a stored procedure). It might help, but it'll increase the tempdb usage by a lot. BTW, changing the isolation level to read uncommitted for an update won't do anything. I'm assuming that the ID column is your clustered index?
    – mrdenny
    Jan 24, 2011 at 23:25

You could also try at the beginning of the proc, setting isolation level to SNAPSHOT. More info available at: http://msdn.microsoft.com/en-us/library/ms173763.aspx

You will incur some cost in tempdb for the row versioning.


I would redesign the applications so that rows are saved in batches. Typically batches of 500-1000 rows work well for me, but you need to run your own tests. Before saving a batch, I would serialize my batch updates using sp_getapplock.

Surely this serialization slows modifications down a little bit, but saving in batches more than compensates for it, so overall this works much faster thgan saving rows one by one.

Also I would run my selects with SNAPSHOT isolation level so that they are npot blocked by modifications.

That done, you may never have any deadlocks at all - we do not have deadlocks in our mixed load system, and so can you.

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.