LLI Wiki — My Digital Knowledge Base

Pourquoi un SELECT attend les verrous de lignes d'un INSERT ou d'un UPDATE

Comprendre le blocage d'un SELECT par un INSERT ou un UPDATE, comparer READPAST, NOLOCK et RCSI, et trouver la session qui détient le verrou.

Un SELECT peut attendre un INSERT ou un UPDATE parce que l'écrivain détient un verrou sur une ligne ou une page dont le lecteur a besoin. Cet article décrit les options et la manière de trouver la session qui détient le verrou. C'est une synthèse, réécrite, d'une réponse Stack Exchange sur le sujet : How to get SQL INSERT and/or UPDATE to not lock entire table on MS SQL Server.

Pourquoi un lecteur attend

Options

Option INSERT UPDATE Compromis
Indice READPAST Ignore les lignes verrouillées. Ignore les lignes verrouillées, donc le lecteur ne voit pas la version validée d'une ligne en cours de modification. Les résultats peuvent être incomplets.
Indice NOLOCK Lit sans attendre. Lit sans attendre. Peut renvoyer des données non validées (lectures sales).
Read Committed Snapshot Isolation (RCSI) Les lecteurs voient la dernière version validée de chaque ligne et n'attendent pas. Idem INSERT. Les versions de lignes sont stockées dans tempdb, qui consomme plus d'espace.

RCSI donne le résultat attendu le plus souvent, au prix d'une utilisation plus forte de tempdb.

Trouver la session qui détient le verrou

-- Verrous détenus par les sessions de l'instance
EXEC sp_lock;

-- Sessions, bloquantes et leurs verrous (sp_WhoIsActive doit être installée au préalable)
EXEC sp_WhoIsActive @get_locks = 1;

Trouver la ligne ou la page derrière un verrou

%%LOCKRES%% est une colonne virtuelle non documentée qui renvoie le hachage de la ressource de verrou de chaque ligne. Elle permet de retrouver la ligne derrière un verrou de clé, puis sa page.

SELECT %%LOCKRES%%, *
FROM dbo.Orders
WHERE %%LOCKRES%% = '(64e4c5fecce7)';

-- Lister les pages de l'index 4 de la table, puis afficher une page
DBCC IND (AppDb, 'dbo.Orders', 4);
DBCC PAGE (AppDb, 1, 22708, 3) WITH TABLERESULTS;

Nombre de lignes par partition

SELECT t.object_id, t.name, p.partition_id, p.partition_number, p.rows
FROM sys.partitions AS p
    INNER JOIN sys.tables AS t
        ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
    AND t.name = 'Orders'
ORDER BY p.partition_number;

-- Nombre de lignes par partition avec la fonction $PARTITION
SELECT $PARTITION.FullOrderDateKeyRangePFN(FULLDATE) AS PARTITIONID,
       COUNT(*) AS ROW_COUNT
FROM dbo.FactInternetSales_Partitioned
GROUP BY $PARTITION.FullOrderDateKeyRangePFN(FULLDATE)
ORDER BY PARTITIONID;

Remarques

Mastodon LLIMastodon SocialFosstodon