Transazioni SQL ACID — Come Garantire Integrità dei Dati e Gestire la Concorrenza nel Tuo Database

Il tuo database perde dati quando due clienti provano ad acquistare l'ultimo prodotto contemporaneamente? O un errore a metà di un pagamento lascia il sistema con uno scontrino emesso ma magazzino non aggiornato? Questi sono i problemi che le transazioni ACID risolvono — e che noi, di Meteora Web, affrontiamo ogni giorno nei progetti di e-commerce e gestionali.

Se non gestisci le transazioni, il tuo codice prima o poi produrrà dati inconsistenti. Non è questione di "se", ma di "quando". E quando capita, il costo è in reclami, rimborsi e fiducia persa.

Perché le transazioni ACID sono essenziali per la tua applicazione

Immagina un ordine su un negozio online:
- Deduci 1 unità dal magazzino.
- Addebiti la carta del cliente.
- Inserisci la riga ordine.
Se dopo l'addebito il server cade, ti ritrovi con soldi presi ma ordine non registrato. Oppure magazzino aggiornato ma pagamento fallito. Le transazioni ACID garantiscono che tutte queste operazioni siano atomiche: o tutto o niente.

Noi lavoriamo con realtà che processano centinaia di ordini al giorno. Senza transazioni, ogni concorrenza genera un buco nei dati. Con le transazioni, il database diventa un arbitro che non lascia passare mezze operazioni.

Come funzionano le proprietà ACID — Atomicità, Coerenza, Isolamento, Durabilità

Le quattro proprietà non sono opzioni, sono requisiti. Ecco cosa significa ciascuna nella pratica:

Atomicità: una transazione è un blocco unico. Se una singola istruzione fallisce, l'intero blocco viene annullato (rollback). Nessuna modifica parziale.

Coerenza: prima e dopo la transazione, il database rimane in uno stato valido. I vincoli (chiavi esterne, unique, check) vengono rispettati. Se rompi una regola, la transazione non committa.

Isolamento: transazioni concorrenti non interferiscono tra loro. Il livello di isolamento (leggiamo dopo) controlla quanta visibilità hanno le une dei dati intermedi delle altre.

Durabilità: una volta committata, la modifica è permanente, anche in caso di crash immediato. I log del database garantiscono il recupero.

Quali problemi di concorrenza risolvono le transazioni?

Tre problemi classici che abbiamo visto in produzione:

• Dirty read (lettura sporca): una transazione legge dati non ancora committati da un'altra. Se quella fa rollback, i dati letti sono falsi.
• Lost update (aggiornamento perso): due transazioni leggono lo stesso valore, lo modificano e lo scrivono. L'ultima sovrascrive la prima, perdendo l'aggiornamento.
• Phantom read (lettura fantasma): una transazione esegue due volte la stessa query e ottiene risultati diversi perché un'altra transazione ha inserito righe nel frattempo.

Noi abbiamo visto un caso reale: un e-commerce di abbigliamento perdeva aggiornamenti delle scorte perché due utenti acquistavano l'ultimo paio di scarpe. Risultato: venduto a due clienti, magazzino a -1. Le transazioni ACID, con il giusto livello di isolamento, risolvono tutto questo.

Come implementare una transazione con rollback in MySQL e PostgreSQL

Il meccanismo base è identico in entrambi i database: BEGIN TRANSACTION, esegui le query, poi COMMIT se tutto ok, ROLLBACK se qualcosa va storto.

Ecco un esempio concreto per un pagamento, usando PHP (PDO) e MySQL:

try {
    $pdo->beginTransaction();

    // 1. Aggiorna magazzino
    $stmt = $pdo->prepare("UPDATE prodotti SET quantita = quantita - 1 WHERE id = :id AND quantita > 0");
    $stmt->execute(['id' => $prodottoId]);

    if ($stmt->rowCount() === 0) {
        throw new Exception("Prodotto non disponibile");
    }

    // 2. Inserisci ordine
    $stmt = $pdo->prepare("INSERT INTO ordini (cliente_id, totale, stato) VALUES (:cliente, :totale, 'completato')");
    $stmt->execute(['cliente' => $clienteId, 'totale' => $totale]);

    $ordineId = $pdo->lastInsertId();

    // 3. Inserisci righe ordine
    $stmt = $pdo->prepare("INSERT INTO righe_ordine (ordine_id, prodotto_id, prezzo) VALUES (:ordine, :prodotto, :prezzo)");
    $stmt->execute(['ordine' => $ordineId, 'prodotto' => $prodottoId, 'prezzo' => $prezzo]);

    // Se tutto è andato bene, committa
    $pdo->commit();

} catch (Exception $e) {
    // Se un passo fallisce, tutto viene annullato
    $pdo->rollBack();
    // Logga l'errore, notifica l'operatore
    error_log($e->getMessage());
}

