Correction des interblocages de base de données ou des problèmes d’intégrité des données

Les opérations complexes sur les bases de données, en particulier celles qui impliquent des transactions, peuvent entraîner des interblocages ou des incohérences de données difficiles à déboguer.

Copilot Chat peut vous aider en identifiant les points d’une transaction où des blocages ou des interblocages pourraient se produire, et peut suggérer les meilleures pratiques pour l’isolation des transactions ou la résolution des interblocages, telles que l’ajustement des stratégies de verrouillage ou le traitement approprié des exceptions d’interblocage.

Éviter les mises à jour simultanées sur des lignes interdépendantes

Lorsque deux transactions ou plus tentent de mettre à jour les mêmes lignes d’une table de base de données, mais dans des ordres différents, cela peut entraîner une condition d’attente circulaire.

Exemple de scénario

L’extrait SQL suivant met à jour une ligne d’une table, puis effectue une opération qui prend plusieurs secondes, avant de mettre à jour une autre ligne de la même table. Cela pose un problème car la transaction verrouille la ligne id = 1 pendant plusieurs secondes avant que la transaction ne s’achève, libérant ainsi le verrou. Si une autre transaction démarre pendant ce temps et effectue une opération similaire, mais verrouille d’abord la ligne id = 2 afin de pouvoir la mettre à jour, puis tente de verrouiller la ligne id = 1, les deux transactions devront attendre que l’autre se termine, ce qui entraînera un interblocage.

BEGIN TRANSACTION;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
UPDATE my_table SET value = 'Another value' WHERE id = 127;
COMMIT TRANSACTION;

Exemple d'invite 1

Vous pouvez vérifier s’il y a des problèmes avec cette transaction.

Dans l’éditeur, sélectionnez le code de transaction, puis demandez Copilot Chat :

Is this transaction good?

Exemple de réponse 1

Copilot vous indique que « la transaction dans votre code SQL est globalement correcte », mais énumère quelques points à prendre en compte : la durée du verrouillage, la gestion des erreurs et la concurrence. Il est mentionné que « la transaction conserve les verrous pendant une période prolongée, ce qui peut entraîner des blocages ou des interblocages ». La réponse comprend également un code révisé qui ajoute la gestion des erreurs.

Dans ce cas, vous décidez de ne pas ajouter la gestion des erreurs. Pour l’instant, vous souhaitez vous concentrer sur les risques d’interblocage.

Exemple d'invite 2

Entrez ce prompt de suivi :

How can I rewrite this code to reduce the chance of deadlock to a minimum while not adversely affecting performance. Tell me anything else I can do to avoid a deadlock. Note: I don't want to add error handling at this time.

Exemple de réponse 2

Copilot fournit le conseil suivant :

• Ordre d’accès : assurez-vous que toutes les transactions accèdent aux tables et aux lignes dans le même ordre.
• Maintenir des transactions courtes : réduisez au minimum la durée pendant laquelle une transaction détient des verrous.
• Utiliser des niveaux d’isolation appropriés : utilisez le niveau d’isolation le plus bas qui réponde à vos besoins.

Voici une version révisée de votre code :

BEGIN TRANSACTION;
-- Update the rows in a consistent order
UPDATE my_table SET value = 'Another value' WHERE id = 127;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
COMMIT TRANSACTION;

Éviter les recherches non indexées

La recherche de colonnes de table qui n’ont pas été indexées entraîne généralement un balayage complet de la table. Cette opération peut être très lente et gourmande en ressources, augmentant ainsi la probabilité de goulots d’étranglement des performances.

Exemple de scénario

Le langage SQL suivant entraînera un balayage complet de la table si some_column n’est pas indexé :

BEGIN TRANSACTION;
SELECT * FROM my_table WHERE some_column = 'some_value';
-- More actions here, then:
COMMIT TRANSACTION;

Exemples d’invite

Poser la question suivante à Copilot révélera généralement le problème potentiel.

How can I optimize this transaction?

Exemple de réponse

Copilot apporte quelques conseils et indique que la colonne doit être indexée :

Pour optimiser la transaction, vous devez vous assurer qu’elle est aussi courte que possible afin de réduire les verrouillages et d’améliorer les performances. Voici quelques conseils :

1. Réduire au minimum le travail effectué dans la transaction : n’incluez que les opérations nécessaires dans la transaction.
2. Utiliser les index appropriés : assurez-vous que les colonnes utilisées dans la clause WHERE sont indexées.
3. Éviter les transactions longues : la durée de la transaction doit être courte pour éviter les problèmes de verrouillage.

