Comment gérez-vous les problèmes de concurrence d'accès à la base de données ?

-- Transaction A
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT;

-- Transaction B (simultanée)
BEGIN;
UPDATE comptes SET solde = solde - 50 WHERE id = 2;
UPDATE comptes SET solde = solde + 50 WHERE id = 1;
COMMIT;
-- Résultat : chaque transaction attend le verrou de l'autre → DEADLOCK

-- Niveau Read Committed (par défaut)
BEGIN;
SELECT * FROM produits WHERE id = 1;
COMMIT;

-- Niveau Serializable (pour les transactions critiques)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM produits WHERE id = 1;
UPDATE produits SET stock = stock - 1 WHERE id = 1;
COMMIT;

BEGIN TRANSACTION;
-- Verrou exclusif sur la ligne
SELECT * FROM produits WITH (UPDLOCK) WHERE id = 1;
UPDATE produits SET stock = stock - 1 WHERE id = 1;
COMMIT;

// Lecture du document avec sa version
const doc = db.collection.find({ _id: 1 });
const version = doc.version;

// Modification
db.collection.updateOne(
  { _id: 1, version: version },
  { $set: { stock: 99 }, $inc: { version: 1 } }
);
// Si version a changé, la mise à jour échoue

START TRANSACTION;
-- Verrou exclusif sur la ligne de la chambre
SELECT * FROM chambres WHERE id = 1 FOR UPDATE;

-- Vérification et réservation
UPDATE chambres SET disponible = FALSE WHERE id = 1 AND disponible = TRUE;

-- Insertion de la réservation
INSERT INTO reservations (chambre_id, client_id, date) VALUES (1, 123, NOW());

COMMIT;

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;