Het verplaatsen van 300 roepies van Asha naar Ben vergt twee updates: trek het af van het ene account en voeg het toe aan het andere. Als het systeem tussen hen faalt, verdwijnt het geld. SQL-transacties bestaan ​​om dat onmogelijk te maken, door beide stappen als één geheel te behandelen.

In deze handleiding bespreken we commits, rollbacks en mislukkingen in een kleine rekeningtabel. We hebben die voorbeelden in SQLite uitgevoerd en de getoonde saldi komen uit die run. De basisopdrachten zijn voor alle databases vergelijkbaar, maar het vergrendelings- en isolatiegedrag varieert sterk. Daarom geven we aan waar u de documentatie van uw eigen database kunt raadplegen.

Wat is een transactie?

Een transactie is een reeks van een of meer SQL-instructies die door de database als één logische bewerking worden behandeld. Hier is onze tabel:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

De CHECK beperking is een regel dat een saldo nooit onder nul kan komen, die we later zullen gebruiken. Merk op dat de syntaxis om een ​​transactie te starten varieert: SQLite en PostgreSQL accepteren BEGIN, MySQL heeft ook START TRANSACTION en SQL Server gebruikt BEGIN TRANSACTION.

Buiten een expliciete transactie gebruiken de meeste databases autocommit, waarbij elke verklaring wordt opgeslagen zodra deze is voltooid. Oracle is een opmerkelijke uitzondering, omdat een sessie daar in een transactie blijft totdat u zich vastlegt.

COMMIT: maak het permanent

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Totdat COMMIT loopt, zijn de wijzigingen voorlopig. Zodra het succesvol terugkeert, garandeert de database dat ze worden opgeslagen. Het totaal voor beide rekeningen bleef op 1200, wat het hele punt van de oefening is.

ROLLBACK: annuleer alles

ROLLBACK maakt alles ongedaan sinds de transactie begon:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Een rollback gebeurt ook automatisch als de verbinding wegvalt of de server crasht voordat u een commit maakt, zodat een halfvoltooide transactie niet overleeft.

Wanneer een stelling mislukt

Hier is een realistischer geval. Asha probeert 5000 te sturen, wat niet op de rekening staat. Het krediet aan Ben loopt eerst, en daarna overtreedt het debet de CHECK regel:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Zonder de transactie zou Ben de 5000 hebben gehouden en zouden de boeken verkeerd zijn. Houd rekening met één belangrijk detail: een falende verklaring annuleert de transactie niet altijd vanzelf. In SQLite en MySQL kunnen eerdere instructies bijvoorbeeld in behandeling blijven, dus uw code moet de rollback uitvoeren. PostgreSQL markeert in plaats daarvan de hele transactie als afgebroken en weigert verdere instructies totdat u terugdraait.

Koppel in applicatiecode altijd de transactie aan foutafhandeling. Hier is het idee in Python, en onze gids voor Python-afhandeling van uitzonderingen legt de syntaxis uit:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Spaar punten

Een savepoint markeert een positie binnen een transactie, zodat u slechts een deel van het werk ongedaan kunt maken:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

De bonus van 999 werd ongedaan gemaakt, terwijl de rest van de transactie werd voortgezet en vastgelegd. Dit is handig voor optionele stappen in een lang proces.

ZUUR-eigenschappen uitgelegd

Consistentie wordt vaak verkeerd begrepen. De database handhaaft alleen de regels die u declareert, zoals beperkingen en vreemde sleutels. Het zal niet voorkomen dat uw code het verkeerde bedrag overmaakt. Duurzaamheid wordt meestal bereikt met een vooruitschrijflogboek, waarin wijzigingen veilig op schijf worden vastgelegd voordat de commit wordt bevestigd.

Wat gebeurt er bij gelijktijdige transacties?

