Les Verrous Oracle : Guide Complet pour Débutants

null

—

Les Verrous Oracle : Comprendre et Maîtriser le Verrouillage des Données

Les verrous Oracle sont des mécanismes fondamentaux qui garantissent l’intégrité des données dans un environnement multi-utilisateurs. Lorsque plusieurs sessions accèdent simultanément aux mêmes enregistrements, les verrous Oracle empêchent les conflits et les incohérences. Comprendre leur fonctionnement est indispensable pour tout développeur ou DBA travaillant sur une base de données Oracle en production.

Définition et utilisation des verrous Oracle

Un verrou Oracle (lock en anglais) est un mécanisme de contrôle de la concurrence qui protège une ressource — ligne, table ou segment — contre des modifications simultanées non contrôlées. Oracle implémente un modèle de verrouillage dit optimiste et non bloquant pour les lectures : une lecture ne pose pas de verrou sur les données, ce qui distingue Oracle de nombreux autres SGBD.

Les principaux types de verrous Oracle

DML Locks (verrous de données) : posés automatiquement lors d’opérations INSERT, UPDATE ou DELETE. Ils opèrent au niveau ligne (Row-Level Lock) et table.
DDL Locks (verrous de dictionnaire) : posés lors d’opérations de définition comme CREATE, ALTER ou DROP.
Verrous manuels (Table Locks) : posés explicitement via l’instruction LOCK TABLE pour un contrôle plus précis.
Latches et Mutexes : verrous internes bas niveau gérés directement par le moteur Oracle, non accessibles au développeur.

Cas d’usage en entreprise

Dans un contexte de gestion de stock, deux opérateurs peuvent simultanément tenter de réserver la même quantité d’articles. Sans verrou, les deux transactions liraient un stock disponible identique et le mettraient à jour indépendamment, provoquant une survente. Oracle pose automatiquement un verrou de ligne lors du premier UPDATE, forçant la seconde transaction à attendre la libération du verrou avant de procéder.

Syntaxe des verrous Oracle

Oracle pose la plupart des verrous DML de manière automatique et transparente. Cependant, il est possible de les contrôler manuellement via deux instructions principales :

1. LOCK TABLE — Verrouillage explicite d’une table

LOCK TABLE nom_table IN mode_verrou MODE [NOWAIT | WAIT n];

Paramètres essentiels :

nom_table : nom de la table à verrouiller (plusieurs tables séparées par des virgules sont autorisées).
mode_verrou : définit le niveau de restriction. Les modes les plus utilisés sont :
- ROW SHARE : permet l’accès concurrent mais interdit le verrouillage exclusif de la table.
- ROW EXCLUSIVE : utilisé implicitement lors des DML ; interdit le verrouillage partagé.
- SHARE : autorise la lecture concurrente mais bloque toute modification.
- EXCLUSIVE : accès exclusif total, aucune autre session ne peut lire ni écrire.
NOWAIT : si le verrou ne peut pas être obtenu immédiatement, Oracle renvoie une erreur au lieu d’attendre.
WAIT n : Oracle attend au maximum n secondes avant de retourner une erreur.

2. SELECT … FOR UPDATE — Verrouillage de lignes sélectionnées

SELECT colonne1, colonne2
FROM nom_table
WHERE condition
FOR UPDATE [OF colonne] [NOWAIT | WAIT n] [SKIP LOCKED];

FOR UPDATE : verrouille les lignes retournées pour empêcher leur modification par d’autres sessions jusqu’au prochain COMMIT ou ROLLBACK.
SKIP LOCKED : ignore les lignes déjà verrouillées par d’autres sessions, utile pour les traitements de file d’attente.

Exemples pratiques de verrous Oracle

Exemple 1 — Verrouillage de lignes pour une mise à jour de stock

Contexte métier : Un système de gestion d’entrepôt doit réserver des articles pour une commande. La session verrouille les lignes concernées avant de procéder à la mise à jour afin d’éviter toute double réservation.

-- Étape 1 : Sélection et verrouillage des lignes de stock concernées
-- WAIT 5 : on attend au maximum 5 secondes avant d'abandonner
SELECT article_id,
       designation,
       quantite_disponible
FROM   stock
WHERE  entrepot_id = 10
  AND  quantite_disponible > 0
FOR UPDATE WAIT 5;

-- Étape 2 : Mise à jour de la quantité réservée
-- Le verrou est maintenu jusqu'au COMMIT
UPDATE stock
SET    quantite_disponible = quantite_disponible - 1
WHERE  article_id = 1042
  AND  entrepot_id = 10;

-- Étape 3 : Validation de la transaction — libère tous les verrous
COMMIT;