Le code suggéré dans la réponse comprend la commande SQL pour l’indexation de la colonne :

`CREATE INDEX idx_some_column ON my_table(some_column);`

Éviter les transactions longues

Il est important d’éviter, dans la mesure du possible, les requêtes et les transactions longues, car elles peuvent fortement dégrader les performances et provoquer des goulets d’étranglement pour d’autres opérations. De plus, elles peuvent consommer beaucoup de mémoire et d’espace de journal, ce qui rend votre application plus vulnérable aux défaillances dans les environnements à forte charge.

Exemple de scénario

Le code SQL suivant crée de nouvelles lignes dans target_table, contenant des données provenant de champs sélectionnés de lignes dans source_table lorsque diverses conditions sont remplies. Si source_table est très volumineux, la transaction risque d’être longue.

BEGIN TRANSACTION;

INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
SELECT first_name, last_name, email, department, job_title, start_date
FROM source_table
WHERE (department = 'Engineering' AND (salary > 95000 OR grade > 5))
  OR (department = 'Marketing' AND performance_rating = 'Excellent');

COMMIT;

Exemples d’invite

Vous pouvez demander à Copilot s’il est possible d’éviter les transactions longues.

source_table is an extremely large table. Without altering the table, how can I ensure this SQL doesn't become a long-running query, that blocks other operations?

Exemple de réponse

Copilot suggère les stratégies suivantes :

• Traitement par lots : traiter les données par petits lots au lieu d’une seule grande transaction. Cette opération peut être réalisée à l’aide d’une boucle avec une limite et un décalage.
• Indexation : assurez-vous que les colonnes utilisées dans la clause WHERE (department, salary, grade et performance_rating) sont indexées pour accélérer la requête.
• Verrouillage : utilisez le verrouillage au niveau de la ligne pour éviter de verrouiller l’ensemble de la table.

Il fournit l’exemple suivant de la manière dont vous pouvez mettre en œuvre le traitement par lots. Ce langage SQL traite les données par lots de 1 000 lignes à la fois, ce qui réduit la charge de la base de données et empêche les transactions longues.

DECLARE @BatchSize INT = 1000;
DECLARE @Offset INT = 0;
DECLARE @RowCount INT;

SET @RowCount = @BatchSize;

WHILE @RowCount = @BatchSize
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
    SELECT first_name, last_name, email, department, job_title, start_date
    FROM source_table
    WHERE (department = 'Engineering' AND salary > 95000)
       OR (department = 'Engineering' AND years_of_experience > 5)
       OR (department = 'Marketing' AND performance_rating = 'Excellent');
    ORDER BY primary_key_column
    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;

    SET @RowCount = @@ROWCOUNT;
    SET @Offset = @Offset + @BatchSize;

    COMMIT;
END;

Copilot vous conseille de remplacer primary_key_column dans le code proposé par le nom de la colonne de clé primaire de source_table.

Éviter les problèmes d’intégrité des données

Il est important que les informations figurant dans vos bases de données restent exactes, cohérentes et complètes. Des requêtes mal conçues peuvent entraîner des données manquantes ou incorrectes.

Exemple de scénario

La transaction SQL suivante supprime un enregistrement d’employé et crée un journal de suppression. Toutefois, il est possible que l’opération DELETE réussisse et que l’opération INSERT échoue, auquel cas il n’y a pas d’enregistrement de journal pour la suppression.

BEGIN TRANSACTION;

DELETE FROM employees
WHERE employee_id = 101;

INSERT INTO deletion_log (employee_id, deletion_date, reason)
VALUES (101, GETDATE(), 'Voluntary Resignation');

COMMIT TRANSACTION;

Exemples d’invite

How can I ensure that the DELETE and INSERT operations are only performed if both succeed?

Copilot vous indique que vous y parvenez en utilisant une transaction avec gestion des erreurs. L’exemple suivant est fourni en utilisant la construction TRY...CATCH du serveur SQL :

BEGIN TRY
    BEGIN TRANSACTION;

    DELETE FROM employees
    WHERE employee_id = 101;

    INSERT INTO deletion_log (employee_id, deletion_date, reason)
    VALUES (101, GETDATE(), 'Voluntary Resignation');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Handle the error, e.g., log it or re-throw
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

Le code suggéré garantit que si l’opération DELETE ou INSERT échoue, la transaction est annulée et aucune modification n’est apportée à la base de données.

Pour aller plus loin

• « Ingénierie d’invite pour GitHub Copilot »
• « Meilleures pratiques pour l’utilisation de GitHub Copilot »