How to prevent Deadlock on SELECT queries?

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

I can't say much about your deadlocks without the queries or a deadlock graph.

Is there any reason why you specify an exclusive table lock?

SQL Server is very good at escalating locks from row or page to table itself.


Is there's an option in MSSQL to do a write lock in a manner to not block the selects?

Yes there is, you can enable READ COMMITTED SNAPSHOT ISOLATION LEVEL.

With this isolation level you can always read what's committed. If another query is writing at that moment you will see the latest snapshot of what was written.

Please read through the MSDN page and see if RCSIL will work for your app.

StackExchange Database Administrators Q#244537, answer score: 7