RCSI et SNAPSHOT : passer en isolation par versions de lignes.

Le principe

En isolation READ COMMITTED classique, une lecture prend un verrou partagé : elle attend si une écriture est en cours sur la même ligne, et fait attendre les écritures pendant qu'elle lit. Avec le versionnement, SQL Server conserve l'ancienne version des lignes modifiées dans la base tempdb (le version store). Un lecteur obtient la dernière version validée sans attendre, et sans bloquer personne. Les écritures restent bloquées entre elles.

Deux mécanismes existent, indépendants l'un de l'autre :

• RCSI (Read Committed Snapshot Isolation) : option de base de données qui change le comportement du niveau READ COMMITTED. Chaque instruction voit les données validées au moment où elle commence. Aucun changement de code.
• SNAPSHOT : un niveau d'isolation à demander explicitement. Toute la transaction voit une image cohérente de la base à son démarrage, quoi que fassent les autres.

Activer RCSI sur une base

-- RCSI : nécessite d'être seul sur la base pendant l'opération
ALTER DATABASE [MaBase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Vérification
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'MaBase';

• WITH ROLLBACK IMMEDIATE déconnecte les autres sessions et annule leurs transactions. Planifiez l'opération hors des heures d'activité, et prévenez les utilisateurs. Sans cette clause, la commande attend que la base soit libre.
• Aucun redémarrage n'est nécessaire. Le changement s'applique à toutes les requêtes en READ COMMITTED de la base, sans modifier le code.
• L'option ne s'active pas sur master, tempdb ni msdb.
• Pour revenir en arrière : ALTER DATABASE [MaBase] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE;.

Passer une transaction en SNAPSHOT

Il faut d'abord autoriser ce niveau sur la base. Cette commande ne demande pas de déconnecter les utilisateurs, mais attend que les transactions en cours se terminent.

ALTER DATABASE [MaBase] SET ALLOW_SNAPSHOT_ISOLATION ON;

Ensuite, la transaction demande le niveau SNAPSHOT avant de commencer :

-- Avant : transaction simple, en READ COMMITTED (niveau par défaut)
BEGIN TRAN;
    SELECT Solde FROM dbo.Comptes WHERE Id = 42;
    UPDATE dbo.Comptes SET Solde = Solde - 100 WHERE Id = 42;
COMMIT;

-- Après : la même transaction en SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT Solde FROM dbo.Comptes WHERE Id = 42;      -- image cohérente à l'ouverture de la transaction
    UPDATE dbo.Comptes SET Solde = Solde - 100 WHERE Id = 42;
COMMIT;

• Le réglage s'applique à la session, jusqu'à ce qu'il soit changé. Pour revenir au défaut : SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.
• En SNAPSHOT, deux transactions peuvent lire la même ligne puis vouloir la modifier. La seconde échoue avec l'erreur 3960 (conflit de mise à jour), car la ligne a changé depuis son image. L'application doit réessayer la transaction, comme pour un interblocage.
• Un traitement de lecture seule (rapport, export) en SNAPSHOT donne un résultat cohérent du début à la fin, sans bloquer les écritures : c'est son meilleur usage.

Le coût à connaître

• tempdb travaille plus : le version store consomme de l'espace et des entrées-sorties. Vérifiez la taille et l'emplacement de tempdb avant d'activer : voir l'article sur tempdb saturée.
• Chaque ligne modifiée gagne quelques octets (14 octets environ pour l'information de version), ce qui peut provoquer des fractionnements de pages sur des tables très modifiées.
• Une transaction longue empêche le nettoyage : tant qu'une transaction SNAPSHOT ancienne est ouverte, SQL Server doit conserver toutes les versions depuis son début. Le version store grossit.
• Les lectures ne bloquent plus : un code qui s'appuyait sur ce blocage pour se synchroniser (file d'attente, « vérifier puis insérer ») peut se comporter autrement. Dans ce cas, forcez le comportement précédent avec les indications de verrouillage (WITH (UPDLOCK, HOLDLOCK) ou READCOMMITTEDLOCK).

Surveiller le version store

-- Espace occupé par le version store, par base (SQL Server 2017 et suivants)
SELECT DB_NAME(database_id) AS base,
       reserved_space_kb / 1024.0 AS version_store_mo
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_space_kb DESC;

-- Transactions SNAPSHOT les plus anciennes : ce sont elles qui retiennent le nettoyage
SELECT session_id, transaction_sequence_num, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

Sur les versions antérieures à 2017, l'espace réservé au version store se lit dans tempdb.sys.dm_db_file_space_usage (colonne version_store_reserved_page_count). À partir de SQL Server 2019, la récupération accélérée de base de données (ADR) peut conserver les versions dans la base elle-même plutôt que dans tempdb.

Quand l'utiliser

• RCSI : quand les blocages entre lecteurs et écrivains sont le problème principal, et que l'on ne veut pas modifier le code. C'est le choix par défaut dans Azure SQL Database, et le réglage usuel pour une application transactionnelle.
• SNAPSHOT : pour une transaction ou un rapport qui exige une vue cohérente de bout en bout.
• Testez toujours en préproduction, avec une charge réaliste, en observant tempdb, les attentes et le comportement de l'application. Voyez aussi les blocages et les interblocages que cela supprime.