问题描述
我对锁和提示还很陌生.
I am fairly new to locks and hints.
我有一个非常频繁的SELECT
和INSERT
操作的表.该表有 1100 万条记录.
I have a table with very frequent SELECT
and INSERT
operations. The table has 11 million records.
我向其中添加了一个新列,我需要将同一表中现有列中的数据复制到新列中.
I have added a new column to it and I need to copy over the data from an existing column in the same table to the new column.
我计划使用 ROWLOCK
提示来避免将锁升级到表级锁并阻止表上的所有其他操作.例如:
I am planning to use ROWLOCK
hint to avoid escalating locks to table level locks and blocking out all other operations on the table. For example:
UPDATE
SomeTable WITH (ROWLOCK)
SET
NewColumn = OldColumn
问题:
- 用
NOLOCK
代替ROWLOCK
吗?注意,一旦记录插入到表中,OldColumn 的值就不会改变,所以NOLOCK
不会导致脏读. - 在这种情况下,
NOLOCK
是否有意义,因为 SQL Server 无论如何都必须获得UPDATE
的更新锁. - 是否有更好的方法来实现这一目标?
- Would a
NOLOCK
instead ofROWLOCK
? Note, once the records are inserted in the table, the value for OldColumn does not change, soNOLOCK
would not cause dirty reads. - Does
NOLOCK
even make sense in this case, because the SQL Server would have to anyways get update locks forUPDATE
. - Is there a better way of achieving this?
我知道要避免提示,SQL Server 通常会做出更明智的选择,但我不想在此更新期间锁定表.
I know hints are to be avoided and SQL Server usually makes smarter choices, but I don't want to get the table locked out during this update.
推荐答案
尝试批量更新.
DECLARE @Batch INT = 1000
DECLARE @Rowcount INT = @Batch
WHILE @Rowcount > 0
BEGIN
;WITH CTE AS
(
SELECT TOP (@Batch) NewColumn,OldColumn
FROM SomeTable
WHERE NewColumn <> OldColumn
OR (NewColumn IS NULL AND OldColumn IS NOT NULL)
)
UPDATE cte
SET NewColumn = OldColumn;
SET @Rowcount = @@ROWCOUNT
END
这篇关于更新大量记录时如何避免UPDATE语句锁定整个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!