I have a multi-session DB. Few sessions inserting many rows to the DB. and one session selects from this table.
Sometimes I'm getting a deadlock on the SELECT query and I don't understand why.
For example: Assume this table
Create Table t1([id] int identity(1,1), [column] varchar(max))
Insertions go like this: (I'm doing it from an 'after' trigger)
Begin Transaction Insert into t1 WITH (TABLOCKX) ([column]) values ('value 1'), ('value 2') ... ('value N') Commit Transaction
The selection goes like this:
Select TOP 10 [id],[column] from t1 order by id
The lock on the insertions meant to keep the sequence of session insertions in the DB.
Each time I lock only one table in a transaction. I've tried to recover the deadlock with one session inserting and second one selecting but I've failed. Before I've added the TABLOCKX I've never got the deadlock.
So my questions are:
- How come a deadlock possible? (Maybe in a nested transaction?)
- Is there's an option in MSSQL to do a write lock in a manner to not block the selects?
- Any idea to prevent deadlock? (but keep session insertion sequence in the DB without changes...)