SQL Server语言 - 批处理和1秒等待

Question

我正在更新一个表中超过1亿行。为了减少被锁定的行数(甚至表锁)，我使用while循环和每次递增10k行的组合来处理更新。

我在SQL Server 2012上运行这个。

DECLARE @i int = 0
DECLARE @last int = 10000000
WHILE @i <= @last
BEGIN
UPDATE mytbl (ROWLOCK)
SET foo = null
WHERE id BETWEEN @i AND @i + 10000
SET @i += 10000
WAITFOR DELAY '00:00:01'
END

代码中的1秒等待在提高性能、提交/刷新事务或释放事务锁方面做了什么吗?

Answer 1

RE: "为了减少被锁定的行数(甚至是表锁)…">

总的来说，你有一个很好的方法。建议:

1. 我建议您将UPDATE包装在显式事务中。在每次迭代中提交事务。否则，您可能会有100亿行事务的风险。显式事务意味着我知道它已提交。开始翻译…更新……提交TRAN。对于这种类型的工作，我每次都使用显式事务。
2. 从10,000开始减少增量。我在所有情况下都用1000。您可以运行测试来确定触发表锁的确切数字。您可以阅读2012年的Microsoft锁升级指南(锁升级因版本而异)。但是，我的目标是，当我这样做的时候，决不使用表锁。强调"绝不"。我只用1000块，晚上睡得更好。我向你推荐1000个。但是，您的里程可能会有所不同。
删除WAITFOR。对于这个循环的目的，它只增加了几个小时的运行时间(1亿行上最初的10,000个增量为10,000秒)。

我希望这对你有帮助。我已经在数十亿行的表上做了50到100次这样的实验。关键字(如上所述):永远不要表锁，并且每次迭代都提交以保持事务较小。小额交易是你的朋友，有很多原因。