为什么SQL Server 2008在长事务插入时阻塞SELECT语句?

We are trying to have a transactional table that only takes new records inserted on a regular basis.

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

This simple table requires us to continuously add new records to it over time. The volume of transactions into this table is expected to be quite high, and also there might be periodical batch imports of transactions (>1000) that may take multiple seconds to complete.

这个简单的表需要我们不断地向它添加新记录。进入该表的事务量预计会相当高，而且可能还会有周期性的批量导入事务(>1000)，这可能需要几秒钟才能完成。

From this data we then do a set of select statements grouping different columns to return the required values.

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

From our initial testing we have found a bottleneck to be related to SQL Server that blocks our SELECT's when in the middle of a transaction of INSERTS.

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

Below is a simple example that can be run to illustrate the problem.

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

-- Simple DB Table

——简单的数据库表

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

-- Run this in 1 query window

——在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

-- In Query 2 run this in parallel

——在查询2中并行运行这个

select SUM(AMOUNT)
from LOCK_TEST;

I would expect Query 2 to return straight away, with 0 until query 1 completes, and then show 2. We never want to see 1 returned from the 2nd query.

我希望查询2直接返回，直到查询1完成，然后显示2。我们不希望看到从第二个查询返回1。

The answer's we have looked at relate to WITH (NOLOCK) on the select statement. But this violates the transactional boundaries, and the returned information may be financial in nature and we don't wish to see any uncommited details in our queries.

答案是，我们已经研究了select语句中与(NOLOCK)相关的内容。但是这违反了事务边界，返回的信息本质上是财务的，我们不希望在查询中看到任何未提及的细节。

My problem seems to be on the INSERT side...
Why does the INSERT block the SELECT statement even though it's not modifying any existing data?

我的问题似乎在插入方面……为什么INSERT会阻止SELECT语句，即使它没有修改任何现有的数据?

Bonus points question: Is this a "feature" of SQL Server, or would we find this on other Database flavours also?

加分点问题:这是SQL Server的“特性”，还是我们在其他数据库中也会发现这一点?

UPDATE I have now had time to find a local oracle database and run the same simple test. This test pass's as I would expect.

现在，我已经有时间找到本地的oracle数据库并运行相同的简单测试。这次考试通过了。

Ie I can run query as often as I want, and it will return null until the 1st transaction commits, then returns 2.

我可以像我希望的那样经常运行查询，并且它将返回null，直到第一个事务提交，然后返回2。

Is there a way to make SQL Server work like this? or do we need to move to Oracle?

是否有一种方法可以让SQL服务器这样工作?或者我们需要转到Oracle吗?

4 个解决方案

#1

this locking behavior is a feature of SQL Server. With 2005 and above, you can use row level versioning (which is what is used by default on Oracle) to achieve the same result & not block your selects. This puts extra strain on tempdb because tempdb maintains the row level versioning, so make sure you accommodate for this. To make SQL behave the way you want it to, run this:

这种锁定行为是SQL Server的一个特性。使用2005年及以上版本，您可以使用行级别版本控制(这是Oracle默认使用的)来实现相同的结果，而不会阻塞您的选择。这对tempdb造成了额外的压力，因为tempdb维护行级别的版本控制，所以请确保您适应了这一点。要使SQL按照您希望的方式运行，请运行以下命令:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

#2

This is completely standard behaviour in SQL Server and Sybase (at least).

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

Data changes (Insert, update, delete) require exclusive locks. this causes readers to be blocked:

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

With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

使用独占(X)锁，其他事务无法修改数据;读取操作只能通过使用NOLOCK提示或读取未提交的隔离级别进行。

With SQL Server 2005 and above, they introduced snapshot isolation (aka row versioning). From MS BOL: Understanding Row Versioning-Based Isolation Levels. This means a reader has latest committed data but if you then want to write back, say, then you may find the data is wrong because it changed in the blocking transaction.

在SQL Server 2005和上面，它们引入了快照隔离(也称为行版本控制)。来自BOL:理解基于行版本的隔离级别。这意味着读取器有最新提交的数据，但是如果您想要回写，那么您可能会发现数据是错误的，因为它在阻塞事务中发生了更改。

Now, this is why best practice is to keep transactions short. For example, only process what you need to between BEGIN/COMMIT, don't send emails from triggers etc.

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

Edit:

编辑:

Locking like this happens all the time in SQL Server. However, locking for too long becomes blocking that reduce performance. Too long is subjective, obviously.

这样的锁定在SQL Server中经常发生。但是，锁定太长时间会导致阻塞，从而降低性能。很明显，太长时间是主观的。

#3

This exists in MySQL too. Here's the logic behind it: if you perform a SELECT on some data, you expect it to operate on an up-to-date dataset. That's why INSERT results in a table-level lock (that is unless the engine is able to perform row-level locking, like innodb can). There are ways to disable this behaviour, so that you can do "dirty reads", but they all are software (or even database-engine) specific.

这在MySQL中也存在。它背后的逻辑是:如果您对某些数据执行SELECT，您希望它对最新的数据集进行操作。这就是插入导致表级锁的原因(除非引擎能够执行行级锁，比如innodb可以)。有一些方法可以禁用这种行为，这样您就可以做“脏读”，但是它们都是特定的软件(甚至是数据库引擎)。

#4

I also ran into this issue. After investigation, it seemed to be that it was not the data that was locked per se, but the clustered index that was being modified as a result of the insert. Since the clustered index resides on the data pages, the associated data rows are locked as well.

我也遇到了这个问题。经过调查，似乎并不是数据本身被锁定，而是由于插入而修改的聚集索引。由于聚集索引驻留在数据页上，因此关联的数据行也被锁定。