使用 TSQL SNAPSHOT ISOLATION，是否可以仅更新非锁定行？
大型 SQL Server 2008 表通常使用 SNAPSHOT ISOLATION 事务以（相对）小的块进行更新。快照非常适合这些更新，因为块从不重叠。这些更新不是单个长时间运行的操作，而是按事务分组的许多小型单行插入/更新。
我想要一个较低优先级的事务来更新当前未锁定的所有行。有谁知道我怎样才能得到这种行为？另一个 SNAPSHOT ISOLATION 事务会在行发生冲突时立即失败，还是会在失败之前更新所有内容？
使用 try-catch 设置 DEADLOCK_PRIORITY LOW 会有帮助吗？也许在带有 WHERE 的重试循环中，该循环仅针对尚未更新的行？
A large SQL Server 2008 table is normally being updated in (relatively) small chunks using a SNAPSHOT ISOLATION transaction. Snapshot works very well for those updates since the chunks never overlap. These updates aren't a single long running operation, but many small one-row insert/update grouped by the transaction.
I would like a lower priority transaction to update all the rows which aren't currently locked. Does anyone know how I can get this behavior? Will another SNAPSHOT ISOLATION transaction fail as soon as it a row clashes, or will it update everything it can before failing?
Could SET DEADLOCK_PRIORITY LOW with a try-catch be of any help? Maybe in a retry loop with a WHERE which targets only rows which haven't been updated?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
快照隔离实际上并不是这样工作的。乐观锁定模型意味着它在准备好写入/提交之前不会检查锁定或冲突。您也不能设置查询“优先级”本身，也不能在更新上使用 READPAST 提示。
每个更新都是隐式原子事务，因此如果 10 个更新中有 1 个失败（在单个事务中），它们都会回滚。
SET DEADLOCK_PRIORITY 仅设置在发生 dealdlock 时回滚事务的首选项（否则选择“最便宜”的回滚）。
如果您期望定期发生碰撞，则 try-catch 几乎是必需的。
重试循环的工作方式与使用不同的锁定模型和NOWAIT 提示来跳过将被阻止的查询一样。
Snapshot isolation doesn't really work that way; the optimistic locking model means it won't check for locks or conflicts until it's ready to write/commit. You also can't set query 'priority' per se, nor can you use the READPAST hint on an update.
Each update is an implicit atomic transaction so if 1 update out of 10 fails (in a single transaction) they all roll back.
SET DEADLOCK_PRIORITY only sets a preference for which transaction is rolled back in the event of a dealdlock (otherwise the 'cheapest' rollback is selected).
A try-catch is pretty much a requirement if you're expecting regular collisions.
The retry loop would work as would using a different locking model and the NOWAIT hint to skip queries that would be blocked.
SNAPSHOT ISOLATION 事务一旦遇到更新冲突就会失败。但是，我会使用数据库外部的一些队列来确定更新的优先级。
SNAPSHOT ISOLATION transaction fails as soon as it encounters an update conflict. However, I would use some queue outside the database to prioritize updates.