CODEWITHSUNDEEP
sql-server-2008nhibernatedeadlock
Artemoniks
Artemoniks

Reputation: 15

NHibernate query deadlock in case multiple connection

I have next transaction:

  Desc d = new Desc();
  d.Descr = "new";

  _sess.Transaction.Begin();

  _sess.SaveOrUpdate(d);

  var desc = _sess.CreateCriteria(typeof(Desc)).List<Desc>();

  _sess.Transaction.Commit();

This transaction performs next query:

BEGIN TRANSACTION

INSERT

SELECT 

COMMIT TRANSACTION

When I perform this code in two processes I have deadlock, because

1 Process

Perform INSERT and lock Key

2 Process

Perform INSERT and lock key

1 Process wants to perform SELECT and passes in TIMEOUT STATE 2 Process wants to perform SELECT and passes in TIMEOUT STATE

result: deadlock

BD: MS SQL Server 2008 R2

2 questions:

  1. How do me set UPDATE LOCK on All tables what included in transaction

  2. If I use this code:

    Desc d = new Desc(); d.Descr = "new";

    _sess.Transaction.Begin(IsolationLevel.Serializable);

    _sess.SaveOrUpdate(d);

    var desc = _sess.CreateCriteria(typeof(Desc)).List();

    _sess.Transaction.Commit();

Nothing changes.

What does IsolationLevel.Serializable do ?

UPDATE:

I need to get following:

USE Test

BEGIN TRANSACTION

SELECT TOP 1 Id FROM [Desc] (UPDLOCK)

INSERT INTO [Desc] (Descr) VALUES ('33333')

SELECT * FROM [Desc] 

COMMIT TRANSACTION

How do me perform with help NHibernate following:

SELECT TOP 1 Id FROM [Desc] (UPDLOCK)

?

Upvotes: 1

Views: 3322

Answers (1)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

I would change the transaction isolation level to snapshot. This avoids locks when reading data, allows much more concurrency and particularly no deadlocks in read-only transactions.

The reason for the deadlock is following: insert do not conflict with each other. They lock the newly inserted row. The query however is locked out, because it tries to read the newly inserted row from the other transaction. So you get two queries both waiting for the other transaction to complete, which is a deadlock. With isolation level snapshot, the query doesn't care about non committed row at all. Instead of waiting for locks to be released, it only "sees" rows that had been committed. This avoids deadlocks in queries.

Upvotes: 3

Related Questions