为什么我的 SQL Server UPSERT 代码有时不会阻塞? [英] Why does my SQL Server UPSERT code sometimes not block?

问题描述

我有一个表 ImportSourceMetadata，我用它来控制导入批处理.它包含一个 PK 列 SourceId 和一个数据列 LastCheckpoint.导入批处理读取给定 SourceId 的 LastCheckpoint，执行一些逻辑(在其他表上)，然后更新该 LastCheckpoint>SourceId 或者如果它不存在则插入它.

I have a table ImportSourceMetadata which I use to control an import batch process. It contains a PK column SourceId and a data column LastCheckpoint. The import batch process reads the LastCheckpoint for a given SourceId, performs some logic (on other tables), then updates the LastCheckpoint for that SourceId or inserts it if it doesn't exist yet.

进程的多个实例同时运行，通常使用分离的SourceIds，对于这些情况我需要高并行性.但是，可能会发生为相同的 SourceId 启动两个进程的情况；在这种情况下，我需要实例相互阻止.

Multiple instances of the process run at the same time, usually with disjunct SourceIds, and I need high parallelity for those cases. However, it can happen that two processes are started for the same SourceId; in that case, I need the instances to block each other.

因此，我的代码如下所示:

Therefore, my code looks as follows:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId = 'Source'

-- Perform some processing

-- UPSERT: if the SELECT above yielded no value, then
INSERT INTO ImportSourceMetadata(SourceId, LastCheckpoint) VALUES ('Source', '2013-12-21')
-- otherwise, we'd do this: UPDATE ImportSourceMetadata SET LastCheckpoint = '2013-12-21' WHERE SourceId = 'Source'

COMMIT TRAN

