我需要什么隔离级别来确保跨数据库INSERT ... SELECT原子地发生？

I have a source table that drives the content of a second table. I have two or more processes that will be running for this (with different additions to the where clause). All essentially use the statement below. Somehow I am getting duplicates in the destination table. I am not currently specifying an isolation level - or any locking hints. The SQL Server is running 2005 which is something I cannot change (I know 2008's MERGE would probably be applicable).

我有一个驱动第二个表的内容的源表。我有两个或更多将为此运行的进程（对where子句添加了不同的进程）。所有基本上都使用以下声明。不知何故，我在目的地表中得到重复。我目前没有指定隔离级别 - 或任何锁定提示。 SQL Server正在运行2005，这是我无法改变的（我知道2008年的MERGE可能适用）。

INSERT INTO tableDst 
    (PK, ...)
SELECT src.PK, ...
FROM tableSrc src
LEFT JOIN tableDst dst ON src.PK = dst.PK
WHERE dst.PK IS NULL

The two tables are in different databases. Both databases are on the same instance on the same server, so cross-database statements such as this are possible.

这两个表位于不同的数据库中。两个数据库都位于同一服务器上的同一实例上，因此可以使用此类跨数据库语句。

What isolation level or locking hints do I need to make this work in an atomic fashion, so duplicates are prevented? Do I need to wrap it in a transaction?

我需要什么隔离级别或锁定提示才能使其以原子方式工作，因此可以防止重复？我需要将其包装在交易中吗？

1 个解决方案

#1

INSERT INTO tableDst 
    (PK, ...)
SELECT src.PK, ...
FROM tableSrc src WITH (UPDLOCK, HOLDLOCK)
LEFT JOIN tableDst dst ON src.PK = dst.PK
WHERE dst.PK IS NULL

That is equivalent to SERIALIZABLE, it will block reads to the source table selected rows.

这相当于SERIALIZABLE，它会阻塞对源表所选行的读取。

Without knowing your precise scenario and testing not sure if you might also need:

在不知道您的精确场景和测试的情况下，不确定您是否还需要：

INSERT INTO tableDst 
    (PK, ...)
SELECT src.PK, ...
FROM tableSrc src WITH (UPDLOCK, HOLDLOCK)
LEFT JOIN tableDst dst WITH (UPDLOCK, HOLDLOCK) ON src.PK = dst.PK
WHERE dst.PK IS NULL