4

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:

  1. How come a deadlock possible? (Maybe in a nested transaction?)
  2. Is there's an option in MSSQL to do a write lock in a manner to not block the selects?
  3. Any idea to prevent deadlock? (but keep session insertion sequence in the DB without changes...)
Improve this question
3
  • 4
    Can you upload the deadlock graph file contents please? This describes the deadlock and as such we need this to diagnose the issue
    – George.Palacios
    Aug 5, 2019 at 9:06
  • 3
    Maybe worth reading this: minimal reproducible example
    – George.Palacios
    Aug 5, 2019 at 9:08
  • 2
    There aren't really nested transactions in SQL Server. You can say BEGIN TRANSACTION twice in a row, but it doesn't mean you have two transactions (in spite of what @@TRANCOUNT tells you). I've never used an explicit transaction inside a trigger and you shouldn't need that (especially if the trigger was fired by a single statement that itself is an implicit transaction). We'll need the deadlock .xdl (which you can get from system_health) and the rest of the trigger code to provide advice, but off the bat, TOP without ORDER BY? Do you not care which TOP 10? Also look into RCSI.
    – Aaron Bertrand
    Aug 5, 2019 at 11:40

1 Answer 1

Reset to default
7

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.