Controllo della concorrenza nei DBMS: come locking, MVCC e strategie ottimistiche mantengono la coerenza dei dati

Il controllo della concorrenza è l'insieme di meccanismi che un sistema di gestione di database (DBMS) utilizza per gestire transazioni simultanee senza corrompere i dati. Quando più utenti o processi leggono e scrivono contemporaneamente, l'accesso incontrollato porta ad anomalie dei dati, come aggiornamenti persi, letture sporche e risultati incoerenti che possono propagarsi nei sistemi a valle.

Il controllo della concorrenza applica la “I” (Isolamento) in ACID: le transazioni concorrenti dovrebbero produrre lo stesso risultato come se fossero state eseguite una dopo l'altra, una proprietà nota come serializzabilità. Senza di esso, un'applicazione bancaria potrebbe perdere trasferimenti, un sistema di inventario potrebbe vendere prodotti in eccesso e una pipeline di analisi potrebbe produrre report basati su dati scritti a metà.

Questa guida copre i problemi principali della concorrenza, i meccanismi principali per risolverli — locking, controllo della concorrenza multi-versione (MVCC) e strategie ottimistiche e pessimistiche — insieme ai livelli di isolamento, alla gestione dei deadlock e a indicazioni pratiche per scegliere l'approccio giusto per il tuo carico di lavoro.

Perché il controllo della concorrenza è importante

I moderni sistemi di database gestiscono regolarmente da migliaia a milioni di transazioni concorrenti al secondo. Ogni volta che un utente invia una query, aggiorna un record o avvia una pipeline ETL, il DBMS deve coordinare quell'operazione insieme a ogni altra operazione che avviene nello stesso momento. Senza controllo della concorrenza, le transazioni intersecate producono risultati imprevedibili e errati.

Considera uno scenario concreto: due utenti aggiornano contemporaneamente lo stesso saldo del conto bancario. L'utente A legge il saldo ($1.000), sottrae $200 e scrive $800. Anche l'utente B legge $1.000, aggiunge $500 e scrive $1.500. Il risultato corretto dipende dall'ordine — $800 o $1.500 — ma senza coordinamento, una scrittura sovrascrive semplicemente l'altra. Questo è un classico aggiornamento perso, una delle diverse anomalie che il controllo della concorrenza esiste per prevenire.

Il controllo della concorrenza applica la serializzabilità: l'esito dell'esecuzione concorrente deve corrispondere a un qualche ordinamento seriale delle stesse transazioni. Questo supporta direttamente tutte e quattro le garanzie ACID — atomicità (tutto o niente), consistenza (transizioni di stato valide), isolamento (nessuna interferenza) e durabilità (modifiche confermate sono permanenti). Questo garantisce le fondamenta su cui ogni applicazione costruisce la fiducia nei propri dati.

Problemi di concorrenza da risolvere

Prima di esplorare le soluzioni, è utile comprendere le specifiche anomalie che sorgono quando le transazioni concorrenti vengono eseguite senza controlli adeguati.

• Lettura sporca. Una transazione legge dati scritti da un'altra transazione non confermata. Se quella seconda transazione viene annullata, la prima transazione ha basato la sua logica su dati che in realtà non sono mai esistiti. Le letture sporche sono tra le anomalie più pericolose perché introducono uno stato fantasma nel processo decisionale.
• Aggiornamento perso. Due transazioni leggono lo stesso elemento di dati e poi scrivono entrambi valori aggiornati. La seconda scrittura sovrascrive la prima senza incorporare le sue modifiche, cancellando di fatto un aggiornamento valido. Lo scenario del conto bancario sopra è un esempio classico.
• Lettura non ripetibile. Una transazione legge la stessa riga due volte e ottiene valori diversi perché un'altra transazione ha modificato e confermato la riga tra le letture. Questo interrompe qualsiasi logica che dipende da dati stabili all'interno di una singola transazione.
• Lettura fantasma. Una transazione riesegue una query di intervallo e ottiene un set diverso di righe perché un'altra transazione ha inserito o eliminato righe corrispondenti nell'intervallo. I fantasmi sono particolarmente problematici per le query aggregate e i carichi di lavoro di reporting.

Queste quattro anomalie sono il “perché” dietro ogni meccanismo di controllo della concorrenza. Ogni meccanismo ne previene alcune o tutte, a seconda del livello di isolamento che applica.

