更新大量记录时如何避免UPDATE语句锁定整个表 [英] How to avoid UPDATE statement locking out the entire table when updating large number of records

问题描述

我对锁和提示还很陌生.

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

问题:

1. 用 NOLOCK 代替 ROWLOCK 吗?注意，一旦记录插入到表中，OldColumn 的值就不会改变，所以 NOLOCK 不会导致脏读.
2. 在这种情况下，NOLOCK 是否有意义，因为 SQL Server 无论如何都必须获得 UPDATE 的更新锁.
3. 是否有更好的方法来实现这一目标?

1. Would a NOLOCK instead of ROWLOCK? Note, once the records are inserted in the table, the value for OldColumn does not change, so NOLOCK would not cause dirty reads.
2. Does NOLOCK even make sense in this case, because the SQL Server would have to anyways get update locks for UPDATE.
3. 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语句锁定整个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！