如何在T-SQL中执行非事务性的插入和更新操作

Question

我在SQL Server T-SQL中有存储过程，从.NET中的事务范围内调用。

在我的存储过程中，我正在对一些审计表进行记录。我向审计表插入一行，然后稍后通过更新的方式填充更多信息。

我发现，如果几个人同时尝试同样的事情，其中1或2个人将成为事务死锁受害者。目前我假设在插入审计表时发生了某种形式的锁定。

我想执行对审计表的插入和更新操作，而不是在执行的事务之外，这样即使事务回滚，审计也会发生。我希望这可以防止出现任何锁定，允许多个人同时执行该过程。

有人可以帮我在T-SQL中完成此操作吗？

谢谢, Rich

更新-我后来发现审计与事务死锁无关，感谢Josh建议使用SQL Profiler跟踪死锁来源。

- Rich

---

你的审计表是否有指向主要“热门”表的外键？ - Mitch Wheat

如果我要插入新行，我会创建一个“插入”审计记录。然后，我创建包括从审计表中的键以显示哪个受审计的操作创建了该行的新行。接下来，我会使用新行的ID更新审计记录，以显示已创建什么内容。没有关系。 - Rich

好的，我刚刚谷歌搜索了"SQL事务抑制"，这个问题是第三个链接，我觉得很有趣。 - JoshBerke

你正在运行哪个隔离级别？ - JoshBerke

Answer 1

事务范围支持Suppress：

using (TransactionScope scope = new TransactionScope())
{

    // Transactional code...


    // Call a SQL stored procedure (but suppress the transaction)
    using (TransactionScope suppress = new TransactionScope(TransactionScopeOption.Suppress))
    {
        using (SqlConnection conn = new SqlConnection(...))
        {
            conn.Open();
            SqlCommand sqlCommand = conn.CreateCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "MyStoredProcedure";
            int rows = (int)sqlCommand.ExecuteScalar();
        }
    }

    scope.Complete();

}

但是我必须质疑为什么日志记录/审计会在事务之外运行？如果事务被回滚，您仍将提交审核/日志记录，并且这可能不是您想要的。

您没有提供有关如何记录日志的信息。您的审计表是否具有指向主要活动表的外键？如果是，请删除外键（假设审核记录仅来自“已知”应用程序）。

- Mitch Wheat

---

嗨，我想在 T-SQL 中抑制事务而不是 .NET。没有外键关系，但我记录了涉及到和从审计表中的表的键。 - Rich

Answer 2

你可以将审核记录保存到表变量中（不受事务影响），然后在SP结束时（超出事务范围）将行插入审核表中。

然而，听起来你试图解决症状而非问题。你可能想要追踪死锁并修复它们。

- BankZ

Answer 3

我有一个类似的需求，需要将错误记录到错误日志表中，但发现回滚操作会将它们清除。

通过将先前插入的错误记录弹出到一个表变量中，调用Rollback，然后将记录推回（插入）到表中来解决了这个问题。

这种方法非常有效，但代码很混乱，因为它必须是内联的。如果将ROLLBACK放入存储过程中，则会出现“EXECUTE后事务计数...”错误。

- Y-Mi Wong

Answer 4

你为什么要更新审计表？如果你只是在进行插入操作，那么你可能会帮助防止锁升级。此外，你是否检查了死锁跟踪以确定你到底是死锁了什么？

你可以通过启用跟踪标志1204或运行SQL Profiler来实现这一点。这将为你提供详细的信息，让你知道死锁的类型（锁定、线程、并行等）。

请查看这篇关于检测和结束死锁的文章。

另一种审计的方法是完全与业务交易分离，通过将所有日志事件发送到应用程序层的队列中，这将最小化日志记录对你的业务交易的影响，但对于现有应用程序来说可能是非常大的。

- Josh

---

我创建一个审计记录，然后创建行，然后使用行的 ID 更新审计记录以显示所创建的内容。这使我可以将审计记录的 ID 放入行中，也显示哪个审计事件导致了它。 - Rich

为什么不在插入后立即创建审计记录呢？我有一种感觉，这可能是你的问题。 - JoshBerke

因为记录本身不会包含创建它的审计记录的信息，所以我喜欢双向链接。 - Rich

谢谢Josh，这是对我最有用的答案。通过使用SQL Profiler，我可以看到审计并没有引起锁定，实际上是由于一个构造不良的事务导致了另一个表区域的问题。通过重新排列事物，一切都好了。 - Rich