Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 18 / 40

Tranzacții și proprietățile ACID

Ce înseamnă cu adevărat atomic, consistent, izolat și durabil. BEGIN TRAN, COMMIT, ROLLBACK, SAVEPOINT și pattern-urile de error handling care țin datele oneste.

O tranzacție e un grup de operațiuni de bază de date care fie se întâmplă toate, fie niciuna. Atât. Asta e toată ideea.

Și totuși, cuvântul „tranzacție” ascunde motivul pentru care fiecare business serios rulează pe un RDBMS. Când clientul Runehold dă checkout — plata procesată, comanda creată, stocul decrementat, adresa de livrare scrisă, e-mail-ul de confirmare pus în coadă — fiecare dintre aceste operațiuni trebuie să reușească, sau niciuna nu trebuie să se întâmple. Nu vrei bani luați și nicio comandă. Nu vrei o comandă fără decrementare de stoc. Nu vrei un checkout pe jumătate.

Asta rezolvă tranzacțiile.

BEGIN, COMMIT, ROLLBACK

Trei cuvinte cheie. Învață-le pe vecie.

BEGIN TRANSACTION;         -- sau BEGIN TRAN, același lucru

    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;

Dacă vreo instrucțiune din interiorul blocului BEGIN/COMMIT eșuează (sau apelezi explicit ROLLBACK), tot ce e în ea e anulat. Starea bazei de date e exact ca și când întreaga chestie nu ar fi început vreodată.

Fără un BEGIN TRAN, fiecare instrucțiune e propria ei tranzacție implicită. E ok pentru operațiuni pe un singur rând, dar catastrofal când ai nevoie ca mai multe instrucțiuni să rămână sincronizate.

ACID, literă cu literă

A — Atomic

„Tot sau nimic.” Tranzacția e indivizibilă. Fie fiecare modificare din ea e vizibilă lumii din afară, fie niciuna.

Dacă serverul cade în mijlocul unui commit — lumina din camera serverelor pâlpâie, curentul cade, container-ul e ucis — log-ul write-ahead al SQL Server-ului asigură că, la repornire, orice tranzacție care nu a fost commit-uită e anulată. Nu vezi niciodată tranzacții aplicate pe jumătate.

C — Consistent

Baza de date trece dintr-o stare validă în altă stare validă. Foreign key-urile rămân valide, check constraints rămân, constrângerile unique nu sunt încălcate. Regulile tale de business — impuse prin constrângeri — sunt întotdeauna adevărate la finalul unei tranzacții.

Notă: „consistency”-ul din SQL e despre regulile schemei, nu despre consistența din sisteme distribuite (teorema CAP). Cuvânt diferit, aceleași litere.

I — Izolat

Tranzacțiile concurente nu își calcă pe picioare. Citirile dintr-o tranzacție nu văd scrierile altei tranzacții care nu au fost încă commit-uite. E mai complex decât pare — lecția 19 acoperă diferitele niveluri de izolare dintre care poți alege.

D — Durabil

Odată ce o tranzacție e commit-uită, rămâne commit-uită. Chiar dacă serverul cade imediat după COMMIT. Commit-ul scrie în log-ul de tranzacții, care e flushed pe disc înainte ca COMMIT să se întoarcă. „Durabil” înseamnă „pe disc, nu doar în RAM”.

SQL Server are un mod opțional delayed durability care slăbește asta — commit-urile se întorc înainte ca log-ul să fie flushed. Mai rapid, riscant. Implicit e durabilitate completă.

Error handling explicit: TRY/CATCH

Fără error handling, o instrucțiune din interiorul BEGIN TRAN care eșuează poate lăsa tranzacția într-o stare ciudată. Pattern-ul pe care îl vrei:

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;

    -- Loghează eroarea, re-aruncă sau gestionează cum așteaptă aplicația
    THROW;
END CATCH;

Structură de internalizat:

  • BEGIN TRY / END TRY înfășoară instrucțiunile care ar putea eșua.
  • BEGIN CATCH / END CATCH rulează la orice eroare din interiorul blocului TRY.
  • XACT_STATE() întoarce 0 (fără tranzacție), 1 (activă, poate fi commit-uită) sau -1 (activă, dar imposibil de commit-uit — unele erori lasă tranzacția într-o stare „doomed”).
  • THROW fără argumente re-aruncă eroarea prinsă. Folosește-l pentru a propaga erorile către apelant nealterate.

Fiecare stored procedure care modifică mai mult de o tabelă la Runehold folosește acest pattern. E prețul fiabilității.

@@TRANCOUNT și tranzacțiile imbricate

SELECT @@TRANCOUNT;     -- 0 în afara oricărei tranzacții, 1 în interior

Important: SQL Server nu are tranzacții imbricate cu adevărat. BEGIN TRAN în interiorul unei tranzacții active incrementează @@TRANCOUNT; un COMMIT îl decrementează. Doar când @@TRANCOUNT ajunge la zero se întâmplă cu adevărat un commit.

BEGIN TRAN;          -- @@TRANCOUNT = 1
    BEGIN TRAN;      -- @@TRANCOUNT = 2, fără efect real
        ...
    COMMIT;          -- @@TRANCOUNT = 1, încă nu e commit-uit
    ROLLBACK;        -- totul se anulează, @@TRANCOUNT = 0

ROLLBACK anulează întregul stack de tranzacții, nu doar un nivel. Dacă apelezi un stored procedure care face BEGIN TRAN; ... ROLLBACK;, anulează și tranzacția ta exterioară. E surprinzător și motivul pentru care stored procedure-urile care gestionează tranzacții au nevoie de design atent.

