NHibernate lock database table to avoid insert "duplicates"

Question

I think that's a common problem but I haven't found any solution, perhaps I'm not searching for the problem correctly in google. In summary, I have a process that inserts multiple rows in a table (among a lot of other things in the same transaction), but the process is executed in multiple threads and multiple servers.

TABLE: COVERAGES
COLUMNS: COV_Id, COV_Description

The description is Unique, but not as a constraint in database (legacy), and I want to avoid to insert duplicate descriptions. I've isolated the search and insert in a independent transaction and I want to lock the table before select and liberate it after "Save" if it not exists.

I would something like that (high level):

{
    this.Lock(Coverage); // Lock table Coverages to avoid select out of this transaction
    Coverage coverage = session.QueryOver<Coverage>().Where(g => g.Description == description).Take(1).SingleOrDefault();
    if (coverage == null)
    {
        this.Save(new Coverage { Description = description });
    }
    return coverage;
};

I can't use lock instruction of C# because the process is executed in multiple servers, and I can't use Lock instruction of NHibernate, because precisely I want to block when I've no results.

I'm using NHibernate 3.3 for SqlServer and Oracle.

Answer 1

I finally implement a semaphore on database to solve the problem. As I mention on my above "discussion" with Frédéric, I need to lock the thread at select to avoid duplicate insertion, the Serializable isolation level, locks on INSERT and throws deadlock exceptions when the insert is called in concurrent calls on SQL Server. By other way on Oracle throws error 08177. 00000 - "can't serialize access for this transaction", or keeps waiting for the end of the other transaction inserting the value duplicated later (see sample sql's below).

So the solution is something like this:

public Coverage CreateCoverageSessionIsolated(string description, out bool isNew)
{
    Coverage coverage = null;
    bool _isNew = false;
    this.ExecuteOnNewSession((session) =>
    {
        this.semphoresDao.LockSemaphore(session, "SMF_COVERAGES");
        coverage = session.QueryOver<Coverage>()
            .Where(g => g.Description == description)
            .Take(1) 
            .SingleOrDefault();
        _isNew = coverage == null;
        if (coverage == null)
        {
            coverage = new Coverage { Description = description };
            this.Save(coverage);
        }
    });
    isNew = _isNew;
    return coverage;
}

I adapt the real code a bit to better comprension.

• ExecuteOnNewSession, starts a new isolated ReadCommitted transaction. So it doesn't interferes with opened transaction, to avoid timeouts on uncontrolled locks and deadlocks and reduces the risk time.
• LockSempahore: Executes a select query, locking specific row.

I've tried it and works fine on SQL Server and Oracle.

EDIT: To check that the solution of Serializable transaction doesn't fit for me I use that simple SQL code on two concurrent transactions executing step by step, side by side:

BEGIN TRAN; -- ONLY FOR SQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COV_ID FROM COVERAGES WHERE COV_DESCRIPTION = 'testcov';
INSERT INTO COVERAGES (COV_DESCRIPTION) VALUES ('testcov');
COMMIT;

Answer 2

You may lock your table with a transaction using IsolationLevel.Serializable.

using (var t = session.BeginTransaction(IsolationLevel.Serializable))
{
    var coverage = session.QueryOver<Coverage>()
        .Where(g => g.Description == description)
        .Take(1).SingleOrDefault();
    if (coverage == null)
    {
        coverage = new Coverage { Description = description };
        session.Save(coverage);
    }
    t.Commit();
    return coverage;
}

For limiting lock contention, this requires that your table has an index on Description and that this index is actually used by the reading query. Otherwise, it will lock the entire table instead of just locking "nearby" Description values. Read more here. For official documentation a bit more complete than .Net Framework one, read here and here.

In case two or more processes (or threads) attempt to perform concurrent conflicting inserts¹, they will all encounter a deadlock. All of them excepted one will be rollbacked as deadlocks victims. The remaining single one will carry on.

The deadlock occurs on the insert, not on the select. All the processes will hang on the insert, all ending rollbacked, excepted one. This ensures no duplicates will be inserted.

This means the complete code for handling that is a bit more elaborated.

while (true)
{
    using (var session = sessFactory.OpenSession())
    {
        try
        {
            using (var t = session.BeginTransaction(IsolationLevel.Serializable))
            {
                var coverage = session.QueryOver<Coverage>()
                    .Where(g => g.Description == description)
                    .Take(1).SingleOrDefault();
                if (coverage == null)
                {
                    coverage = new Coverage { Description = description };
                    session.Save(coverage);
                }
                t.Commit();
                // Breaks the loop by the way.
                return coverage;
            }
        }
        catch (GenericADOException ex)
        {
            // SQL-Server specific code for identifying deadlocks
            var sqlEx = ex.InnerException as SqlException;
            if (sqlEx == null || sqlEx.Number != 1205)
                throw;
            // Deadlock, just try again by letting the loop go on (eventually
            // log it).
        }
    }
}

Note:
1. Conflicting according to the range locked by the DB, not only about the actual value to insert. Having an adequate index is highly recommended for reducing this range. Not having one may cause the whole table to be locked, resulting in a very poor ability to concurrently insert different values.