NHibernate: exclusive locking

Question

In NHibernate, I want to retrieve an instance, and put an exclusive lock on the record that represents the retrieved entity on the database.

Right now, I have this code:

With.Transaction (session, IsolationLevel.Serializable, delegate
{
    ICriteria crit = session.CreateCriteria (typeof (TarificationProfile));

    crit.SetLockMode (LockMode.Upgrade);

    crit.Add (Expression.Eq ("Id", tarificationProfileId));

    TarificationProfile profile = crit.UniqueResult<TarificationProfile> ();

    nextNumber = profile.AttestCounter;

    profile.AttestCounter++;

    session.SaveOrUpdate (profile);
});

As you can see, I set the LockMode for this Criteria to 'Upgrade'. This issues an SQL statement for SQL Server which uses the updlock and rowlock locking hints:

SELECT ... FROM MyTable with (updlock, rowlock)

However, I want to be able to use a real exclusive lock. That is, prevent that others can read this very same record, until I have released the lock. In other words, I want to be able to use an xlock locking hint, instead of an updlock.

I don't know how (or even if) I can achieve that .... Maybe somebody can give me some hints about this :)

If it is really necessary, I can use the SQLQuery functionality of NHibernate, and write my own SQL Query, but, I'd like to avoid that as much as possible.

Answer 1

You could use isolation level "repeatable read" if you want to make sure that values you read from the database don't change during the transaction. But you have to do this in all critical transactions. Or you lock it in the critical reading transaction with an upgrade lock.

Answer 2

If all you read are done with a IsolationLevel of Serializable and all the write are also done with a IsolationLevel of Serializable I don't see why you need to do any locking of database rows your self.

So the serialize keeps the data safe, now we still have the problem of possible dead locks....

If the deadlocks are not common, just putting the [start transaction, read, update, save] in a retry loop when you get a deadlock may be good enough.

Otherwise a simple “select for update” statement generated directly (e.g. not with nhibernate) could be used to stop another transaction reading the row before it is changed.

However I keep thinking, that if the update rate is fast enough to get lots of deadlocks a ORM may not be the correct tool for the update, or the database schema may need redesigning to avoid the value that has to be read/written (e.g calculation it when reading the data)

Answer 3

I doubt it can be done from NHibernate. Personally, I would use a stored procedure to do what you're trying to accomplish.

Answer 4

A HQL DML query will accomplish your update without needing a lock.

This is available in NHibernate 2.1, but is not yet in the reference documentation. The Java hibernate documentation is very close to the NHibernate implementation.

Assuming you are using ReadCommitted Isolation, you can then safely read your value back inside the transaction.

With.Transaction (session, IsolationLevel.Serializable, delegate
{
    session.CreateQuery( "update TarificationProfile t set t.AttestCounter = 1 + t.AttestCounter where t.id=:id" )
        .SetInt32("id", tarificationProfileId)
        .ExecuteUpdate();

    nextNumber = session.CreateQuery( "select AttestCounter from TarificationProfile where Id=:id" )
        .SetInt32("id", id )
        .UniqueResult<int>();
}

Depending on your table and column names, the generated SQL will be:

update TarificationProfile
set    AttestCounter = 1 + AttestCounter
where  Id = 1 /* @p0 */

select tarificati0_.AttestCounter as col_0_0_
from   TarificationProfile tarificati0_
where  tarificati0_.Id = 1 /* @p0 */