How to lock objects before update using NHibernate lock modes?

Question

So first let me state what I'm trying to accomplish. I have a table that is populated with jobs. And there is a web service with a method that allows for the alteration of job data called SaveJob. This method retrieves the job and all it's data, runs validations (which require some db queries of their own to other tables) on the new data and then saves it back to the database. It's somewhat slow. About a second.

What happens is occasionally two SaveJob calls will be made close together on the same job and they'll run over each other. Now currently this is the only web server but I would like to have my solution web farm compatible so even though I'm aware of how to solve this issue using a singleton I'd prefer to have the locks handled by the database.

The question is can I use NHibernate and SQL Server database locks to get a second SaveJob call to block when it tries to read a Job that is already being altered by another SaveJob call?

I believe the answer is yes but I'm not really sure how to go about it. I've read the documentation about the ISession.Lock() and I believe what I need is to use the NHibernate.LockMode.Upgrade

My next question is when is this lock released?

I assume that it is released when the transaction is committed but I can't find any documentation that explicitly states this.

However if that's the case then is there a way for me to open another transaction and run some queries without closing the transaction I've started when I grabbed the job in the first place? Or do I have to do this all in one transaction?

Answer 1

First, I would advise against using a single transaction across multiple threads since the ISession itself is not thread safe.

When you use LockMode.Upgrade on the session, or on session.Get(), NHibernate will issue a select with (rowlock) statement (details might depend on database type and configuration) when you retrieve the entity. If the object was previously retrieved by another thread, the current thread will wait until the lock is released or the timeout expires. The lock itself is release when the update statement is executed on transaction.Commit(). Using LockMode.UpgradeNoWait offers similar functionality except it does not wait and simply fails when the lock is taken.

This Ayende's post also has some info on what SQL NHibernate emits on session.Lock().

You can read on the different types of locks on the NHibernate Documentation Chapter 11, Section 6.