🔗 7) Concurrence, locks & contention

Signaux (AWR/ASH)

• Wait class Concurrency / events liés aux verrous.
• Sessions actives qui attendent : blocking_session non null.
• Incidents : timeouts applicatifs / p99 qui explose.

Blocking chain : méthode simple

1. Identifier les sessions WAITING (event + seconds_in_wait).
2. Récupérer blocking_session (bloqueur).
3. Récupérer SQL_ID des 2 côtés + module/action (app).
4. Identifier l’objet locké (table/index) si possible.

Objectif :
résoudre la cause (transaction longue / pattern)
pas “tuer des sessions” en aveugle.
          

Actions (ordre “safe”)

1. Réduire durée transaction (commit plus tôt, scope plus petit).
2. Indexer les FK (pour éviter locks table lors parent DML).
3. Éviter DDL en prod sur tables actives.
4. Revoir SELECT…FOR UPDATE (granularité, order, NOWAIT/SKIP LOCKED).

Causes fréquentes

• Hard parsing massif (SQL non bindé / SQL unique).
• Explosion child cursors (bind mismatch, cursor sharing).
• Shared pool pressure / invalidations (DDL, stats, etc.).
• Application qui génère trop de requêtes différentes (lack of normalization).

Preuve / indices

• AWR : événements “library cache”, “cursor”, “mutex” (selon version).
• ASH : beaucoup de sessions sur mêmes waits/concurrency internes.
• V$SQL : parse_calls élevé, nombreux child cursors, plan instability.

Actions (pragmatiques)

1. Normaliser SQL + utiliser bind variables correctement.
2. Réduire invalidations (DDL, changements de stats non maîtrisés).
3. Réduire le nombre de requêtes “uniques” générées.
4. Valider après : parse calls ↓, contention ↓, DB CPU utile ↑.

Signaux observables

• Waits type buffer busy / contention sur blocs (selon version).
• ASH : sessions sur mêmes objets/segments.
• Dégradation proportionnelle au nombre de sessions concurrentes.

Actions possibles

1. Répartir les inserts : partitions, hash keys, design de clé.
2. Revoir l’indexation (éviter un seul index hotspot).
3. Réduire concurrence sur la même ressource (batching, sharding logique).

Règle :
un hot block se corrige souvent par “design”
plus que par paramètre.
          

Validation

Comparer avant/après :
- waits de contention ↓
- throughput TPS ↑
- latence p95/p99 ↓
- CPU utile stable
          

Patterns applicatifs typiques

• Deux transactions qui mettent à jour les mêmes tables mais dans un ordre différent.
• Traitements batch qui “mixent” des ordres d’accès non déterministes.
• Locks explicites + logique multi-étapes trop longue.

Enquête

1. Récupérer la trace / deadlock graph (alert log/trace).
2. Identifier les SQL et les objets concernés.
3. Reproduire en staging si possible (workload minimal).

Fix (le vrai)

1. Imposer un ordre d’accès identique (table A puis table B, toujours).
2. Réduire durée transaction (moins de locks tenus).
3. Utiliser NOWAIT/SKIP LOCKED selon cas métier.

Alternatives

• Batch commit (taille contrôlée, backpressure).
• SKIP LOCKED / NOWAIT si la sémantique métier le permet.
• Sharding logique : répartir par clé.
• Fenêtres de change + runbook rollback.

OLTP : objectifs

- Transactions courtes
- Peu de locks tenus longtemps
- Accès déterministes (ordre stable)
- Binds & SQL stable (moins de parsing)
          

Batch : objectifs

- Backpressure (limiter parallélisme)
- Fenêtre dédiée
- Partitionnement / chunking
- Mesure avant/après (AWR/ASH)
          

Triage (ordre)

1. Confirmer wait class = Concurrency.
2. ASH : récupérer blocking_session et SQL_ID.
3. Identifier objet locké (table/index) si possible.
4. Déterminer : transaction longue ? pattern app ? hot blocks ?

Fix (pragmatiques)

1. Réduire durée transaction / ordre d’accès stable.
2. Indexer FK / prédicats pour réduire lock scope.
3. Optimiser top SQL qui tient les locks.
4. Découper batch + limiter parallélisme.

Rollback

- revert change applicatif
- drop index (si ajouté)
- revert paramètres (si modifiés)
- désactiver job/batch (si source)
- validation : waits contention ↓
          

Cycle deadlock

flowchart LR
  A[Session A] -->|lock R1| R1[Ressource 1]
  A -->|wait R2| R2[Ressource 2]
  B[Session B] -->|lock R2| R2
  B -->|wait R1| R1
  R1 --> X[Cycle => ORA-00060]
          

Locks (approche)

-- Vue simple : qui bloque qui (si dispo)
SELECT * FROM dba_blockers;
SELECT * FROM dba_waiters;

-- V$LOCK (selon usage, nécessite interprétation)
SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock
WHERE block = 1 OR request > 0
ORDER BY sid;
          

Les vues DBA_BLOCKERS/DBA_WAITERS sont souvent plus lisibles que V$LOCK brute.

ASH : top blocking sessions

SELECT blocking_session, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - (15/1440)
  AND blocking_session IS NOT NULL
GROUP BY blocking_session
ORDER BY samples DESC;
          

SELECT sql_id, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - (15/1440)
  AND wait_class = 'Concurrency'
GROUP BY sql_id
ORDER BY samples DESC FETCH FIRST 15 ROWS ONLY;
          

Deadlocks : point d’entrée

-- En pratique :
1) trouver ORA-00060 dans alert log
2) ouvrir la trace associée (deadlock graph)
3) identifier SQL + objets + ordre d’accès
4) corriger l’app (ordre stable / scope court)