为什么Server 2008在长事务插入上阻塞SELECT？

Question

我们正在尝试有一个事务性表，它只接受定期插入的新记录。

这个简单的表要求我们随着时间的推移不断地向它添加新的记录。该表中的事务数量预计会相当高，而且可能会有定期的批处理事务导入(>1000)，可能需要多秒才能完成。

然后，我们根据这些数据执行一组select语句，对不同的列进行分组，以返回所需的值。

从最初的测试中，我们发现了一个与SQL Server相关的瓶颈，在插入事务的中间阻塞我们的SELECT。

下面是一个简单的例子，可以运行来说明这个问题。

-简单数据库表

create table LOCK_TEST (
LOCK_TEST_ID int identity ,
AMOUNT int);

--在一个查询窗口中运行此操作

begin tran
insert into LOCK_TEST (AMOUNT) values (1);
WAITFOR DELAY '00:00:15' ---- 15 Second Delay
insert into LOCK_TEST (AMOUNT) values (1);
commit

-在查询2中，并行运行此命令

select SUM(AMOUNT)
from LOCK_TEST;

我希望查询2立即返回，0直到查询1完成，然后显示2。我们永远不希望看到第二个查询返回1。

答案是，我们看过select语句上的relate (NOLOCK)。但是这违反了事务边界，返回的信息可能是财务性质的，我们不希望在查询中看到任何未经说明的细节。

我的问题似乎在插入方面.

为什么INSERT阻塞SELECT语句，即使它没有修改任何现有数据？

加分问题：是Server的一个“特性”，还是我们也会在其他数据库中找到它？

UPDATE我现在有时间查找本地oracle数据库并运行相同的简单测试。这张考证和我预料的一样。

Ie我可以任意频繁地运行查询，它将返回null，直到第一个事务提交，然后返回2。

有办法让Server像这样工作吗？还是我们需要迁移到甲骨文？

Answer 1

这种锁定行为是Server的一个特性。对于2005年及以上版本，您可以使用行级版本控制 (这是Oracle默认使用的)来实现相同的结果&而不是阻止您的选择。这会给tempdb带来额外的压力，因为tempdb维护行级别的版本控制，因此请确保您能够适应这种情况。要使SQL以您希望的方式运行，请运行以下命令：

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Answer 2

这是Server和Sybase中的完全标准行为(至少如此)。

数据更改(插入、更新、删除)需要排他锁。这会导致读者被封锁：

使用独占(X)锁，其他事务都不能修改数据；只有使用NOLOCK提示或读取未提交隔离级别才能进行读取操作。

在Server 2005及更高版本中，他们引入了快照隔离(也称为行版本控制)。波尔小姐：了解基于行版本控制的隔离级别。这意味着读者拥有最新提交的数据，但是如果您想要回写，比如说，您可能会发现数据是错误的，因为它在阻塞事务中发生了变化。

现在，这就是为什么最好的做法是保持交易短。例如，只处理在开始/提交之间需要处理的内容，不要从触发器发送电子邮件等等。

编辑：

在Server中，这样的锁定一直在发生。但是，锁定时间太长会导致阻塞，从而降低性能。显然，太久是主观的。

Answer 3

这在MySQL中也存在。这背后的逻辑是:如果您对某些数据执行选择，您就会期望它在最新的数据集上运行。这就是为什么INSERT会导致表级锁(也就是说，除非引擎能够执行行级锁定，就像innodb那样)。有一些方法可以禁用这种行为，这样您就可以进行“脏读”，但它们都是特定于软件(甚至数据库引擎)的。