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

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

Update: Given the continued downvotes I'll expand on this. Frederick is asking how to use locking hints, which are syntax- and implementation-specific details of his underlying database engine, from his ORM layer. This is the wrong level to attempt to perform such an operation - even if it was possible (it isn't), the likelihood it would ever work consistently across all NHibernate-supported databases is vanishingly low.

It's great Frederick's eventual solution didn't require pre-emptive exclusive locks (which kill performance and are generally a bad idea unless you know what you're doing), but my answer is valid. Anyone who stumbles across this question and wants to do exclusive-lock-on-read from NHibernate - firstly: don't, secondly: if you have to, use a stored procedure or a SQLQuery.

Answer 2

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 */