锁定数据库表以避免插入“重复项”

Question

我认为这是一个常见的问题，但我还没有找到任何解决方案，也许我没有在谷歌上正确地搜索这个问题。总之，我有一个在表中插入多行的过程(在同一个事务中的许多其他事情中)，但是这个过程是在多个线程和多个服务器中执行的。

TABLE: COVERAGES
COLUMNS: COV_Id, COV_Description

描述是唯一的，但不作为数据库（旧版）中的约束，我想避免插入重复的描述。我已经隔离了搜索并插入到一个独立的事务中，我想在选择之前锁定表，如果它不存在，则在“保存”之后释放它。

我想要这样的东西（高水平）：

{
    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;
};

我不能使用 C# 的锁定指令，因为该过程在多个服务器中执行，并且我不能使用 NHibernate 的锁定指令，因为当我没有结果时，我恰恰想阻止。

对于SqlServer和Oracle，我使用的是NHibernate 3.3。

Answer 1

您可以使用< code>IsolationLevel用事务锁定您的表。可序列化。

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;
}

为了限制锁争用，这要求您的表在Description上有一个索引，并且该索引实际上由读取查询使用。否则，它将锁定整个表，而不是仅锁定“附近”Description值。点击此处阅读更多信息。有关比.Net Framework更完整的官方文档，请阅读此处和此处。

如果两个或多个进程（或线程）试图执行并发的冲突插入¹，它们都将遇到死锁。除了一个将作为死锁受害者回滚。剩下的一个将继续。

死锁发生在插入上，而不是在选择上。所有进程都将挂在插入件上，所有进程都结束回滚，除了一个。这可确保不会插入重复项。

这意味着用于处理的完整代码更加详细。

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).
        }
    }
}

注意:< br> 1。根据DB锁定的范围，不仅仅是关于要插入的实际值的冲突。强烈建议使用适当的索引来缩小这个范围。如果没有，可能会导致整个表被锁定，从而导致并发插入不同值的能力非常差。

Answer 2

我终于在数据库上实现了信号量来解决这个问题。正如我在上面与 Frédric 的“讨论”中提到的，我需要在 select 处锁定线程以避免重复插入，可序列化隔离级别锁定在 INSERT 上，并在 SQL Server 上的并发调用中调用插入时引发死锁异常。通过其他方式在 Oracle 上抛出错误 08177。00000 - “无法序列化此事务的访问”，或者继续等待另一个事务的结束，插入稍后复制的值（请参阅下面的示例 SQL）。

所以解决方案是这样的：

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;
}

我对实际代码进行了一点修改，以更好地理解。

• ExecuteOnNewSession，启动一个新的隔离的 ReadCommit 事务。因此，它不会干扰打开的事务，以避免不受控制的锁和死锁超时，并减少风险时间。
• LockSempahore：执行选择查询，锁定特定行。

我已经尝试过，并且在SQL Server和Oracle上运行良好。

编辑：为了检查可序列化事务的解决方案不适合我，我在两个并行事务上使用简单的SQL代码，逐级并行执行：

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;