Inserting and updating and selecting at thousands of times per second

Question

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

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED

   end

   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.

Answer 1

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.

Answer 2

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.

Answer 3

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!