我使用事务来实现原子性，但我只能使用 READ COMMITTED 隔离级别(因为执行一些处理"块中的并行性要求).因此(并避免死锁)，我在 SELECT 语句中包含了一个 UPDLOCK 提示，以实现在 SourceId 值上参数化的关键部分".

I'm using the transaction to achieve atomicity, but I can only use READ COMMITTED isolation level (because of the parallelity requirements in the "Perform some processing" block). Therefore (and to avoid deadlocks), I'm including an UPDLOCK hint with the SELECT statement to achieve a "critical section" parameterized on the SourceIdvalue.

现在，这在大多数情况下都运行良好，但是当为相同的 SourceId 启动大量并行进程时，我已经设法使用 INSERT 语句触发主键违规错误数据库.但是，我无法可靠地重现这一点，而且我不明白为什么它不起作用.

Now, this works quite well most of the time, but I've managed to trigger primary key violation errors with the INSERT statement when starting a lot of parallel processes for the same SourceIdwith an empty database. I cannot reliably reproduce this, however, and I don't understand why it doesn't work.

我在互联网上找到了一些提示(例如，此处和此处，在评论中) 我需要指定 WITH (UPDLOCK,HOLDLOCK) (resp. WITH (UPDLOCK,SERIALIZABLE)) 而不是仅仅在 SELECT 上使用 UPDLOCK，但我真的不明白为什么会这样.MSDN 文档说、

I've found hints on the internet (e.g., here and here, in a comment) that I need to specify WITH (UPDLOCK,HOLDLOCK) (resp. WITH (UPDLOCK,SERIALIZABLE)) rather than just taking an UPDLOCK on the SELECT, but I don't really understand why that is. MSDN docs say,

更新锁定
指定在事务完成之前获取并保持更新锁.

UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes.

在事务完成之前获取并保持的更新锁应该足以阻止后续的 INSERT，事实上，当我在 SQL Server Management Studio 中尝试它时，它确实阻止了我的插入.但是，在极少数情况下，它似乎突然不再起作用了.

An update lock that is taken and held until the transaction completes should be enough to block a subsequent INSERT, and in fact, when I try it out in SQL Server Management Studio, it does indeed block my insert. However, in some rare cases, it seems to suddenly not work any more.

那么，为什么 UPDLOCK 是不够的，为什么在我 99% 的测试运行中都足够了(以及在 SQL Server Management Studio 中模拟它时)?

So, why exactly is it that UPDLOCK is not enough, and why is it enough in 99% of my test runs (and when simulating it in SQL Server Management Studio)?

更新:我现在发现我可以通过在 SQL Server Management Studio 的两个不同窗口中同时执行上面的代码可靠地重现非阻塞行为，直到插入之前，但是 仅 创建数据库后的第一次.之后(即使我删除了 ImportSourceMetadata 表的内容)，SELECT WITH (UPDLOCK) 确实会阻塞并且代码不再失败.实际上，在 sys.dm_tran_locks 中，我可以看到即使该行在后续测试运行中不存在，但在创建表后的第一次运行时不存在，我也可以看到采用了 U 锁.

Update: I've now found I can reproduce the non-blocking behavior reliably by executing the code above in two different windows of SQL Server Management Studio simultaneously up to just before the INSERT, but only the first time after creating the database. After that (even though I deleted the contents of the ImportSourceMetadata table), the SELECT WITH (UPDLOCK) will indeed block and the code no longer fails. Indeed, in sys.dm_tran_locks, I can see a U-lock taken even though the row does not exist on subsequent test runs, but not on the first run after creating the table.

这是一个完整的示例，用于显示新创建的表"和旧表"之间的锁差异:

This is a complete sample to show the difference in locks between a "newly created table" and an "old table":

DROP TABLE ImportSourceMetadata
CREATE TABLE ImportSourceMetadata(SourceId nvarchar(50) PRIMARY KEY, LastCheckpoint datetime)

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId='Source'

SELECT * 
FROM sys.dm_tran_locks l 
JOIN sys.partitions p 
ON l.resource_associated_entity_id = p.hobt_id JOIN sys.objects o 
ON p.object_id = o.object_id

INSERT INTO ImportSourceMetadata VALUES('Source', '2013-12-21')
ROLLBACK TRAN

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT LastCheckpoint FROM ImportSourceMetadata WITH (UPDLOCK) WHERE SourceId='Source'

SELECT * 
FROM sys.dm_tran_locks l 
JOIN sys.partitions p 
ON l.resource_associated_entity_id = p.hobt_id JOIN sys.objects o 
ON p.object_id = o.object_id

ROLLBACK TRAN

在我的系统上(使用 SQL Server 2012)，第一个查询显示 ImportSourceMetadata 上没有锁定，但第二个查询显示 ImportSourceMetadata<上的 KEY 锁定/代码>.

On my system (with SQL Server 2012), the first query shows no locks on ImportSourceMetadata, but the second query shows a KEY lock on ImportSourceMetadata.

换句话说，HOLDLOCK 确实是必需的，但前提是表是新创建的.这是为什么?

In other words, HOLDLOCK is indeed required, but only if the table was freshly created. Why's that?

推荐答案

您还需要 HOLDLOCK.

如果该行确实存在，那么您的 SELECT 语句将至少在该行上取出一个 U 锁并将其保留到事务结束.

If the row does exist then your SELECT statement will take out a U lock on at least that row and retain it until the end of the transaction.

如果该行不存在，则没有行可以获取并保持 U 锁定，因此您不会锁定任何内容.HOLDLOCK 将至少锁定该行适合的范围.

If the row doesn't exist there is no row to take and hold a U lock in so you aren't locking anything. HOLDLOCK will lock at least the range where the row would fit in.

如果没有 HOLDLOCK，两个并发事务都可以对不存在的行执行 SELECT.保留没有冲突的锁，并且都移动到 INSERT 上.

Without HOLDLOCK two concurrent transactions can both do the SELECT for a non existent row. Retain no conflicting locks and both move onto the INSERT.

关于您问题中的重现，行不存在"问题似乎比我最初想象的要复杂一些.

Regarding the repro in your question it seems the "row doesn't exist" issue is a bit more complex than I first thought.

如果该行先前确实存在但此后已被逻辑删除但仍作为幽灵"记录物理存在于页面上，则仍然可以在解释阻塞的幽灵上取出 U 锁你看到的.

If the row previously did exist but has since been logically deleted but still physically exists on the page as a "ghost" record then the U lock can still be taken out on the ghost explaining the blocking that you are seeing.

您可以使用 DBCC PAGE 来查看幽灵记录，就像对您的代码稍作修改一样.

You can use DBCC PAGE to see ghost records as in this slight amend to your code.

SET NOCOUNT ON;

DROP TABLE ImportSourceMetadata

CREATE TABLE ImportSourceMetadata
  (
     SourceId       NVARCHAR(50),
     LastCheckpoint DATETIME,
     PRIMARY KEY(SourceId)
  )

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT LastCheckpoint
FROM   ImportSourceMetadata WITH (UPDLOCK)
WHERE  SourceId = 'Source'

INSERT INTO ImportSourceMetadata
VALUES      ('Source',  '2013-12-21')

DECLARE @DBCCPAGE NVARCHAR(100)

SELECT TOP 1 @DBCCPAGE = 'DBCC PAGE(0,' + CAST(file_id AS VARCHAR) + ',' + CAST(page_id AS VARCHAR) + ',3) WITH NO_INFOMSGS'
FROM   ImportSourceMetadata
       CROSS APPLY  sys.fn_physloccracker(%%physloc%%)

ROLLBACK TRAN

DBCC TRACEON(3604)

EXEC (@DBCCPAGE)

DBCC TRACEOFF(3604)

SSMS 消息选项卡显示

The SSMS messages tab shows

Slot 0 Offset 0x60 Length 31

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 31                     
Memory Dump @0x000000001215A060

0000000000000000:   3c000c00 00000000 9ba20000 02000001 †<.......¢...... 
0000000000000010:   001f0053 006f0075 00720063 006500††††...S.o.u.r.c.e.  

Slot 0 Column 1 Offset 0x13 Length 12 Length (physical) 12

这篇关于为什么我的 SQL Server UPSERT 代码有时不会阻塞?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！