SQL-transacties houden samenhangende databasebewerkingen bij elkaar, ook wanneer meerdere verzoeken tegelijk gegevens lezen of wijzigen. Je leest hoe isolatieniveaus bepalen welke conflicten mogelijk zijn, wat verschijnselen als dirty reads en deadlocks veroorzaken en welke ontwerpkeuzes daarbij horen.
Wat een SQL-transactie afbakent
Een transactie groepeert databasebewerkingen die voor de applicatie één logische wijziging vormen. Denk aan een bankoverschrijving: het bedrag wordt van de ene rekening afgeschreven en bij de andere bijgeschreven. Als slechts één bewerking slaagt, klopt de administratie niet meer. Door beide wijzigingen binnen dezelfde transactie uit te voeren, kan de database ze samen vastleggen of allebei terugdraaien.
In SQL begint een transactie doorgaans expliciet met BEGIN of START TRANSACTION. Met COMMIT worden de wijzigingen definitief; met ROLLBACK worden ze ongedaan gemaakt. Veel databases ondersteunen daarnaast autocommit, waarbij elke losse instructie automatisch een eigen transactie vormt. Dat is handig voor eenvoudige bewerkingen, maar beschermt geen reeks afhankelijke statements als één geheel.
De grens van een transactie vraagt daarom aandacht. Plaats alle databasebewerkingen die samen moeten slagen binnen dezelfde grens, maar neem geen ongerelateerde acties mee. Een transactie openhouden tijdens een lange API-aanroep of gebruikersinteractie kan locks vasthouden en andere verzoeken blokkeren. Een korte, doelgerichte transactie maakt duidelijk welke toestand als één geheel wordt gewijzigd en beperkt de tijd waarin concurrentieproblemen kunnen ontstaan.
ACID-eigenschappen en hun grenzen
Transacties worden vaak beschreven met de ACID-eigenschappen: atomiciteit, consistentie, isolatie en duurzaamheid. Atomiciteit betekent dat de database de transactie als geheel verwerkt: alle wijzigingen worden vastgelegd of geen ervan. Consistentie verwijst naar het bewaren van geldige database-invarianten, zoals een unieke sleutel of een foreign-keyrelatie. De database kan zulke regels afdwingen, maar de applicatie moet ook bedrijfsregels correct modelleren.
Isolatie bepaalt in hoeverre gelijktijdige transacties elkaars tussenstappen kunnen waarnemen. Dat is geen absolute eigenschap die overal hetzelfde werkt; het gekozen isolatieniveau en de implementatie van de database bepalen welke waarnemingen zijn toegestaan. Duurzaamheid betekent dat een bevestigde transactie na een storing behouden blijft, binnen de configuratie en garanties van het gebruikte databasesysteem. Replicatie, opslaginstellingen en herstelprocedures spelen daarbij eveneens een rol.
ACID is dus geen vervanging voor zorgvuldig ontwerp. Een transactie kan technisch atomair zijn en toch een foutieve uitkomst vastleggen wanneer de applicatie een invariant niet controleert. Een constraint kan bijvoorbeeld dubbele waarden voorkomen, maar niet vanzelf een complexe regel afdwingen die over meerdere rijen verdeeld is. Breng daarom per bewerking in kaart welke regels altijd moeten gelden, welke daarvan de database kan afdwingen en welke controle binnen de transactie hoort. Zo wordt duidelijk wat de transactie daadwerkelijk beschermt.
Dirty reads, non-repeatable reads en phantom reads
Isolatieproblemen ontstaan wanneer transacties elkaars wijzigingen op verschillende momenten waarnemen. Een dirty read treedt op als een transactie gegevens leest die een andere transactie nog niet heeft vastgelegd. Als die tweede transactie vervolgens wordt teruggedraaid, heeft de eerste gewerkt met een waarde die nooit definitief bestond. Dit kan leiden tot besluiten of berekeningen die niet aansluiten op de uiteindelijke database-inhoud.
Bij een non-repeatable read leest een transactie dezelfde rij twee keer en krijgt tussendoor een andere waarde, doordat een andere transactie de rij heeft gewijzigd en vastgelegd. Een phantom read betreft een query op een verzameling rijen: dezelfde zoekvoorwaarde levert later extra of juist minder rijen op, doordat een andere transactie rijen heeft toegevoegd, verwijderd of aangepast zodat ze wel of niet aan de voorwaarde voldoen. Het onderscheid is relevant wanneer applicatielogica niet alleen één rij, maar een resultaatset gebruikt.
Deze verschijnselen zijn niet in elk databasesysteem op dezelfde manier toegestaan. Sommige systemen voorkomen dirty reads ook bij een laag isolatieniveau; andere bieden instellingen die meer variatie toelaten. Bovendien garandeert een stabiele lezing niet automatisch dat een latere wijziging veilig is. Als een applicatie bijvoorbeeld het aantal beschikbare plaatsen leest en daarna een reservering toevoegt, kunnen twee transacties dezelfde vrije plaats zien. De keuze van isolatie moet dus aansluiten op de invariant die beschermd moet worden, niet alleen op de naam van een anomalie.
De SQL-isolatieniveaus in de praktijk
De SQL-standaard beschrijft vier gangbare niveaus: Read Uncommitted, Read Committed, Repeatable Read en Serializable. Read Uncommitted laat in theorie de meeste tussenresultaten toe, waaronder dirty reads. Read Committed voorkomt het lezen van niet-vastgelegde wijzigingen, maar een tweede lezing binnen dezelfde transactie kan een nieuw resultaat tonen. Dit niveau is in veel systemen een gebruikelijke standaard, maar de precieze uitvoering verschilt per database.
Repeatable Read beschermt herhaald lezen van eerder gelezen gegevens sterker. Afhankelijk van de implementatie kan het ook phantom reads toelaten, of juist met aanvullende mechanismen voorkomen. Serializable is bedoeld om gelijktijdige transacties een uitkomst te laten produceren die overeenkomt met een seriële uitvoering. Dat levert sterke isolatie, maar kan leiden tot meer blokkering, conflicten of transacties die opnieuw moeten worden uitgevoerd.
De isolatieniveaus vormen geen eenvoudige schaal waarop het hoogste niveau altijd de beste keuze is. Een rapportagequery kan soms met Read Committed volstaan, terwijl een voorraadmutatie een striktere bescherming nodig heeft. Controleer de documentatie van het concrete databasesysteem en test het gedrag met gelijktijdige sessies; dezelfde SQL-keuze kan verschillende lock- of snapshotstrategieën activeren. Beoordeel ook foutafhandeling: een database die een transactie afbreekt om serialiseerbaarheid te handhaven, verwacht dat de applicatie die fout herkent en passend afhandelt.
MVCC en snapshots bij gelijktijdige lezingen
Veel databases gebruiken Multi-Version Concurrency Control (MVCC) om lezingen en schrijfbewerkingen minder direct van elkaar afhankelijk te maken. In plaats van een lezer altijd te laten wachten op een schrijver, bewaart de database versies van rijen. Een transactie kan dan een snapshot raadplegen die past bij het ingestelde isolatieniveau, terwijl een andere transactie nieuwe wijzigingen voorbereidt. Dat verhoogt vaak de doorvoer bij workloads met veel lezingen.
Een snapshot voorkomt echter niet elk conflict. Een transactie kan een consistente weergave lezen en op basis daarvan een wijziging uitvoeren die niet meer past bij de actuele toestand. Bij snapshot isolation kunnen bijvoorbeeld twee transacties elk een andere rij aanpassen, terwijl gezamenlijk een bedrijfsregel wordt geschonden. Dit staat bekend als write skew. De database ziet mogelijk geen directe botsing op dezelfde rij, hoewel de gecombineerde uitkomst ongeldig is.
Daarom moet een ontwerp bepalen of de regel via een constraint, expliciete rijvergrendeling of striktere isolatie kan worden afgedwongen. Een constraint is vaak het meest robuust voor eenvoudige invarianten, omdat die controle ook geldt wanneer meerdere codepaden schrijven. MVCC brengt bovendien beheer met zich mee: oude rijversies moeten uiteindelijk worden opgeruimd. Langlopende transacties kunnen die opruiming hinderen, opslag laten groeien en snapshots onnodig oud houden. Korte transacties zijn dus ook bij een MVCC-database van belang.
Locks, lockvolgorde en deadlocks
Databases gebruiken locks om wijzigingen te coördineren. Een schrijvende transactie kan bijvoorbeeld een exclusieve lock op een rij nemen, zodat een andere transactie die rij niet tegelijk op een conflicterende manier wijzigt. Afhankelijk van het systeem kunnen locks ook op pagina's, tabellen of bereiken van sleutels werken. De reikwijdte beïnvloedt hoeveel andere verzoeken moeten wachten en hoe groot de kans op blokkering is.
Een deadlock ontstaat wanneer transacties op elkaar wachten in een kring. Transactie A houdt bijvoorbeeld een lock op rij één en vraagt rij twee aan, terwijl transactie B rij twee vasthoudt en vervolgens rij één nodig heeft. Geen van beide kan doorgaan. De database detecteert doorgaans de cyclus en breekt één transactie af, zodat de andere verder kan. De applicatie moet de fout kunnen afhandelen; een afgebroken transactie mag niet worden behandeld alsof alle wijzigingen zijn vastgelegd.
Een vaste lockvolgorde vermindert de kans op zulke cycli. Als alle codepaden eerst rekening A en daarna rekening B aanpassen, ontstaan minder tegengestelde wachtpatronen. Houd daarnaast transacties kort, wijzig alleen de benodigde rijen en zorg voor passende indexen, zodat zoekopdrachten niet meer gegevens hoeven te onderzoeken dan nodig. Een deadlock is niet altijd volledig uit te sluiten, vooral bij complexe workloads. Ontwerp daarom ook gecontroleerde herhaalbaarheid in voor transacties die door de database zijn afgebroken, zonder externe neveneffecten dubbel uit te voeren.
Transactiegrenzen in applicatiecode ontwerpen
De applicatie bepaalt vaak waar een transactie begint en eindigt, en die keuze beïnvloedt zowel correctheid als belasting. Een transactie hoort de bewerkingen te omvatten die samen één consistente wijziging vormen. Bij het plaatsen van een bestelling kunnen het aanmaken van de order en het vastleggen van orderregels bijvoorbeeld één database-eenheid zijn. Een latere e-mail of aanroep naar een externe dienst hoort meestal niet binnen dezelfde open databasetransactie: netwerkvertraging kan locks onnodig lang vasthouden en een database-rollback kan een al verzonden bericht niet ongedaan maken.
Voor acties met externe systemen is een patroon zoals de transactional outbox vaak geschikter. De applicatie schrijft de bedrijfswijziging en een berichtrecord in dezelfde database-transactie. Een afzonderlijk proces verstuurt dat bericht vervolgens en kan verzending opnieuw proberen. Daarmee wordt de koppeling tussen databasecommit en berichtverwerking expliciet gemaakt, zonder te doen alsof twee onafhankelijke systemen één atomaire transactie delen.
Let ook op ORM's en frameworks die transacties impliciet openen of statements automatisch committen. Een fout in een later statement kan afhankelijk van de configuratie een deel van de bewerkingen achterlaten. Controleer daarom de werkelijke transactiegrenzen, uitzonderingsafhandeling en het gedrag van de verbinding na een fout. Een rollback moet plaatsvinden voordat dezelfde verbinding voor een nieuwe bewerking wordt gebruikt, tenzij het framework die toestand aantoonbaar zelf herstelt.
Concurrencyproblemen testen en storingen onderzoeken
Concurrencyfouten zijn lastig te ontdekken met tests die transacties na elkaar uitvoeren. Maak tests waarin meerdere verbindingen bewust tegelijk werken en pauzeer ze op gecontroleerde momenten. Zo kan een test bijvoorbeeld twee transacties dezelfde voorraadwaarde laten lezen voordat beide een bestelling proberen vast te leggen. De uitkomst maakt zichtbaar of de database een invariant afdwingt, of dat de applicatie een verloren update of overschrijding toelaat.
Test met hetzelfde databasesysteem en relevante configuratie als in de omgeving waar de applicatie draait. In-memory databases, mocks en SQLite kunnen ander lockgedrag en andere isolatiesemantiek hebben dan PostgreSQL, SQL Server, MySQL of Oracle. Test niet alleen het succesvolle pad, maar ook rollback, time-outs, deadlockmeldingen en transacties die door serialiseerbaarheidscontroles worden afgebroken. Controleer dat herhaling geen dubbele orders, betalingen of andere neveneffecten veroorzaakt.
Voor onderzoek in productie zijn transactieduur, lockwachttijd, blokkadeketen en queryplannen belangrijke signalen. Een lange wachttijd kan voortkomen uit een transactie die te veel werk omvat, maar ook uit een ontbrekende index waardoor meer rijen worden geraakt. Log voldoende context om de betreffende bewerking te herkennen, zonder gevoelige gegevens onnodig vast te leggen. Meet bovendien op het niveau van afzonderlijke queries en transactiegrenzen: een gemiddelde responstijd kan pieken en incidentele deadlocks verbergen. Daarmee wordt zichtbaar of de oplossing in isolatie, queryvorm, indexering of applicatiegedrag ligt.
Veelgestelde vragen
Hoe voorkom ik verloren updates met optimistic locking?
Met optimistic locking voorkom je verloren updates door bij het opslaan te controleren of de rij sinds het lezen nog ongewijzigd is. Een veelgebruikte aanpak is een versienummer of wijzigingstijdstip op de rij: de update slaagt alleen als de opgeslagen versie overeenkomt met de versie die de applicatie eerder heeft gelezen. Als een andere transactie de rij intussen heeft aangepast, werkt de update nul rijen bij en kan de applicatie het conflict afhandelen.
- Laat de gebruiker de actuele gegevens opnieuw laden of toon een melding.
- Herhaal de bewerking alleen als dat veilig is en de invoer nog geldig blijft.
- Gebruik databaseconstraints voor regels die altijd moeten gelden.
Deze aanpak is vooral geschikt wanneer conflicten relatief zeldzaam zijn en transacties niet langdurig een lock hoeven vast te houden.
Wanneer gebruik ik een SAVEPOINT in een SQL-transactie?
Gebruik een SAVEPOINT wanneer je binnen één transactie een herkenbaar tussentijds herstelpunt nodig hebt, zonder de hele transactie terug te draaien. Na een fout kun je terugrollen tot dat punt en eventueel doorgaan met andere bewerkingen. Dat kan nuttig zijn wanneer een optioneel onderdeel van een grotere wijziging mag mislukken, terwijl de overige wijzigingen nog geldig zijn.
- Maak de savepoint vóór het gedeelte dat eventueel teruggedraaid mag worden.
- Gebruik ROLLBACK TO SAVEPOINT om alleen dat gedeelte ongedaan te maken.
- Controleer hoe jouw database en framework savepoints ondersteunen.
Een savepoint is geen vervanging voor een heldere transactiegrens. Het maakt een transactie ook niet automatisch veilig om na elke fout voort te zetten: sommige fouten zetten de volledige transactie in een mislukte toestand.
Hoe stel ik het isolatieniveau in voor één SQL-transactie?
Je stelt het isolatieniveau voor één SQL-transactie in met de daarvoor bedoelde opdracht van je database, voordat de relevante queries worden uitgevoerd. De precieze syntaxis en het moment waarop de instelling actief wordt, verschillen per databasesysteem. Controleer daarom de documentatie van de database en het gedrag van je driver of framework, in plaats van aan te nemen dat dezelfde opdracht overal hetzelfde werkt.
- Stel het niveau in voordat de transactie gegevens leest of wijzigt.
- Beperk een strengere instelling tot transacties die die bescherming nodig hebben.
- Behandel mogelijke conflicten of afgebroken transacties expliciet.
Test het gekozen niveau met meerdere gelijktijdige verbindingen. Zo controleer je niet alleen of de instelling wordt geaccepteerd, maar ook of de uitkomst de bedrijfsregel beschermt.
Hoe verwerk ik grote batches zonder één lange SQL-transactie?
Verwerk een grote batch meestal in afgebakende stukken, zodat elke transactie een beperkte hoeveelheid werk bevat. Dat verkort de tijd waarin locks worden vastgehouden en maakt herstel na een fout beheersbaarder. De juiste omvang hangt af van de database, de hoeveelheid werk per rij en de vereiste consistentie; er is geen universeel optimaal aantal records per batch.
- Kies een stabiele sortering of sleutelbereik om batches terugvindbaar te maken.
- Leg vast welke items al succesvol zijn verwerkt.
- Maak herhaling veilig, bijvoorbeeld door unieke sleutels of idempotente bewerkingen.
Als alle records absoluut als één geheel moeten slagen, kan opdelen de vereiste atomiciteit veranderen. Bepaal daarom eerst welke consistentie de batch nodig heeft en ontwerp herstel en voortzetting daarop.
Wat gebeurt er met een open SQL-transactie als een verbinding teruggaat naar de connection pool?
Een open SQL-transactie kan problemen veroorzaken als de verbinding wordt teruggegeven aan een connection pool zonder eerst te committen of terug te rollen. De volgende gebruiker van die verbinding kan dan een onverwachte transactietoestand aantreffen, of de database kan locks blijven vasthouden. Pooling maakt de verbinding herbruikbaar, maar garandeert niet vanzelf dat applicatiecode elke transactie correct afsluit.
- Zorg dat elke codeweg eindigt met COMMIT of ROLLBACK.
- Controleer hoe je framework verbindingen na fouten en uitzonderingen opruimt.
- Gebruik waar mogelijk transactionele contexten die automatisch terugrollen bij fouten.
Test dit ook bij time-outs en onderbroken verzoeken. Controleer bovendien de configuratie van de pool en de database, zodat een verbinding met een mislukte of open transactie niet stilzwijgend opnieuw wordt gebruikt.
