sql-server - 如果 RCSI 下不存在则插入
问题描述
我有一个 READ_COMMITTED_SNAPSHOT_ISOLATION 设置为 ON 的数据库(无法更改)。
我在许多并行会话上将新行插入到表中,但前提是它们尚不存在(经典左连接检查)。
插入代码如下所示:
INSERT INTO dbo.Destination(OrderID)
SELECT DISTINCT s.OrderID
FROM dbo.Source s
LEFT JOIN dbo.Destination d ON d.OrderID = s.OrderID
WHERE d.OrderID IS NULL;
如果我在许多并行会话上运行它,我会得到很多重复的键错误,因为不同的会话会一遍又一遍地尝试插入相同的 OrderID。
这是预期的,因为 RCSI 下缺少共享锁。
这里推荐的解决方案(根据我的研究)是使用 READCOMMITTEDLOCK 提示,如下所示:
LEFT JOIN dbo.Destination d WITH (READCOMMITTEDLOCK) ON d.OrderID = s.OrderID
这有点工作,因为大大减少了重复的键错误,但(令我惊讶的是)并没有完全消除它们。
作为一个实验,我删除了 Destination 表上的唯一约束,并看到许多重复项在同一毫秒内进入表,来自不同的会话。
似乎尽管有表格提示,但我仍然在存在检查时得到误报,并且冗余插入触发。
我尝试了不同的提示(SERIALIZABLE),但它使情况变得更糟,并让我陷入了死锁。
我怎样才能使这个插入在 RCSI 下工作?
解决方案
读取您将要插入的表的正确锁定提示是 (UPDLOCK,HOLDLOCK),它会在您读取行时将 U 锁放在行上,如果行不存在,还会放置 SERIALIZABLE 样式的范围锁。
您的方法的问题是每个客户端都试图插入一批行,并且每批都必须完全成功或失败。如果您使用行级锁定,您将始终遇到会话成功插入一行但随后被阻塞等待读取或插入后续行的情况。这不可避免地会导致 PK 失败或死锁,具体取决于所使用的行锁的类型。
解决方案是:
1)一个接一个地插入行,在检查并插入下一行时不要从一行中持有锁。
2) 简单地升级到 tabblockx 或Applciation Lock以强制您的并发会话通过这段代码进行序列化。
因此,您可以拥有高并发加载或批量加载,但不能同时拥有两者。好吧,主要是。
3)您可以在索引上打开IGNORE_DUP_KEY,而不是错误将在插入时跳过任何重复项。
推荐阅读
- ansible - 如果行已经存在于某处,Ansible lineinfile 将不会添加到文件末尾
- c# - 如何在选定的 Windows 桌面上运行 UWP 应用程序
- ruby-on-rails - 当我尝试创建 Rails 应用程序时,为什么没有为必需的参数 'app_path' 提供值```?
- javascript - 指令中的 AngularJS rootScope.$emit
- angular - 清除过滤器角度 6
- php - 无法从 php Web 应用程序执行 tkinter python gui 应用程序
- php - 我的图像没有显示在 php 上,但是当我在 html 文件上预览它时
- android - 在生物识别提示中使用时如何确定使用哪个生物识别?
- android - security threat warning when I use SmsManager
- python - 根据某些字符在列表中排序子列表