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