如何在MS SQL Server上最小化追加表锁定的建议？

Question

我正在编写一些日志/审计代码，这些代码将在生产环境中运行（不仅仅是在错误抛出或开发期间）。阅读完Coding Horror关于死锁和日志记录的经验后，我决定寻求建议。（Jeff的“不记录日志”的解决方案对我不起作用，这是法律强制要求的安全审计）

是否存在适当的隔离级别来最小化争用和死锁？我可以添加哪些查询提示到插入语句或存储过程中吗？

我非常关心事务完整性，除了审计表以外的所有内容。想法是如果有一些条目失败，那么所记录的内容会非常多，这并不是一个问题。如果记录日志停止了其他某些交易– 那就糟糕了。

我可以记录到数据库或文件中，虽然记录到文件中不太可取，因为我需要能够以某种方式显示结果。记录到文件中会（几乎）保证日志记录不会干扰其他代码。

- MatthewMartin

Answer 1

一个普通的事务（例如 READ COMMITTED）插入已经做了“最小”的锁定。插入密集型应用程序不会在插入时发生死锁，无论插入与其他操作的顺序如何混合。最坏的情况是，插入系统可能会在插入发生的热点处引起页面锁竞争，但不会发生死锁。

要像Jeff所描述的那样导致死锁，必须有更多的因素参与，比如以下任何一个：

• 系统正在使用更高的隔离级别（那么他们就应该有所准备并且值得拥有）
• 他们在事务期间从日志表中读取数据（因此不再是“仅追加”）
• 死锁链涉及应用程序层锁（即 log4net 框架中的 .Net lock 语句），导致无法检测到死锁（即应用程序挂起）。考虑到解决问题需要查看进程转储，我想这就是他们遇到的场景。

因此，只要您在 READ COMMITTED 隔离级别事务中仅记录插入操作，您就是安全的。如果您预计会出现与 SO 相同的问题（即涉及应用程序层锁的死锁），则无论是在单独的事务中记录还是在单独的连接中记录，都无法解决该问题。

- Remus Rusanu

---

只要你在READ COMMITTED隔离级别的事务中仅进行插入日志操作，你就是安全的。但这并不完全正确。如果你参与了一个大型事务，在尝试向日志表插入内容时，可能会阻塞整个事务（如果由于运行某些疯狂的报表或其他原因而导致日志表被阻塞）。此外，这也会增加死锁的风险！ - Sam Saffron

不，这是完全不正确的。无论在事务中做什么，插入（INSERT）都不会在（读取已提交）报表后面阻塞。并发插入也永远不会出现在死锁链中，因为它们既不会相互阻塞，也不会被SELECT语句所阻塞。可以阻止插入的因素包括：覆盖插入点的范围S锁（即可序列化报表），对表进行的S锁定（即从可重复读报表升级），或X锁（即某些“追加”之外的活动，例如升级到表X锁的更新操作）。 - Remus Rusanu

向带有聚集索引的表中插入数据，在读提交模式下会在页面上获取IX锁，以及在键上获取X锁。如果它无法在页面上获得IX锁，它将等待；如果它无法在键上获得X锁，它也将等待。SELECT语句需要共享锁。锁可能会发生冲突。死锁可能存在。详见：http://msdn.microsoft.com/en-us/library/ms186396.aspx - Sam Saffron

S锁与任何意向锁的冲突只会在升级场景中发生，而S升级只会在高隔离级别下发生。插入的X可以仅与范围锁冲突（因为它是一个新行，新键）。再次强调：仅追加记录日志不会导致死锁问题。作为额外奖励，它也不会与读提交报告发生冲突。 - Remus Rusanu

@SamSaffron和Remus，是否有可能构建一个简单的测试来确定行为？ - Pacerier

Answer 2

如果您不关心日志表的一致性，为什么不从单独的线程执行所有的日志记录呢？在记录日志之前，我可能不会等待事务完成，因为日志可以在诊断长时间运行的事务时起到关键作用。此外，这使您能够查看回滚的事务所做的所有工作。在记录线程中获取堆栈跟踪和所有日志数据，在有新的日志消息时将其放入队列中，并在一个事务中将其刷新到数据库中。

最小化锁定的步骤：

- （关键）在主线程/连接/事务之外执行所有对日志表的追加。 - 确保您的日志表具有单调递增的聚集索引（例如 int identity），每次追加日志消息时都会增加。这确保插入的页面通常在内存中，并避免了堆表带来的性能损失。 - 在一个事务中执行多个日志追加操作（10 次插入事务比 10 次非事务插入更快，通常获得更少的锁定）。 - 给自己休息时间。每 N 毫秒才将日志记录到您的数据库中。分批处理工作。 - 如果您需要历史报告，可以考虑对日志表进行分区。例如：您可以每月创建一个新的日志表，并同时拥有一个日志 VIEW，该视图是所有旧日志表的 UNION ALL。根据最合适的来源执行报告。 - 通过在单个（较小的）事务中刷新多个日志消息，您将获得更好的性能，并且具有这样的优势：如果有 10 个线程正在执行工作并记录内容，则只有一个线程正在将内容刷新到日志表中。这种流水线实际上使事情更加扩展。

- Sam Saffron

Answer 3

由于您不关心审计表的事务完整性，因此可以在事务之外（即在事务完成后）执行日志记录。这将最大程度地减少对事务的影响。

此外，如果您想要最小化锁定，应尽可能确保您的查询工作负载具有覆盖非聚集索引的尽可能多的部分。（SQL Server 2005及以上版本中，使用NC索引中的INCLUDE语句可以产生很大的差异）

- Mitch Wheat

---

注意：如果您在事务完成后执行所有日志记录，那么调试长时间运行的事务或中止的事务将变得相当困难。 - Sam Saffron

海报已经明确表示，如果日志记录失败，只要交易完成就可以了。显然，交易应该尽可能短。 - Mitch Wheat

我想说的是，在这种情况下（即在单独的线程中），使用“可能比”（即在完成后）更好。 - Sam Saffron

Answer 4

防止日志记录与“常规”数据库发生锁定问题的一种简单方法是不使用相同的数据库。只需为您的日志记录创建另一个数据库即可。作为奖励，您的日志记录数据库的快速增长不会导致主要数据库中的碎片化。个人而言，我通常更喜欢记录到文件中--但话说回来，我习惯于在我的编辑器-VIM中进行大量文本操作。将日志记录到单独的数据库应该有助于避免死锁问题。

只需确保，如果您尝试为所使用的日志框架编写自己的数据库附加程序，则要非常小心您的锁定（我猜这就是您引用的Jeff博客文章中的问题）。正确编写（请参见Jeff帖子中的几条评论），除非它们做了一些奇怪的事情，否则您不应该在日志框架中遇到锁定问题。

- Jonathan Rupp

---

我认为专门引入一个全新的数据库只是为了创建一个日志表有些过于激进了，反正你总是可以使用文件组来控制每个表的存储位置。同时需要注意的是，如果你已经在使用分布式事务，那么将日志记录到一个单独的数据库中实际上可能会使事情变得更糟，尤其是当该操作参与分布式事务时。 - Sam Saffron

如果您正在使用分布式事务，我建议不要将日志登记在其中。万一出现故障导致事务回滚，而又没有任何记录表明是什么原因导致的故障，那就麻烦了。 - Jonathan Rupp