Pattern-ul comun: stored procs detectează dacă sunt în interiorul unei tranzacții și pornesc una proprie doar dacă nu sunt:

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
        ... lucru ...
        IF @nested = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @nested = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Urât, dar corect. Pentru cod nou, ține tranzacțiile la nivelul de top (aplicația sau procedura exterioară) și lasă procedurile interioare să moștenească.

SAVEPOINT: rollback parțial

Un savepoint e un checkpoint cu nume în interiorul unei tranzacții. Poți să te întorci la el fără a anula întreaga tranzacție:

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

    SAVE TRAN step1;

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

    IF @someCondition = 0
        ROLLBACK TRAN step1;    -- anulează update-ul pe Orders, păstrează cel pe Customer

COMMIT;

Rar nevoie în codul de zi cu zi. Util în interiorul job-urilor mari de încărcare de date, unde unele secțiuni sunt opționale.

Tranzacții implicite vs explicite

SQL Server e implicit în mod autocommit: fiecare instrucțiune individuală se commit-ează automat. Scrierea UPDATE Customer SET Name = 'X' fără BEGIN TRAN rulează tot într-o tranzacție implicită care face commit în momentul în care instrucțiunea se întoarce.

Unele baze de cod mai vechi activează SET IMPLICIT_TRANSACTIONS ON per sesiune, ceea ce înseamnă că orice instrucțiune de modificare a datelor pornește o tranzacție care rămâne deschisă până faci explicit COMMIT sau ROLLBACK. Ușor de uitat să faci commit. Ușor să ții blocări ore în șir. Ușor să arunci în aer un workload de producție.

Recomandare: lasă IMPLICIT_TRANSACTIONS off (implicit). Pornește tranzacțiile explicit cu BEGIN TRAN. Termină-le explicit cu COMMIT sau ROLLBACK.

Problema „tranzacției lungi”

O tranzacție ține blocările din momentul în care le-a obținut până când face commit. Cu cât tranzacția e deschisă mai mult, cu atât blocările sunt ținute mai mult, cu atât mai multe sesiuni se aglomerează în așteptare.

Antipattern-uri:

  • Apel de rețea lent către un serviciu extern în interiorul unei tranzacții. Serviciul atârnă; tranzacția ta e deschisă 30 de secunde; toți așteaptă.
  • Ținerea unei tranzacții deschise în timp ce aștepți parametri de la utilizator („dă click pe Confirm”). Utilizatorul pleacă după cafea; blocarea e ținută o oră.
  • Update-uri mari în loturi într-o singură tranzacție. Blocare de zece minute pe jumătate de tabelă.

Reguli:

  1. Ține tranzacțiile scurte. Ideal, milisecunde.
  2. Fă lucrul costisitor în afara tranzacției. Calcule, apeluri API, citiri din fișiere — fă-le mai întâi, apoi BEGIN TRAN pentru schimbarea de date.
  3. Lotează operațiile mari. Cum am arătat în lecția 17, UPDATE TOP (10000) într-o buclă ține fiecare tranzacție scurtă.

Valori de retur și numărul de rânduri

@@ROWCOUNT întoarce numărul de rânduri afectate de instrucțiunea anterioară. Se resetează după fiecare instrucțiune. Captează-l imediat dacă ai nevoie:

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

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

Utilizarea canonică: verifică dacă UPDATE-ul tău chiar a atins rândul pe care îl așteptai. Util pentru concurența optimistă („update doar dacă versiunea se potrivește”).

Rulează asta pe mașina ta

USE Runehold;
GO

-- 1. Drill-ul de siguranță: BEGIN TRAN, inspectează, ROLLBACK
BEGIN TRAN;
    DELETE FROM Sales.Orders WHERE Total < 10;
    SELECT @@ROWCOUNT AS would_delete;
ROLLBACK;
-- Rândurile au revenit. Niciun rău făcut. Acesta e obiceiul de format.

-- 2. TRY/CATCH cu eșec controlat
BEGIN TRY
    BEGIN TRAN;
        -- Asta va reuși
        UPDATE Sales.Customer SET Country = 'XX' WHERE CustomerId = 1;

        -- Asta va eșua: Country e CHAR(2), 'TOOLONG' nu încape
        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;

-- Verifică dacă țara CustomerId 1 a revenit la cum era
SELECT CustomerId, Country FROM Sales.Customer WHERE CustomerId IN (1, 2);

-- 3. Antipattern de tranzacție lungă (nu face asta în cod real!)
BEGIN TRAN;
    UPDATE Sales.Orders SET Notes = 'holding locks' WHERE OrderId = 1;
    -- Imaginează un apel API de 30 de secunde aici. Alte sesiuni care încearcă
    -- să atingă OrderId 1 vor bloca până facem commit. NU FACE ASTA.
    WAITFOR DELAY '00:00:03';
COMMIT;

-- 4. Exemplu 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;

    -- Anulează doar al doilea update, nu primul
    ROLLBACK TRAN checkpoint_a;

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

-- 5. Verificare @@ROWCOUNT pentru concurență optimistă
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);

Următoarea lecție: niveluri de izolare. De ce query-urile tale văd uneori vederi ciudat-inconsistente asupra datelor, cum repari asta (fără să omori throughput-ul) și viața secretă a READ COMMITTED SNAPSHOT.

Caută