Inserting and updating and selecting at thousands of times per second

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

• The database has 2-5 simultaneous 1000+ row inserts using Linq to Sql.

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

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.

StackExchange Database Administrators Q#779, answer score: 2