Attenzione: in MySQL, BEGIN avvia implicitamente una transazione, ma è buona pratica usare START TRANSACTION per chiarezza. In PostgreSQL, funziona allo stesso modo.

Salvataggi intermedi con SAVEPOINT

Se vuoi fare rollback parziale senza buttare via tutta la transazione, usa SAVEPOINT. Utile per processi complessi:

START TRANSACTION;
SAVEPOINT prima_pagamento;
UPDATE conti SET saldo = saldo - 100 WHERE id = 1;
-- Se il pagamento fallisce, rollback solo a questo punto
ROLLBACK TO SAVEPOINT prima_pagamento;
-- e puoi ritentare o gestire l'errore
COMMIT;

Quali livelli di isolamento scegliere e perché?

SQL standard definisce 4 livelli. Più è alto, più protezione, ma meno concorrenza. Ecco la scala:

• READ UNCOMMITTED: permette dirty read. Non lo usiamo mai. Zero integrità.
• READ COMMITTED: evita dirty read. È il default di PostgreSQL e di molti sistemi. Buon compromesso.
• REPEATABLE READ: evita dirty read e lost update. In MySQL (InnoDB) è il default. Garantisce che una riga letta due volte nella stessa transazione dia lo stesso valore.
• SERIALIZABLE: massimo isolamento. Esegue le transazioni come se fossero seriali. Previene anche i phantom read. Costoso in termini di performance.

Noi consigliamo READ COMMITTED per la maggior parte delle applicazioni web, REPEATABLE READ per scenari finanziari dove la consistenza delle letture è critica, e SERIALIZABLE solo quando la concorrenza è bassa o la correttezza assoluta è imprescindibile (es. transazioni bancarie).

In MySQL, imposta il livello globale o per sessione:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

In PostgreSQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Come diagnosticare e risolvere deadlock da transazioni concorrenti

Deadlock: due transazioni si bloccano a vicenda aspettando risorse bloccate dall'altra. Il database li rileva e fa rollback di una delle due. Noi li abbiamo visti in sistemi di prenotazione e contabilità. Per evitarli:
- Accedi alle tabelle sempre nello stesso ordine.
- Mantieni le transazioni brevi.
- Usa SELECT ... FOR UPDATE con moderazione.

Esempio di query che blocca una riga per evitare aggiornamenti concorrenti:

START TRANSACTION;
SELECT quantita FROM prodotti WHERE id = 123 FOR UPDATE;
-- Ora nessun'altra transazione può modificare quella riga finché non committiamo o rollbackiamo
UPDATE prodotti SET quantita = quantita - 1 WHERE id = 123;
COMMIT;

Transazioni ACID e performance — quando evitare o ottimizzare

Ogni transazione apre un blocco e scrive log. Se fai transazioni troppo lunghe o su tabelle enormi, il database rallenta. Regole pratiche:
- Non fare operazioni lente (API esterne, upload file) dentro una transazione.
- Usa COMMIT frequente per lotti di dati, non una singola transazione da 10.000 righe.
- Monitora le attese di blocco con SHOW PROCESSLIST in MySQL o pg_stat_activity in PostgreSQL.

Noi abbiamo ottimizzato un gestionale che processava 50.000 righe al giorno: abbiamo diviso il batch in lotti di 500 record con transazione singola per lotto. Il tempo di esecuzione è crollato del 70%.

Cosa fare adesso

1. Controlla il codice esistente: nelle funzioni critiche (ordini, pagamenti, iscrizioni) ci sono transazioni esplicite? Se no, aggiungile subito.
2. Scegli il livello di isolamento giusto: per impostazione predefinita, InnoDB usa REPEATABLE READ, PostgreSQL usa READ COMMITTED. Valuta se il tuo scenario richiede di cambiarlo.
3. Implementa un pattern di retry: quando il database rileva un deadlock e fa rollback, riprova la transazione (2-3 tentativi con backoff).
4. Testa la concorrenza: usa strumenti come mysqlslap o script custom con 10 thread concorrenti per simulare carichi reali.
5. Leggi la documentazione ufficiale: MySQL Transaction Model e PostgreSQL Transactions.

Noi, di Meteora Web, ogni giorno applichiamo queste pratiche per garantire che i database dei nostri clienti non perdano un centesimo. Se vuoi approfondire il contesto generale delle query relazionali, leggi il nostro articolo principale su SQL e Database Relazionali.