// Référence des erreurs SQL Server

Erreur SQL Server 1205 : Interblocage, transaction choisie comme victime

Deux sessions attendaient chacune des verrous détenus par l’autre. SQL Server a annulé cette transaction pour que l’autre puisse se terminer. Réessayez-la.

Numéro d'erreur
1205
Sévérité
13
Catégorie
Transactions et verrouillage
Message d'erreur
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Ce que cela signifie

Un interblocage est un cycle d'attente de verrous : la session A détient un verrou dont B a besoin, tandis que B détient un verrou dont A a besoin. Le moniteur d'interblocage de SQL Server détecte le cycle et y met fin en choisissant une victime, annulant toute la transaction de cette session et levant l'erreur 1205. La victime est la session avec la priorité la plus basse DEADLOCK_PRIORITY, et parmi les priorités égales, celle dont l'annulation est la moins coûteuse.

L'autre session continue comme si de rien n'était. Les interblocages sont un résultat normal en cas de concurrence ; le but est de les rendre rares et de réessayer lorsqu'ils se produisent.

Causes courantes

  • Deux chemins de code mettent à jour les mêmes tables ou lignes dans un ordre opposé.
  • Une lecture sous le niveau d’isolation read committed par défaut, qui attend un écrivain qui attend lui-même le verrou partagé du lecteur (fréquent avec les recherches via des index non cluster).
  • Index manquants qui font que les mises à jour et les suppressions parcourent et verrouillent beaucoup plus de lignes qu'elles ne changent.
  • Transactions longues, ou interaction de l'utilisateur au milieu d'une transaction ouverte.
  • Niveaux d'isolation plus élevés tels que SERIALIZABLE détenant des verrous de plage.

Comment le corriger

  1. Réessayez la transaction dans l'application lorsqu'elle échoue avec 1205. En T-SQL, une boucle de réessai ressemble à ceci :

    DECLARE @tries int = 0;
    WHILE @tries < 3
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1;
            UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2;
            COMMIT;
            BREAK;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW;
            SET @tries += 1;
            WAITFOR DELAY '00:00:00.100';
        END CATCH
    END;
  2. Lisez le graphe d'interblocage. La session intégrée system_health Une session Événements étendus enregistre chaque interblocage.

    SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph
    FROM (SELECT CAST(target_data AS xml) AS target_data
          FROM sys.dm_xe_session_targets AS t
          JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
          WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS data
    CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed);
  3. Accédez aux tables et aux lignes dans le même ordre à chaque transaction, et gardez les transactions courtes.

  4. Ajoutez des index afin que les écritures ne touchent que les lignes qu'elles modifient ; le graphe d'interblocage montre sur quel index chaque verrou se trouvait.

  5. Envisagez l'isolation d'instantané en lecture validée (read committed snapshot), afin que les lecteurs utilisent des versions de lignes plutôt que des verrous partagés et n'entrent plus en interblocage avec les écrivains.

    ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  6. Marquez le travail moins important comme victime préférée.

    SET DEADLOCK_PRIORITY LOW;

Reproduisez-le en toute sécurité

Exécutez les étapes dans deux connexions séparées vers une base de données de test, dans l'ordre indiqué, puis annulez les modifications de la session survivante et supprimez la table.

-- Setup (once):
CREATE TABLE dbo.DL (id int PRIMARY KEY, v int);
INSERT INTO dbo.DL VALUES (1, 0), (2, 0);
-- Session 1:
BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2;
UPDATE dbo.DL SET v = 2 WHERE id = 1;  -- waits
-- Session 1:
UPDATE dbo.DL SET v = 1 WHERE id = 2;  -- one session gets Msg 1205

Questions

Mes données sont-elles perdues après un interblocage ?

La transaction entière de la victime est annulée, donc aucune de ses modifications n'a été enregistrée. Rien d'autre n'est affecté. Exécutez de nouveau la transaction.

Le NOLOCK résout-il les interblocages ?

Il peut empêcher les lecteurs de prendre des verrous partagés, mais il permet les lectures sales et incohérentes (lignes manquantes ou dupliquées). L'isolement read committed snapshot supprime le blocage lecteur-écrivain sans ces risques.

Erreurs liées

Plus d'erreurs de transactions et de verrouillage

Le code et le texte du message sont issus de Référence des événements et erreurs du moteur de base de données Microsoft. Parcourir la liste complète Liste des erreurs de SQL Server ou Rechercher toutes les erreurs de base de données.

// moins d'erreurs, corrections plus rapides

Travailler sur SQL Server dans SQLly

Un client SQL natif pour macOS, Windows et Linux avec IntelliSense conscient du schéma, des garde-fous pour les instructions risquées et des messages d’erreur clairs, pour SQL Server et les autres bases de données que vous utilisez.