We hebben twee verbindingen met één SQLite-bestand geopend. De eerste startte een transactie en updatete een rij van 1 naar 2, zonder zich nog vast te leggen. Dit is wat de tweede verbinding zag:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite staat slechts één schrijver tegelijk toe, wat eenvoudiger is dan serverdatabases, waar veel transacties tegelijkertijd verschillende rijen kunnen schrijven. Maar het principe is hetzelfde: andere sessies zouden onder normale instellingen geen niet-vastgelegde wijzigingen moeten zien, en conflicterende schrijfbewerkingen moeten wachten. In onze test hebben we de tweede verbinding ingesteld om onmiddellijk op te geven in plaats van te wachten, zodat de fout meteen verscheen.

Lees afwijkingen

Wanneer transacties elkaar overlappen, kunnen bepaalde problemen optreden. De SQL-standaard definieert deze in termen van wat een transactie kan waarnemen:

Een ander probleem is de verloren update. Twee sessies lezen een saldo van 100, elke voegt 10 toe en elke schrijft 110. Eén update is feitelijk verdwenen, terwijl het antwoord 120 zou moeten zijn.

SQL-isolatieniveaus

Met isolatieniveaus kunt u striktheid inruilen voor gelijktijdigheid. Dit is wat de SQL-standaard op elk niveau toestaat:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

De exacte syntaxis en het gedrag zijn afhankelijk van uw database. De standaardinstellingen verschillen ook: PostgreSQL en SQL Server gebruiken read commit, terwijl MySQL’s InnoDB herhaalbaar lezen gebruikt. Sommige databases geven sterkere garanties dan de standaard vereist, of behandelen het ene niveau als het andere. In PostgreSQL gedraagt ​​read uncommit zich bijvoorbeeld als read commit. Lees altijd de documentatie van uw database voordat u op een specifiek gedrag vertrouwt.

Impasses

Er is sprake van een deadlock wanneer twee transacties elk een lock bevatten die de ander nodig heeft. Transactie A vergrendelt rij 1 en wil rij 2, terwijl transactie B rij 2 vergrendelt en rij 1 wil. Geen van beide kan doorgaan.

Databases als PostgreSQL, MySQL en SQL Server merken dit op en annuleren één transactie met een foutmelding. Dat is normaal gedrag en uw aanvraag moet hierop voorbereid zijn. Om impasses te voorkomen, moet u de rijen altijd in een consistente volgorde bijwerken, bijvoorbeeld met de laagste account-ID eerst, de transacties kort houden en het opnieuw proberen wanneer iemand als slachtoffer is gekozen.

Beste praktijken

• Houd transacties kort. Lange transacties houden sloten vast en blokkeren andere gebruikers.
• Wacht nooit op een gebruiker of een trage API binnen een transactie. Doe dat werk voordat je begint, of daarna.
• Handel altijd fouten af. Elk pad moet eindigen in een commit of een rollback.
• Voeg beperkingen toe. Ze geven de database echte regels die kunnen worden afgedwongen. Een goed tafelontwerp helpt ook.
• Gebruik goede indexen. Snellere zoekopdrachten betekenen dat vergrendelingen minder lang worden vastgehouden.
• Wees voorzichtig met nieuwe pogingen. Probeer het opnieuw bij impasses, maar zorg ervoor dat een bewerking niet tweemaal kan worden toegepast.
• Test gelijktijdigheid expres. Veel van deze bugs verschijnen alleen onder belasting, dus eenvoudige integratietests alleen brengen ze mogelijk niet aan het licht.

Tot slot

Transacties maken van meerdere risicovolle stappen één veilige operatie, en ACID beschrijft de garanties die dit betrouwbaar maken. Wanneer een zakelijke actie meer dan één rij of tabel raakt, vraag uzelf dan af wat er zou gebeuren als deze halverwege zou stoppen. Als het antwoord u zorgen baart, verpak het dan in een transactie. Om te oefenen opent u twee sessies in een lokale database en probeert u zelf de afwijkingen te produceren.

Veelgestelde vragen

Blijf leren