NHibernate, TransactionScope and locking

Question

I am trying to use TransactionScope with NHibernate in order to call several methods in one transactions. Data repository methods are like this:

public virtual void Save(T dataObject)
{
    try
    {
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
        {
            this.session.SaveOrUpdate(dataObject);
            scope.Complete();
        }
    }
    catch (Exception ex)
    {
        bool rethrow = ExceptionPolicy.HandleException(ex, "Data Layer Policy");
        if (rethrow)
        {
            throw;
        }
    }
}

public T GetByNumber(string documentNumber) { T document = null; try { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead })) { document = this.Session.CreateCriteria(typeof(T)) .Add(Restrictions.Eq("Number", documentNumber)) .UniqueResult(); scope.Complete(); } } catch (Exception ex) { bool rethrow = ExceptionPolicy.HandleException(ex, "Data Layer Policy"); if (rethrow) { throw; } } return document; }

I wanted to test row/table locking in transactions so I made several unit tests and some console applications. Here is code from these console applications:

Application which does update:

const string DocumentNumber = "386774321";
Random randomGenerator = new Random();
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
{
    using (BillingDocumentRepository billingDocumentRepository = new BillingDocumentRepository())
    {
        BillingOrderData orderData = billingDocumentRepository.GetByNumber(DocumentNumber);
        orderData.Notes = randomGenerator.Next().ToString();
        Console.WriteLine(string.Format("SECOND: {0}: Updated notes to {1}.", DateTime.Now.ToString("HH:mm:ss.fffff"), orderData.Notes));
        Console.WriteLine(string.Format("SECOND: {0}: Updating order.", DateTime.Now.ToString("HH:mm:ss.fffff")));
        Console.WriteLine(string.Format("SECOND: {0}: Going to sleep for 10000ms.", DateTime.Now.ToString("HH:mm:ss.fffff")));
        Sleep(10000); // My custom sleep method because I didn't want to use Thread.Sleep for simulating long transaction
        billingDocumentRepository.Save(orderData);
    }
    Console.WriteLine(string.Format("SECOND: {0}: Going to sleep for  10000ms.", DateTime.Now.ToString("HH:mm:ss.fffff")));
    Sleep(10000);
    Console.WriteLine(string.Format("SECOND: {0}: Completing transaction.", DateTime.Now.ToString("HH:mm:ss.fffff")));
    scope.Complete();
}

Application which reads the same row in database:

while (true)
{
    using (BillingDocumentRepository repository = new BillingDocumentRepository())
    {
        Console.WriteLine(string.Format("MAIN: {0}: Getting document.", DateTime.Now.ToString("HH:mm:ss.fffff")));
        BillingOrderData billingOrderData = repository.GetByNumber("386774321");
        Console.WriteLine(string.Format("MAIN: {0}: Got order with notes {1}.", DateTime.Now.ToString("HH:mm:ss.fffff"), billingOrderData.Notes));
        Sleep(1000);
    }
}

Problem is that first transaction (which updates row) doesn't lock row for reading at any moment. Second application is reading that row all the time with old value before scope.Complete() and than new value after that. How can I achieve locking with this model?

Answer 1

You should lock when reading. Locking later is "too late":

 document = this.Session.CreateCriteria(typeof(T))
            .Add(Restrictions.Eq("Number", documentNumber))
            .SetLockMode(LockMode.Upgrade)
            .SetTimeout(5)
            .UniqueResult();

Or:

 var doc = session.QueryOver<BillingDocument>()
               .Where(c => c.Number== "2233445")
                .Lock()
                .Upgrade
                .UnderlyingCriteria.
                 SetTimeout(5).
                 List().
                 FirstOrNull() as BillingDocument;

Answer 2

There is a session.Lock(object) method.

When you call session.Save(object), NHibernate isn't doing anything in the database until it gets flushed.

Flushing is done (depending on the flush mode, which is usually AutoFlush)

• before queries (except Get and Load)
• when calling flush explicitly
• when committing the transaction (if the connection is created by NH I think)

When the session is flushed, the actual update, insert and delete operations are done on the database and locks are set.

In SQL Server, when the lock is set, the reading transaction is waiting until commit of the updating transaction. When it commits, it reads the committed values (when you are in "Read Committed" isolation).