Je suis assez nouveau dans les serrures et les astuces.

J'ai une table avec des opérations SELECT et INSERT très fréquentes. Le tableau contient 11 millions d'enregistrements.

J'y ai ajouté une nouvelle colonne et je dois copier les données d'une colonne existante dans le même tableau vers la nouvelle colonne.

Je prévois d’utiliser l’indice ROWLOCK pour éviter d’escalader les verrous en verrous au niveau de la table et de bloquer toutes les autres opérations sur la table. Par exemple:

UPDATE 
    SomeTable WITH (ROWLOCK)
SET
    NewColumn = OldColumn

Des questions:

  1. Est-ce qu'un NOLOCK au lieu de ROWLOCK? Notez qu'une fois les enregistrements insérés dans la table, la valeur de OldColumn ne change pas, donc NOLOCK ne provoquerait pas de lectures incorrectes.
  2. Est-ce que NOLOCK a même du sens dans ce cas, car le serveur SQL devrait de toute façon obtenir des verrous de mise à jour pour UPDATE.
  3. Existe-t-il un meilleur moyen d'y parvenir?

Je sais que les indices doivent être évités et que SQL Server fait généralement des choix plus intelligents, mais je ne veux pas que la table soit verrouillée pendant cette mise à jour.

10
HappyTown 24 janv. 2017 à 21:18

4 réponses

Meilleure réponse

Essayez de mettre à jour par lots.

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
6
Gerald Leach 30 sept. 2019 à 20:35

J'ai adopté l'approche de @ pacreely (voir sa réponse à cette question) de mise à jour par lots et j'ai créé une variante update...top. J'ai ajouté l'indication (rowlock) indiquant au serveur SQL de garder les verrous au niveau des lignes.

Voir update ... top pour plus de détails. Notez également que vous ne pouvez pas utiliser order by lors de l'utilisation de top dans l'instruction update, insert, merge, delete donc les lignes référencées ne sont pas organisé dans n'importe quel ordre.

declare @BatchSize  int = 1000
declare @RowCount   int = @BatchSize

while @RowCount > 0
begin
    update top (@BatchSize) SomeTable with (rowlock)
    set NewColumn = OldColumn
    where 
        NewColumn <> OldColumn      or
        (
            NewColumn is null       and
            OldColumn is not null
        )
    select @RowCount = @@rowcount
end
3
HappyTown 27 janv. 2017 à 23:30

Nous avons récemment eu un cas où nous voulions faire quelque chose de similaire, mais lentement au fil des jours (mise à jour uniquement d'un certain nombre d'enregistrements par exécution, et seulement pendant certaines heures). Les données les plus récentes étaient correctes, mais des millions de lignes de données plus anciennes devaient être mises à jour. Notre tableau de données ressemble à ceci:

Create Table FileContent
(
FileContent varchar(max),
File_PK bigint,
NewFileContent varchar(max)
)

Et nous n'avions besoin de mettre à jour que certaines lignes, mais des millions d'entre elles. Nous avons créé une table pour stocker notre progression afin de pouvoir utiliser un travail planifié pour parcourir et mettre à jour la table principale, puis nous avons rempli cette table avec les clés primaires des enregistrements de la table principale qui nécessitaient une mise à jour:

Create Table FilesToUpdate
(
File_PK bigint,
IsUpdated bit NOT NULL DEFAULT 0
)

Ensuite, nous avons programmé le script suivant pour effectuer la mise à jour (pour votre propre usage, jouez avec le dimensionnement des lots et la planification de ce qui fonctionne avec votre système).

/***  
Script to update and fix records.
***/
DECLARE @Rowcount INT = 1 -- 
    ,   @BatchSize INT = 100 -- how many rows will be updated on each iteration of the loop 
    ,   @BatchesToRun INT = 25 -- the max number of times the loop will iterate
    ,   @StartingRecord BIGINT = 1;

-- Get the highest File_PK not already fixed as a starting point.
Select @StartingRecord = MAX(File_PK) From FilesToUpdate where IsUpdated = 0

-- While there are still rows to update and we haven't hit our limit on iterations... 
WHILE (@Rowcount > 0 and @BatchesToRun > 0)   
BEGIN
    print Concat('StartingRecord (Start of Loop): ', @StartingRecord)
    UPDATE FileContent SET  NewFileContent = 'New value here'
    WHERE File_PK BETWEEN (@StartingRecord - @BatchSize + 1) AND @StartingRecord;

    -- @@Rowcount is the number of records affected by the last statement.  If this returns 0, the loop will stop because we've run out of things to update.
    SET @Rowcount = @@ROWCOUNT;
    print Concat('RowCount: ', @Rowcount)

    -- Record which PKs were updated so we know where to start next time around.
    UPDATE FilesToUpdate Set IsUpdated = 1 where File_PK BETWEEN (@StartingRecord - @BatchSize + 1) AND @StartingRecord;

    -- The loop will stop after @BatchSize*@BatchesToRun records are updated. 
    -- If there aren't that many records left to update, the @Rowcount checks will stop it. 
    SELECT @BatchesToRun = @BatchesToRun - 1
    print Concat('Batches Remaining: ',@BatchesToRun)

    -- Set the starting record for the next time through the loop.
    SELECT @StartingRecord -= @BatchSize
    print Concat('StartingRecord (End of Loop): ', @StartingRecord)
END
0
Jen R 3 mai 2019 à 16:16

Cette question a une réponse sur le site des administrateurs de base de données sur StackExchange ici: https://dba.stackexchange.com/questions/127158/how-to-get-sql-insert-and-or-update-to- ne pas verrouiller toute la table sur un serveur ms sql

1
Community 13 avril 2017 à 12:42