Dans cet exemple, si une autre session tente de modifier les mêmes lignes pendant l’exécution, elle sera bloquée pendant 5 secondes maximum, puis recevra une erreur ORA-30006. Cela garantit la cohérence des données de stock.

Exemple 2 — Verrouillage exclusif d’une table pour un traitement de masse

Contexte métier : En fin de mois, le service comptable lance un calcul de clôture sur la table ECRITURES_COMPTABLES. Pendant ce traitement, aucune écriture ne doit être insérée ou modifiée.

-- Verrouillage exclusif de la table pour la durée du traitement de clôture
-- NOWAIT : si la table est déjà verrouillée, on échoue immédiatement
LOCK TABLE ecritures_comptables IN EXCLUSIVE MODE NOWAIT;

-- Traitement de clôture mensuelle
INSERT INTO cloture_mensuelle (mois, total_debit, total_credit)
SELECT TO_CHAR(date_ecriture, 'YYYY-MM'),
       SUM(montant_debit),
       SUM(montant_credit)
FROM   ecritures_comptables
WHERE  EXTRACT(MONTH FROM date_ecriture) = EXTRACT(MONTH FROM SYSDATE) - 1
GROUP BY TO_CHAR(date_ecriture, 'YYYY-MM');

-- Libération du verrou après la fin du traitement
COMMIT;

Ici, le mode EXCLUSIVE garantit qu’aucune autre session ne peut accéder à la table en écriture pendant la clôture. L’option NOWAIT évite tout blocage silencieux : si une session tient déjà un verrou, l’erreur est remontée immédiatement à l’application.

Erreurs courantes avec les verrous Oracle

Erreur : ORA-00054 — Resource busy and acquire with NOWAIT specified

Message complet : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Cause : Cette erreur survient lorsque vous utilisez LOCK TABLE … IN … MODE NOWAIT ou SELECT … FOR UPDATE NOWAIT et que la ressource est déjà verrouillée par une autre session. Oracle ne peut pas obtenir le verrou immédiatement et, conformément à l’instruction NOWAIT, renvoie une erreur plutôt que d’attendre.

Solution :

Vérifiez les sessions qui bloquent via la vue V$LOCK et V$SESSION :

-- Identifier les sessions bloquantes
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       l.type,
       l.lmode,
       l.request
FROM   v$lock    l
JOIN   v$session s ON l.sid = s.sid
WHERE  l.block = 1;

Si le blocage est anormal (session inactive depuis longtemps), le DBA peut mettre fin à la session : ALTER SYSTEM KILL SESSION 'sid,serial#';
Remplacez NOWAIT par WAIT n si un délai d’attente raisonnable est acceptable dans votre application.

Résumé des verrous Oracle

Concept	Détail
Verrou automatique DML	Posé implicitement par `INSERT`, `UPDATE`, `DELETE` au niveau ligne
Verrou manuel table	`LOCK TABLE … IN … MODE`
Verrou de lignes	`SELECT … FOR UPDATE`
Modes principaux	ROW SHARE, ROW EXCLUSIVE, SHARE, EXCLUSIVE
Libération du verrou	Automatique au `COMMIT` ou `ROLLBACK`
Lecture non bloquante	Oracle ne pose jamais de verrou sur un `SELECT` simple
Vue de diagnostic	`V$LOCK`, `V$SESSION`
Erreur fréquente	`ORA-00054` : ressource occupée avec `NOWAIT`

Deux bonnes pratiques Oracle

Privilégiez les transactions courtes : Plus une transaction est longue, plus elle maintient ses verrous et risque de bloquer d’autres sessions. Effectuez vos COMMIT le plus tôt possible après la fin du traitement nécessaire.
Utilisez SKIP LOCKED pour les files d’attente : Dans les architectures orientées traitement par lots ou files de messages, SELECT … FOR UPDATE SKIP LOCKED permet à plusieurs sessions de travailler en parallèle sans se bloquer mutuellement, améliorant considérablement les performances.

Aller plus loin

Pour approfondir votre maîtrise de la concurrence et de la gestion des transactions dans Oracle, nous vous recommandons les sujets suivants :

Les transactions Oracle : COMMIT, ROLLBACK et SAVEPOINT — comprendre le cycle de vie d’une transaction et son lien direct avec la libération des verrous.
Les deadlocks Oracle (ORA-00060) — détecter, analyser et résoudre les interblocages causés par des conflits de verrous entre sessions.
Surveiller les sessions avec V$LOCK et V$SESSION — utiliser les vues de performance dynamiques pour diagnostiquer les problèmes de concurrence en production.