NHibernate with SQL Server - How to Process Next New type of Environment

Question

I have an application (ASP.NET MVC) that uses a Next New method to get the next new piece of work. The user clicks on a Next New button, and my NHibernate code would go fetch the next piece of work with an Active status. As the number of users has increased, we have seen an issue arise where 2 users can get the same piece of work. This occurs even though I have wrapped my SELECT and UPDATE calls into a transaction.

I also tried setting the transaction to an IsolationLevel of RepeatableRead, but this would just throw a TransactionException when 2 users try to access the data at the same time.

Here is the process I am currently using:

1. Get the next work object
2. Update the object to show it is pended and save to the db
3. Display the work to the user

Here is the code I am currently using:

    using (var txn = _repository.Session.BeginTransaction(IsolationLevel.RepeatableRead))
    {
        try
        {
            newAccount = _repository.FindFirstWithLock(criteria, LockMode.Read);

            newAccount.QueueStatus = pendingStatus;
            _repository.Save(newAccount);    

            txn.Commit();
        }
        catch (Exception)
        {
            txn.Rollback();
            throw;
        }
    }

The criteria is a DetachedCriteria object which just looks for accounts which are active and sorts descending by a date property.

Does anyone have any suggestions on how I could make this work?

Answer 1

Update:

I solved this problem by sending through a custom T-SQL Statement, locking the current row, and reading past the locked row if one was already locked:

    using (var txn = _accountRepository.Session.BeginTransaction())
    {
        try
        {

            updateResult = _accountRepository.Session.CreateSQLQuery(
                SqlQueries.GetNextQueueItem(orderByClause, strategyId, languageId, tomorrow, userName)).ExecuteUpdate();
            txn.Commit();
        }
        catch (Exception)
        {
            txn.Rollback();
            throw;
        }
    }

The SQL Query looks like this:

UPDATE       ACCOUNTS 
            SET          QUEUE_STATUS_ID = 2 
                        ,OWNED_BY = '[username]' 
                        ,OWNERSHIP_DATE = GETDATE() 
            FROM        ACCOUNTS   
            WHERE   CUSTOMER_ACCOUNT_ID = 
                            ( 
                                SELECT      TOP(1) CUSTOMER_ACCOUNT_ID 
                                FROM        FIT_PAYPAL_ACCOUNTS  WITH(ROWLOCK, READPAST, UPDLOCK) 
                                WHERE           STRATEGY_ID = 999
                                            AND NEXT_WORK_DATE < '3/1/2010' 
                                            AND STATUS_ID = 0