Controllo della concorrenza basato su lock

Il locking è l'approccio più antico e intuitivo al controllo della concorrenza. Il DBMS assegna lock agli elementi di dati — righe, pagine o tabelle intere — prima di consentire alle transazioni di accedervi. Un lock agisce come un guardiano: se un'altra transazione detiene già un lock conflittuale, la transazione richiedente deve attendere.

Lock condivisi ed esclusivi

• Lock condivisi (lettura) consentono a più transazioni di leggere contemporaneamente gli stessi dati. Poiché nessuno sta modificando i dati, le letture concorrenti sono sicure.
• Lock esclusivi (scrittura) concedono a una singola transazione l'accesso esclusivo a un elemento di dati. Mentre un lock esclusivo è detenuto, nessun'altra transazione può leggere o scrivere quell'elemento. Questo previene aggiornamenti persi e letture sporche, ma limita anche la concorrenza.

La granularità dei lock introduce un importante compromesso. I lock a livello di riga massimizzano la concorrenza perché le righe non correlate rimangono accessibili, ma aggiungono overhead poiché il sistema deve tenere traccia di molti lock individuali. I lock a livello di tabella sono più semplici ed economici da gestire, ma costringono le transazioni non correlate ad attendere, riducendo il throughput.

Two-phase locking (2PL)

Il two-phase locking è il protocollo standard per garantire la serializzabilità tramite lock. Divide ogni transazione in due fasi. Durante la fase di crescita, una transazione acquisisce tutti i lock di cui ha bisogno ma non ne rilascia nessuno. Una volta rilasciato il primo lock, entra nella fase di contrazione e può solo rilasciare lock, mai acquisirne di nuovi. Questa regola garantisce che l'ordine in cui le transazioni bloccano gli elementi di dati sia coerente, il che a sua volta garantisce uno schedule serializzabile.

Strict 2PL è una variante comune che mantiene tutti i lock fino a quando la transazione non viene confermata. Questo previene rollback a cascata, una situazione in cui una transazione annullata costringe altre transazioni che hanno letto i suoi dati non confermati ad annullarsi a loro volta. La maggior parte dei database relazionali che utilizzano il controllo della concorrenza basato su lock implementano una qualche forma di strict 2PL.

Lo svantaggio del 2PL è che introduce il blocco — le transazioni devono attendere i lock detenuti da altri — e crea le condizioni per i deadlock.

Controllo della concorrenza multi-versione (MVCC)

MVCC adotta un approccio fondamentalmente diverso: invece di bloccare l'accesso, mantiene più versioni di ogni elemento di dati in modo che ogni transazione veda uno snapshot coerente del database. Quando una transazione scrive una riga, MVCC crea una nuova versione invece di sovrascrivere quella esistente. I lettori continuano a vedere la versione che era corrente all'avvio della loro transazione.

Il vantaggio principale è che i lettori non bloccano mai gli scrittori e gli scrittori non bloccano mai i lettori. Questo è un importante vantaggio prestazionale per i carichi di lavoro con molte letture, motivo per cui MVCC è il meccanismo di controllo della concorrenza dominante nei database moderni. PostgreSQL, MySQL/InnoDB, Oracle e la maggior parte dei sistemi OLTP e OLAP contemporanei utilizzano una qualche forma di MVCC.

I conflitti di scrittura vengono rilevati al momento del commit. Se due transazioni modificano la stessa riga, la prima a confermare vince e la seconda deve riprovare. Questo approccio ottimistico alla risoluzione dei conflitti di scrittura mantiene un throughput elevato quando i conflitti sono rari, come di solito accade.

Snapshot isolation è il livello di isolamento più comune basato su MVCC. Ogni transazione vede il database come esisteva all'avvio della transazione, prevenendo letture sporche e letture non ripetibili. Tuttavia, lo snapshot isolation consente una sottile anomalia chiamata write skew, in cui due transazioni leggono dati sovrapposti e poi effettuano scritture basate su ciò che hanno letto, risultando in uno stato che nessuna delle due transazioni avrebbe prodotto da sola. Serializable snapshot isolation (SSI) affronta questa lacuna a costo di un overhead aggiuntivo.

MVCC introduce i propri compromessi. Il mantenimento di più versioni consuma spazio di archiviazione aggiuntivo e il sistema deve periodicamente raccogliere le versioni obsolete — un processo noto come VACUUM in PostgreSQL. Sotto forte contesa di scrittura, il costo di ripetizione e l'overhead di gestione delle versioni possono diventare significativi.

Controllo della concorrenza ottimistico vs. pessimistico

Oltre ai meccanismi specifici di locking e MVCC, le strategie di controllo della concorrenza rientrano in due campi filosofici: pessimistico e ottimistico. Comprendere quale campo si adatta al tuo carico di lavoro è una delle decisioni architetturali più significative che un team di dati possa prendere.

Controllo pessimistico

Il controllo della concorrenza pessimistico presuppone che i conflitti siano probabili e li previene acquisendo lock prima di accedere ai dati. Questo approccio è migliore per i carichi di lavoro con molte scritture in cui i conflitti sono frequenti e il costo di annullare e riprovare una transazione è elevato. I sistemi di registro finanziario, in cui ogni scrittura deve essere sequenziata e verificata, sono un caso d'uso classico.

Lo svantaggio è il blocco. Sotto alta concorrenza, le transazioni si mettono in coda in attesa dei lock, il che riduce il throughput. Nel peggiore dei casi, richieste di lock in competizione creano deadlock.

Controllo ottimistico

Il controllo della concorrenza ottimistico presuppone che i conflitti siano rari. Le transazioni vengono eseguite liberamente senza acquisire lock, operando sulle proprie copie private dei dati. Al momento del commit, il sistema convalida se le letture e scritture della transazione confliggono con qualsiasi altra transazione confermata. Se non vengono rilevati conflitti, la transazione viene confermata. Se viene rilevato un conflitto, la transazione viene annullata e riprovata.

Questo modello a tre fasi — lettura, validazione, scrittura — eccelle in ambienti in cui la maggior parte delle transazioni non tocca gli stessi dati. Sistemi di gestione dei contenuti, applicazioni di navigazione cataloghi e dashboard di reporting ne sono esempi tipici.

Il rovescio della medaglia è il lavoro sprecato. Quando i conflitti sono frequenti, le transazioni eseguono ripetutamente la loro logica completa solo per essere annullate durante la validazione, consumando risorse senza produrre risultati.

Quando usare quale

La scelta tra controllo pessimistico e ottimistico dipende dalle caratteristiche del carico di lavoro. Un'elevata contesa di scrittura, transazioni brevi e bassa tolleranza ai tentativi indicano un controllo pessimistico. Carichi di lavoro a predominanza di lettura con bassa probabilità di conflitto e requisiti di alta concorrenza favoriscono approcci ottimistici o basati su MVCC.

In pratica, molti sistemi di produzione combinano entrambe le strategie. Un pattern comune utilizza MVCC per letture e scritture generali, applicando blocchi pessimistici a righe note "hot-spot" — ad esempio, utilizzando SELECT ... FOR UPDATE per bloccare una riga specifica per cui più transazioni potrebbero competere contemporaneamente.

Livelli di isolamento e i loro compromessi

I livelli di isolamento definiscono quali anomalie di concorrenza una transazione può incontrare. Livelli più rigorosi prevengono più anomalie ma riducono la concorrenza. Lo standard SQL definisce quattro livelli e la maggior parte dei database ne implementa una variante.

• Read uncommitted offre la massima concorrenza ma consente tutte le anomalie, comprese le letture sporche. È raramente utilizzato in produzione perché i rischi superano di gran lunga i guadagni di prestazioni.
• Read committed previene le letture sporche assicurando che una transazione veda solo i dati confermati prima dell'esecuzione di ciascuna istruzione. Consente letture non ripetibili e fantasma, che è un compromesso accettabile per la maggior parte delle applicazioni web e carichi di lavoro generici. PostgreSQL e Oracle lo usano come impostazione predefinita.
• Repeatable read previene letture sporche e non ripetibili garantendo che qualsiasi riga letta durante una transazione restituirà lo stesso valore se letta di nuovo. Le letture fantasma possono ancora verificarsi. MySQL/InnoDB utilizza questo livello come impostazione predefinita.
• Serializable previene tutte le anomalie. Le transazioni si comportano come se fossero eseguite una alla volta, in un qualche ordine seriale. Questa è la garanzia di coerenza più elevata ma comporta la minore concorrenza. È tipicamente riservata a carichi di lavoro finanziari, di conformità o di sicurezza critica in cui la correttezza assoluta è non negoziabile.

La maggior parte delle applicazioni opera in modo sicuro a Read Committed o Repeatable Read. La scelta di Serializable è un compromesso deliberato che dovrebbe essere guidato da specifici requisiti aziendali piuttosto che utilizzato come impostazione predefinita.

Deadlock: prevenzione e risoluzione

Un deadlock si verifica quando due o più transazioni detengono blocchi di cui l'altra ha bisogno, creando un ciclo di attesa reciproca. Nessuna delle due transazioni può procedere e, senza intervento, entrambe aspetterebbero indefinitamente.

• Rilevamento. La maggior parte dei database rileva i deadlock controllando periodicamente i cicli in un grafo di attesa — una struttura dati che mappa quale transazione sta aspettando quale blocco. Quando viene trovato un ciclo, il DBMS seleziona una transazione "vittima" e la annulla, liberando i blocchi in modo che le transazioni rimanenti possano continuare.
• Prevenzione. La strategia di prevenzione più efficace è acquisire i blocchi in un ordine coerente e prevedibile in tutte le transazioni. Se ogni transazione blocca le risorse nella stessa sequenza, le condizioni di attesa circolare non possono formarsi. I timeout dei blocchi forniscono un ulteriore livello di sicurezza: se una transazione attende troppo a lungo, viene annullata invece di contribuire a un potenziale deadlock.
• Risoluzione. La transazione vittima annullata viene sottoposta a rollback e tipicamente ritentata automaticamente dall'applicazione. Poiché i deadlock sono solitamente infrequenti, il costo del tentativo è modesto.

Suggerimenti pratici per minimizzare i deadlock: mantenere le transazioni il più brevi possibile, accedere alle risorse in un ordine prevedibile, impostare valori di timeout dei blocchi appropriati e monitorare la frequenza dei deadlock come segnale di salute del sistema.

Scelta dell'approccio di controllo della concorrenza corretto

Non esiste una risposta universale. Il meccanismo corretto dipende dalle caratteristiche del carico di lavoro, dai requisiti di coerenza e dai compromessi operativi. Diversi fattori guidano la decisione.

• Rapporto lettura/scrittura. I carichi di lavoro dominati dalle letture beneficiano di MVCC o del controllo ottimistico, che consentono letture concorrenti senza blocchi. I carichi di lavoro a predominanza di scrittura spesso necessitano di blocchi pessimistici per evitare il costo di frequenti tentativi.
• Frequenza dei conflitti. Quando i conflitti sono rari — come nella maggior parte dei carichi di lavoro analitici e web — il controllo ottimistico offre una maggiore produttività. Quando i conflitti sono frequenti, l'overhead dei tentativi rende il controllo pessimistico più efficiente nel complesso.
• Durata della transazione. Le transazioni brevi funzionano bene con i blocchi perché i blocchi vengono mantenuti per breve tempo. Le transazioni di lunga durata beneficiano di MVCC, che evita blocchi prolungati.
• Requisiti di coerenza. Requisiti rigorosi (finanziari, conformità) favoriscono l'isolamento Serializable con 2PL. Requisiti moderati (applicazioni web, cataloghi) sono ben serviti da Read Committed con MVCC.
• Distribuiti vs. nodo singolo. I sistemi distribuiti introducono overhead di coordinamento — latenza di rete, tolleranza alle partizioni e requisiti di consenso — che rendono il controllo della concorrenza più complesso e costoso.

Molti sistemi di produzione combinano strategie: MVCC come impostazione predefinita, blocchi pessimistici su righe "hot-spot" note e logica di retry a livello di applicazione per la risoluzione dei conflitti ottimistici.

Controllo della concorrenza nei sistemi distribuiti e analitici

I database distribuiti affrontano sfide di coordinamento aggiuntive. La latenza di rete tra i nodi, la necessità di tolleranza alle partizioni e i requisiti di consenso aumentano il costo e la complessità del controllo della concorrenza. Gli approcci includono 2PL distribuito, MVCC distribuito con timestamp globali — come utilizzato nel sistema TrueTime di Google Spanner — e protocolli di consenso come Raft e Paxos.

Le piattaforme analitiche e lakehouse gestiscono la concorrenza in modo diverso dai database OLTP tradizionali. Questi sistemi sono ottimizzati per carichi di lavoro a predominanza di lettura e scansione di grandi dimensioni con isolamento snapshot piuttosto che per i pattern di blocco a livello di riga comuni nei sistemi transazionali.

Databricks e Delta Lake utilizzano il controllo della concorrenza ottimistico per scritture concorrenti sulla stessa tabella. Le scritture seguono un processo in tre fasi: lettura dell'ultima versione della tabella per identificare i file interessati, scrittura di nuovi file di dati e quindi validazione al momento del commit che non si siano verificati cambiamenti conflittuali. Se le transazioni non sono in conflitto, hanno successo. Se viene rilevato un conflitto, il retry automatico o la risoluzione dei conflitti gestiscono il resto. Delta Lake implementa un modello MVCC senza blocchi, quindi i lettori vedono sempre uno snapshot coerente mentre gli scrittori procedono in modo indipendente.

Le transazioni ACID sulle tabelle Delta Lake garantiscono la coerenza dei dati anche quando più pipeline, utenti e query accedono agli stessi dati contemporaneamente. Per uno sguardo dettagliato su come la concorrenza a livello di riga funziona in pratica, il blog di ingegneria di Databricks offre un'analisi approfondita dei meccanismi sottostanti.

Questo approccio estende le garanzie di concorrenza oltre l'OLTP tradizionale ai carichi di lavoro di data engineering, ETL e ML in cui più scrittori e lettori operano su set di dati condivisi. Combinando il controllo della concorrenza ottimistico, l'isolamento snapshot e la risoluzione automatica dei conflitti, le moderne piattaforme lakehouse portano l'affidabilità delle transazioni di livello database alla scala e alla flessibilità dei data lake cloud.

Controllo della concorrenza senza la complessità: come Databricks lo gestisce

Comprendere la teoria del controllo della concorrenza è una sfida. Implementarla in modo affidabile su dati distribuiti, scrittori multipli e carichi di lavoro diversi è un'altra. I team che gestiscono le proprie strategie di concorrenza dedicano tempo di ingegneria significativo alla configurazione dei livelli di isolamento, alla messa a punto del comportamento dei blocchi e alla creazione di logiche di retry — tempo che non fa progredire il loro lavoro effettivo.

Databricks Lakebase elimina questo onere operativo. Costruito sulla Databricks Data Intelligence Platform, Lakebase è un database cloud-native completamente gestito che porta l'affidabilità transazionale al lakehouse senza richiedere ai team di implementare o configurare autonomamente il controllo della concorrenza. Di serie, fornisce controllo della concorrenza ottimistico, isolamento snapshot per le letture e isolamento write-serializable per le scritture — gli stessi meccanismi trattati in questo articolo, applicati automaticamente.

Poiché Lakebase utilizza il controllo della concorrenza ottimistica su Delta Lake, non ci sono blocchi da gestire e nessun deadlock da risolvere. Le scritture concorrenti seguono il pattern read-validate-commit descritto in precedenza in questa guida: ogni transazione legge la versione più recente della tabella, scrive nuovi file di dati e valida al momento del commit che non si siano verificati conflitti. Quando più pipeline, utenti o query scrivono sulla stessa tabella, il rilevamento dei conflitti a livello di riga di Delta Lake risolve automaticamente le modifiche non sovrapposte, anche per operazioni MERGE, UPDATE e DELETE concorrenti. I lettori vedono sempre uno snapshot coerente, non influenzato dalle scritture in corso.

Questo non è limitato ai carichi di lavoro analitici. Lakebase estende le garanzie transazionali ai carichi di lavoro operativi, all'ingegneria dei dati, alle pipeline ETL e all'ML, tutto sulla stessa piattaforma. Invece di mantenere un database OLTP separato accanto a un lakehouse e collegarli con codice di integrazione personalizzato, i team eseguono tutto attraverso un'unica architettura governata. I dati rimangono in formati aperti su storage cloud a basso costo, con il livello di calcolo transazionale in esecuzione in modo indipendente sopra.

Il risultato: ogni concetto di controllo della concorrenza trattato in questo articolo — MVCC, validazione ottimistica, isolamento dello snapshot, risoluzione dei conflitti — funziona per impostazione predefinita su Databricks. I team possono concentrarsi sui propri dati e sui propri carichi di lavoro, non sulla propria strategia di concorrenza. Esplora Lakebase per vedere come funziona in pratica.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale