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&lt;> 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.
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.