sql-server - 为什么 SQL Server 2008 在长事务 INSERT 上阻止 SELECT？

Question

我们正在尝试建立一个只定期插入新记录的事务表。

这个简单的表需要我们随着时间的推移不断向其中添加新记录。该表中的交易量预计会相当高，并且可能会定期批量导入交易(>1000)，这可能需要几秒钟才能完成。

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

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

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

-- 简单数据库表

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

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

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 语句上的WITH (NOLOCK) 相关。但这违反了事务边界，并且返回的信息本质上可能是财务信息，我们不希望在查询中看到任何未提交的详细信息。

我的问题似乎出在 INSERT 端......
为什么 INSERT 会阻止 SELECT 语句，即使它没有修改任何现有数据？

加分问题:这是 SQL Server 的“功能”吗？还是我们会在其他数据库风格上找到此功能？

更新我现在有时间找到本地 Oracle 数据库并运行相同的简单测试。这次测试通过了，正如我所期望的那样。

即，我可以根据需要多次运行查询，并且它将返回 null，直到第一个事务提交，然后返回 2。

有没有办法让 SQL Server 像这样工作？或者我们是否需要迁移到 Oracle？

Answer 1

这种锁定行为是 SQL Server 的一项功能。对于2005及以上版本，您可以使用row level versioning (这是 Oracle 上默认使用的)以获得相同的结果并且不会阻止您的选择。这会给 tempdb 带来额外的压力，因为 tempdb 维护行级版本控制，因此请确保适应这一点。要使 SQL 按照您希望的方式运行，请运行以下命令:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON