Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 18 / 40

Transazioni e le proprietà ACID

Cosa vogliono dire davvero atomico, consistente, isolato e durevole. BEGIN TRAN, COMMIT, ROLLBACK, SAVEPOINT, e i pattern di gestione errori che tengono i dati onesti.

Una transazione è un gruppo di operazioni di database che o avvengono tutte o non avviene nessuna. Tutto qui. È tutta l’idea.

Eppure la parola “transazione” nasconde il motivo per cui ogni business serio gira su un RDBMS. Quando il cliente di Runehold completa il checkout — pagamento processato, ordine creato, inventario decrementato, indirizzo di spedizione scritto, email di conferma in coda — ognuna di queste operazioni deve riuscire, oppure nessuna di loro. Non vuoi soldi addebitati e nessun ordine. Non vuoi un ordine senza decremento di inventario. Non vuoi un checkout fatto a metà.

Questo è ciò che le transazioni risolvono.

BEGIN, COMMIT, ROLLBACK

Tre keyword. Imparale per sempre.

BEGIN TRANSACTION;         -- o BEGIN TRAN, è lo stesso

    UPDATE Inventory.Stock
    SET Quantity = Quantity - 1
    WHERE ProductId = @product AND WarehouseId = @warehouse;

    INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
    VALUES (@customer, @total, @country, @vat);

    INSERT INTO Shipping.Shipment (OrderId, Address)
    VALUES (SCOPE_IDENTITY(), @address);

COMMIT TRANSACTION;

Se una qualsiasi istruzione dentro il blocco BEGIN/COMMIT fallisce (o chiami esplicitamente ROLLBACK), tutto al suo interno viene annullato. Lo stato del database è esattamente come se l’intera cosa non fosse mai iniziata.

Senza un BEGIN TRAN, ogni istruzione è la sua transazione implicita. Va bene per operazioni su singola riga ma è catastrofico quando ti servono più istruzioni che restino in sincrono.

ACID, una lettera alla volta

A — Atomico

“Tutto o niente.” La transazione è indivisibile. O ogni cambiamento al suo interno è visibile al mondo esterno, o nessuno lo è.

Se il server crasha a metà commit — la luce nella sala server lampeggia, manca la corrente, il container viene ucciso — il write-ahead log di SQL Server garantisce che al riavvio, qualsiasi transazione non committata venga rolled back. Non vedi mai transazioni applicate a metà.

C — Consistente

Il database passa da uno stato valido a un altro stato valido. Le foreign key restano valide, i check constraint reggono, gli unique constraint non vengono violati. Le tue regole di business — fatte rispettare dai constraint — sono sempre vere alla fine di una transazione.

Nota: la “consistency” di SQL riguarda le regole dello schema, non la consistency dei sistemi distribuiti (teorema CAP). Parola diversa, stesse lettere.

I — Isolato

Le transazioni concorrenti non si pestano i piedi. Le letture di una transazione non vedono le scritture di un’altra che non sono ancora state committate. È più complesso di quanto sembri — la lezione 19 copre i diversi livelli di isolamento tra cui puoi scegliere.

D — Durevole

Una volta che una transazione è stata committata, resta committata. Anche se il server crasha immediatamente dopo il COMMIT. Il commit scrive nel transaction log, che viene flushato sul disco prima che COMMIT ritorni. “Durevole” significa “su disco, non solo in RAM.”

SQL Server ha una modalità opzionale di delayed durability che indebolisce questa cosa — i commit ritornano prima che il log sia flushato. Più veloce, rischiosa. Il default è la durabilità completa.

Gestione errori esplicita: TRY/CATCH

Senza gestione errori, un’istruzione dentro BEGIN TRAN che fallisce può lasciare la tua transazione in uno stato strano. Il pattern che vuoi:

BEGIN TRY
    BEGIN TRANSACTION;

        UPDATE Inventory.Stock SET Quantity = Quantity - 1 WHERE ...;
        IF @@ROWCOUNT = 0
            THROW 50001, 'Not enough stock', 1;

        INSERT INTO Sales.Orders ...;
        INSERT INTO Shipping.Shipment ...;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    -- Logga l'errore, rilancia, o gestisci come si aspetta la tua app
    THROW;
END CATCH;

Struttura da interiorizzare:

  • BEGIN TRY / END TRY avvolge le istruzioni che potrebbero fallire.
  • BEGIN CATCH / END CATCH viene eseguito a qualsiasi errore dentro il blocco TRY.
  • XACT_STATE() ritorna 0 (nessuna transazione), 1 (attiva, può essere committata), o -1 (attiva ma non committabile — alcuni errori lasciano la transazione in uno stato condannato).
  • THROW senza argomenti rilancia l’errore catturato. Usalo per propagare gli errori al chiamante senza modifiche.

Ogni stored procedure che modifica più di una tabella in Runehold usa questo pattern. È il prezzo dell’affidabilità.

@@TRANCOUNT e transazioni nidificate

SELECT @@TRANCOUNT;     -- 0 fuori da qualsiasi transazione, 1 dentro

Importante: SQL Server in realtà non ha transazioni nidificate. BEGIN TRAN dentro una transazione attiva incrementa @@TRANCOUNT; un COMMIT lo decrementa. Solo quando @@TRANCOUNT arriva a zero qualcosa viene committato davvero.

BEGIN TRAN;          -- @@TRANCOUNT = 1
    BEGIN TRAN;      -- @@TRANCOUNT = 2, nessun effetto reale
        ...
    COMMIT;          -- @@TRANCOUNT = 1, ancora non committato
    ROLLBACK;        -- tutto viene rolled back, @@TRANCOUNT = 0

ROLLBACK rolla indietro l’intero stack della transazione, non solo un livello. Se chiami una stored procedure che fa BEGIN TRAN; ... ROLLBACK;, rolla indietro anche la tua transazione esterna. È sorprendente ed è il motivo per cui le stored procedure che gestiscono transazioni hanno bisogno di un design attento.

Il pattern comune: le stored proc rilevano se sono dentro una transazione e ne avviano una propria solo se non lo sono:

CREATE PROCEDURE Sales.CreateOrder ...
AS
BEGIN
    DECLARE @nested INT = CASE WHEN @@TRANCOUNT = 0 THEN 0 ELSE 1 END;
    IF @nested = 0 BEGIN TRANSACTION;

    BEGIN TRY
        ... lavoro ...
        IF @nested = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @nested = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Brutto ma corretto. Per il codice nuovo, tieni le transazioni a livello più alto (l’applicazione o la proc esterna) e lascia che le proc interne ereditino.

SAVEPOINT: rollback parziale

Un savepoint è un checkpoint con nome dentro una transazione. Puoi tornare indietro a esso senza tornare indietro su tutta la transazione:

BEGIN TRAN;
    UPDATE Sales.Customer SET ... WHERE ...;

    SAVE TRAN step1;

    UPDATE Sales.Orders SET ... WHERE ...;

    IF @someCondition = 0
        ROLLBACK TRAN step1;    -- annulla l'update su Orders, mantiene quello su Customer

COMMIT;

Raramente necessario nel codice di tutti i giorni. Utile dentro grandi job di caricamento dati dove alcune sezioni del lavoro sono opzionali.

Transazioni implicite vs esplicite

SQL Server di default è in modalità autocommit: ogni singola istruzione viene committata automaticamente. Scrivere UPDATE Customer SET Name = 'X' senza BEGIN TRAN esegue comunque dentro una transazione implicita che committa nel momento in cui l’istruzione ritorna.

Alcune codebase più vecchie attivano SET IMPLICIT_TRANSACTIONS ON per sessione, il che significa che qualsiasi istruzione di modifica dati avvia una transazione che resta aperta finché non chiami esplicitamente COMMIT o ROLLBACK. Facile dimenticarsi di committare. Facile tenere lock per ore. Facile mandare giù un workload di produzione.

Raccomandazione: lascia IMPLICIT_TRANSACTIONS off (il default). Avvia le tue transazioni esplicitamente con BEGIN TRAN. Concludile esplicitamente con COMMIT o ROLLBACK.

Il problema della “transazione lunga”

Una transazione tiene i lock dal momento in cui li acquisisce fino al commit. Più è aperta a lungo, più a lungo quei lock vengono tenuti, più altre sessioni si accumulano in attesa.

Anti-pattern:

  • Fare una chiamata di rete lenta verso un servizio esterno dentro una transazione. Il servizio si blocca; la tua transazione è aperta per 30 secondi; tutti aspettano.
  • Tenere una transazione aperta mentre prendi parametri dall’utente (“clicca Conferma”). L’utente va a prendersi un caffè; il lock viene tenuto per un’ora.
  • Grandi update batch dentro una sola transazione. Lock di dieci minuti su mezza tabella.

Regole:

  1. Mantieni le transazioni corte. Idealmente millisecondi.
  2. Fai il lavoro costoso fuori dalla transazione. Calcoli, chiamate API, letture di file — falli prima, poi BEGIN TRAN per il cambiamento dati.
  3. Batcha le operazioni grandi. Come mostrato nella lezione 17, UPDATE TOP (10000) in un loop tiene corta ogni transazione.

Valori di ritorno e row count

@@ROWCOUNT ritorna il numero di righe interessate dall’istruzione precedente. Resettato dopo ogni istruzione. Catturalo sempre subito se ti serve:

UPDATE Sales.Orders SET Status = 2 WHERE OrderId = @id;
DECLARE @rc INT = @@ROWCOUNT;

IF @rc = 0
    THROW 50002, 'Order not found', 1;

L’uso canonico: verificare che il tuo UPDATE abbia toccato davvero la riga che ti aspettavi. Utile per la concorrenza ottimistica (“aggiorna solo se la versione corrisponde”).

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. L'esercitazione di sicurezza: BEGIN TRAN, ispeziona, ROLLBACK
BEGIN TRAN;
    DELETE FROM Sales.Orders WHERE Total < 10;
    SELECT @@ROWCOUNT AS would_delete;
ROLLBACK;
-- Le righe sono tornate. Nessun danno fatto. È l'abitudine da costruire.

-- 2. TRY/CATCH con un fallimento controllato
BEGIN TRY
    BEGIN TRAN;
        -- Questa avrà successo
        UPDATE Sales.Customer SET Country = 'XX' WHERE CustomerId = 1;

        -- Questa fallirà: Country è CHAR(2), 'TOOLONG' non ci sta
        UPDATE Sales.Customer SET Country = 'TOOLONG' WHERE CustomerId = 2;

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    SELECT
        ERROR_NUMBER()  AS err_num,
        ERROR_MESSAGE() AS err_msg,
        ERROR_LINE()    AS err_line;
END CATCH;

-- Verifica che il country del CustomerId 1 sia tornato come prima
SELECT CustomerId, Country FROM Sales.Customer WHERE CustomerId IN (1, 2);

-- 3. Anti-pattern di transazione lunga (non farlo nel codice reale!)
BEGIN TRAN;
    UPDATE Sales.Orders SET Notes = 'holding locks' WHERE OrderId = 1;
    -- Immagina una chiamata API di 30 secondi qui. Le altre sessioni che provano a toccare
    -- OrderId 1 si bloccheranno finché non committiamo. NON FARLO.
    WAITFOR DELAY '00:00:03';
COMMIT;

-- 4. Esempio di SAVEPOINT
BEGIN TRAN;
    UPDATE Sales.Customer SET Country = 'NL' WHERE CustomerId = 1;

    SAVE TRAN checkpoint_a;

    UPDATE Sales.Customer SET Country = 'ZZ' WHERE CustomerId = 1;

    -- Annulla solo il secondo update, non il primo
    ROLLBACK TRAN checkpoint_a;

    SELECT Country FROM Sales.Customer WHERE CustomerId = 1;
COMMIT;

-- 5. Verifica @@ROWCOUNT per concorrenza ottimistica
DECLARE @expectedVersion INT = 42;
UPDATE Sales.Customer SET Name = N'New Name' WHERE CustomerId = 1 AND Version = @expectedVersion;
IF @@ROWCOUNT = 0
    RAISERROR('Version mismatch or row not found', 16, 1);

Prossima lezione: livelli di isolamento. Perché le tue query a volte vedono viste dei dati stranamente inconsistenti, come sistemarlo (senza ammazzare il throughput), e la vita segreta di READ COMMITTED SNAPSHOT.

Cerca