Statements en Transacties

Statements:

Gewoonlijk worden SQL statements opeenvolgend en onafhankelijk van elkaar uitgevoerd, waarbij weinig aandacht wordt besteedt voor wat er eerder was gegaan of wat nog gedaan moet worden. Een reeks van INSERT of UPDATE statements, worden uitgevoerd ongeacht of een van de queries in de serie mislukken of fouten oplevert. Dit komt omdat SQL elk statement als een selfcontained unit behandelt, die in geen enkel verband staat met andere statements ervoor of erna.

Transacties:

In sommige situaties echter vormen een aantal SQL statements een logische bewerking op de gegevens, waarbij de acties die door een set van SQL-statements worden uitgevoerd een ‘alles of niets’ plan is. Zo’n reeks statements wordt een transactie genoemd. In dergelijke situaties, zijn niet alleen de acties in de transactie afhankelijk van elkaar, maar het falen in een van de statements in de sequentie betekent dat de hele sequentie moet worden afgebroken en de wijzigingen die door eerdere statements werden gemaakt in dezelfde volgorde moeten worden teruggedraaid, zodat de databank naar de eerdere toestand terugkeert.

Elke instructie die niet formeel wordt omvat in een transactie kan worden beschouwd als een enkele instructie transactie.

Inzicht in transacties

In SQL context bestaat een transactie uit een of meer SQL-instructies als één enkele eenheid. Elk SQL statement in een dergelijke eenheid is afhankelijk van anderen, en de eenheid op zich is niet deelbaar. Als een statement in de eenheid niet met succes kan voltooien, wordt de gehele eenheid teruggedraaid en zullen alle betrokken gegevens worden teruggeplaatst in de staat waarin het zich bevond voordat de transactie was gestart. Aldus wordt een transactie genoemd succesvol zijn ( “gecommitteerd”) indien alle individuele statements met succes zijn uitgevoerd.

Misschien vindt u het moeilijk om te denken aan situaties waarin deze ‘alles of niets’ -situatie nuttig zou zijn. In werkelijkheid zijn transacties overal om ons heen aanwezig: in bankoverschrijvingen, stock-verhandelingen, web-gebaseerde winkelwagens, voorraadbeheer, enzovoorts. In al deze gevallen hangt het succes van de transactie af op een aantal onderling afhankelijke acties die onderling en samen uitgevoerd kunnen worden. Een falen in een van hen moet de transactie annuleren en het systeem terug terug zetten naar zijn vroegere, pre-transactionele staat.

De verwerking van een transactie kan falen vanwege een aantal redenen: softwarefouten, hardware crashes, stroomstoringen, geen ruimte meer op het primaire en of secundair systeemgeheugen, corruptie van de data-opslag media, de gebruiker die al zijn/haar toegewezen CPU-tijd, enz heeft opgebruikt.

Voorbeeld 1:

Een aandelenhandel waarin handelaar A 400 aandelen van de ACME Corp. verkoopt aan handelaar B.

In het bijhouden van de aandelenhandel in het database-systeem, wordt geacht deze handel
voltooid te zijn alleen wanneer handelaar A’s rekening gedebiteerd wordt met 400 ACME Corp. aandelen en handelaar B’s rekening gelijktijdig gecrediteerd wordt met die aandelen. Als een van de vorige twee stappen falen, zou dat betekenen dat de wisseling in het onwenselelijke situatie zou zitten, waaarvan 400 van de ACME Corp. aandelen in het systeem zitten zonder dat er eigenaar aan is verbonden.

Zo kan deze overdracht van 400 ACME Corp. aandelen van handelaar A naar handelaar B als een transactie beschouwd te worden – een enkele eenheid werk die intern meerdere SQL-instructies omvatten:

  • verwijder 400 aandelen van de rekening van handelaar A;
  • voeg 400 aandelen toe aan de rekening van handelaar B;
  • voer de commissieberekeningen uit beide handelaren;
  • bewaar de veranderingen.

Overeenkomstig de voorgaande transactiedefinitie dienen alle statements met succes uitgevoerd te worden. Als een van hen faalt, moet de transactie als zodanig teruggedraaid worden zodat het systeem teruggaat naar zijn eerdere, stabiele toestand. Of, om het anders te zeggen, op geen enkel moment mag het eigendom van de 400 aandelen onduidelijk zijn.

Voorbeeld 2:

Het toevoegen van een nieuwe vlucht aan onze voorbeelddatabase.

Bij het toevoegen van een vlucht, moet de luchtvaartmaatschappij drie stappen uitvoeren:

  • bepalen van de vluchtroute (bron en bestemming) en vliegtuig in tabel flight;
  • bepalen van de vertrekdagen en tijden van de vlucht in de tabel flightdep;
  • bepalen van het aantal klassen en beschikbare plaatsen in elke klasse in de tabel
    flightclass.

Op de databaseniveau, vereisen deze bewerkingen drie verschillende
tabellen aangepast moeten worden. Als een van deze drie stappen zouden mislukken, moet het systeem alle wijzigingen ongedaan maken om een inconsistente of incomplete vluchtrecord te voorkomen.

De drie voorgaande taken vormen een enkele transactie. Een falen in een van hen zou moeten zorgen dat de gehele transactie geannuleerd moet worden en dat het systeem terug moet keren naar zijn vorige toestand.

De SQL transactie architectuur

De MySQL transactie architectuur maakt het mogelijk om SQL statements in transacties te groeperen en uit te voeren als zijde een collectief geheel. Terwijl commerciële producten zoals Oracle en Microsoft SQL Server het transactie model al heel lang ondersteunen, bieden de open-source alternatieven zoals PostgreSQL, MySQL dit pas vanaf versie 4.0, en is dit beperkt aan specifieke storage engines om de gebruikers meer keuze en flexibiliteit te bieden.

De MySQL transactie architectuur volledig voldoet aan de ACID testen voor transactieveiligheid via de standaard InnoDB opslag engine. Oudere tabeltypes, zoals het MyISAM-type, ondersteunen geen transacties. In dergelijke systemen kunnen transacties enkel worden geïmplementeerd door het gebruik tabelvergrendeling (hoewel dit volledig zou kunnen voldoen aan de ACID-standaard).

De ACID eigenschappen

De term ‘ACID’ is een acroniem, die vier (gegarandeerde) eigenschappen voor transactieveiligheid aangeeft waaraan elke transactionele RDBMS moet voldoen. Om te voldoen aan de ACID-standaard, moet een RDBMS de volgende kenmerken bezitten:

  • Atomiciteit
  • Consistentie
  • Isolatie
  • Duurzaamheid

Atomiciteit

Atomiciteit betekent dat elke transactie als een ondeelbare, ‘alles of niets’ eenheid moet worden behandeld. Een transactie die bestaat uit twee of meer taken wordt pas geacht succesvol te zijn indien alle onderliggende taken succesvol zijn. Indien eender welk deel van de transactie mislukt, betekent dat de volledige transactie is mislukt en het systeem moet worden teruggezet naar zijn pre-transactionele staat.

Een atomair systeem moet in elke situatie atomiciteit garanderen, met inbegrip van stroomstoringen, fouten en crashes.

Er bestaan twee populaire technieken voor de implementatie van atomiciteit: wrtie-ahead logging en schaduw paging. In beide gevallen, moet vergrendeling van data verworven worden.

Write-ahead logging maakt het mogelijk om een database ‘in-place’ (op de databank zelf) bij te werken. Alle wijzigingen worden weggeschreven naar een log voordat ze worden vastgelegd. Doorgaans worden zowel ‘redo’ als de ‘undo’ informatie in het logboek opgeslagen. In het geval van een storing kan het systeem dit logboek bekijken en vergelijken wat er eigenlijk was gedaan en wat het moest doen. Op basis van deze vergelijking kan het programma beslissen om, ongedaan te maken wat het had begonnen, te voltooien wat het had begonnen, of de dingen houden zoals ze zijn.

Shadowing maakt wijzigingen op een databank die niet in-place (niet op de databank zelf) staat. Wanneer gegevens worden gewijzigd, wordt een gedeeltelijke kopie van de database in een schaduw pagina (eenheid van fysieke opslag) gezet en worden de wijzigingen toegepast op die gedeeltelijke kopie. Alleen wanneer de transactie in de schaduw pagina met succes is uitgevoerd zal de transactie klaar zijn om doorgevoerd te worden en zal de schaduw pagina duurzaam worden: alle pagina’s die verwijzen naar het origineel worden bijgewerkt om te verwijzen naar de nieuwe vervangende pagina. Omdat de schaduw pagina enkel is ‘geactiveerd’ wanneer de transactie is doorgevoerd, is de transactie atomair.

Verwijzend naar het vorige beursmodel bijvoorbeeld, betekent atomiciteit dat de verkoop van aandelen door ‘handelaar A’ en de aankoop van dezelfde aandelen door
‘handelaar B’ niet onafhankelijk van elkaar optreden, en beide moeten plaatsvinden voordat de transactie als voltooid wordt beschouwd.

Ook in het voorbeeld van de luchtvaartmaatschappij impliceert atomiciteit dat het niet mogelijk dat het systeem een vlucht kan toevoegen zonder een overeenkomstige vertrektijden en klasse/zetel informatie toe te voegen.

Zodat een transactie aan de atomiciteit-eis te voldoet, moet als een van de statement faalt, alle voorgaande statements worden teruggedraaid om te zorgen dat de integriteit van de gegevensbank onaangetast is. Dit is bijzonder belangrijk in bedrijfskritische, real-world toepassingen (zoals financiële systemen) die data entry of updates uitvoeren en een hoge mate van veiligheid van niet-gedetecteerd verlies van gegevens vereisen.

Consistentie

De consistentie eigenschap zorgt ervoor dat elke transactie de database van de ene geldige staat naar de andere geldige staat zal brengen. Alle gegevens die naar de database worden geschreven moeten geldig zijn volgens alle gedefinieerde database-regels, zoals (maar niet beperkt tot) constraintstriggers en elke combinatie daarvan. Dit garandeert dat elke gebruiker, applicatie, noch programmeerfout niet kan leiden tot de schending van een gedefinieerde regel.

Onder verwijzing naar het voorgaande voorbeeld van de luchtvaartmaatschappij, zou de consistentie eigenschap ervoor zorgen dat, indien een vlucht wordt verwijderd, alle gegevens met betrekking tot die vlucht, met inbegrip van het vertrektijden en klasse/zetel
informatie, ook worden verwijderd.

Neem aan dat tabel consistency_test, gemaakt wordt met:

CREATE TABLE consistency_test (A, B, CHECK (A + B = 100));

Omdat consistentie na elke transactie wordt gecontroleerd, is het geweten dat voordat de volgende transactie begint, A + B = 100. Ga ervan uit dat een transactie 10 probeert af te trekken van A zonder dat B wordt veranderd. Voordat de transactie in het systeem kan worden vastgelegd, zal het systeem de resulterende toestand van de gegevens controleren of het in strijd is met de regels van de database. Deze controle zal laten blijken dat de gegevens niet consistent zijn met de CHECK constraint. De gehele transactie zal worden geannuleerd en de getroffen rijen zullen worden teruggedraaid naar hun pre-transactionele staat.

Isolatie

Strict isolatie

Isolatie garandeert dat de gelijktijdige uitvoering van transacties (door gelijktijdige cliënten) resulteert in de toestand van het systeem dat zou worden verkregen indien de transacties serieel zouden worden uitgevoerd, d.w.z. na elkaar. Isolatie betekent dat elke transactie moet plaatsvinden in een eigen aparte en onafhankelijke ’transactie ruimte’ en de effecten op de database onzichtbaar blijven voor de andere databasecliënten totdat de transactie de uitvoering heeft voltooid (ongeacht of de transactie succesvol was of niet). Dit is vooral belangrijk bij meerdere gebruikers en multi-transactionele systemen, omdat de afwezigheid van de isolatie-eigenschap van twee conflicterende handelingen (van gelijktijdige clients) snel kan leiden tot data corruptie, omdat elke transactie elkaars integriteit zou schenden.

Als we verwijzen naar het vorige beursmodel bijvoorbeeld, impliceert isolatie dat de transactie tussen de twee handelaars onafhankelijk is van andere transacties (van gelijktijdige cliënten) op de uitwisselingsdatabase en het resultaat pas zichtbaar is voor het grote publiek nadat deze is voltooid. Bij het overwegen van een vluchtwijziging, impliceert het dat de lijst met beschikbare vluchten wordt bijgewerkt slechts eenmaal de transactie is voltooid, en geen invloed heeft op de andere transacties die op een bepaald moment nog zou in verwerking zouden kunnen zijn.

Relaxed isolatie

De enige manier om absolute scheiding (strict serializability) te verkrijgen zou zijn om ervoor te zorgen dat er slechts één cliënt per moment toegang zou kunnen krijgen tot de database. Dit is echter geen praktische oplossing, helemaal niet als het gaat om een ​​multi-user RDBMS. In plaats daarvan wordt de mogelijkheid om op het zelfde moment veel gebruikers toegang tot de gegevens te geven de isolatie eigenschap versoepeld (relaxed serializability). De transactionele systemen voorzien isolatie met behulp van het beheer van gelijktijdigheid. Er zijn verschillende gelijktijdigheid beheermethoden die kunnen worden gebruikt,
die voorzien in een aantal (hogere of lagere) niveaus van isolatie.

Afhankelijk van de beheermethode van gelijktijdigheid en het gebruikte isolatieniveau, kunnen de gevolgen van een transactie die bezig is meer of minder
toegankelijk/zichtbaar zij voor een andere transactie/cliënt. Een lager isolatieniveau vergroot de mogelijkheid om veel gebruikers toegang tot gegevens op hetzelfde moment te geven alsook de prestaties van het systeem, maar verhoogt ook het aantal effecten door gelijktijdigheid (zoals ‘dirty’ leesopdrachten of verloren wijzigingen) die gebruikers tegenkomen. Omgekeerd kan een hoger isolatieniveau het aantal effecten van gelijktijdigheid verminderen die gebruikers kunnen tegenkomen, maar zal het meer systeembronnen vereisen en de kans verhogen dat één transactie de andere transactie zal blokkeren.

De meeste transactionele systemen maken gebruik van vergrendeling op paginaniveau ofwel vergrendeling op rij-niveau om de wijzigingen door verschillende transacties van elkaar te isoleren. Dit gaat uiteraard gepaard met enig verlies in de prestaties.

Duurzaamheid

Duurzaamheid zorgt ervoor dat de veranderingen die gemaakt zijn door een vastgelegde (succesvol uitgevoerde) transactie niet verloren zal gaan, zelfs niet in het geval van stroomuitval, crashes, of fouten. Dus, zodra een transactie wordt uitgevoerd, moeten de resultaten persistent (permanent) opgeslagen worden in een niet-vluchtig geheugen, zelfs als de database onmiddellijk daarna crasht.

Beide technieken zijn populair om atomiciteit te implementeren en kunnen ook gebruikt worden voor de implementatie van duurzaamheid: write-ahead logging en schaduw paginering.

Veel DBMS systemen implementeren duurzaamheid door het schrijven van alle (vastgelegde) transacties in een transactie log op niet-vluchtige geheugens voordat het de vastlegging (commit) bevestigd. Deze database log houdt elke en alle updates bij die zijn gemaakt op tabellen, queries, rapporten, en ga zo maar door. Een transactie wordt geacht vastgelegd te worden pas nadat het in het logboek wordt ingevoerd. In het geval van een systeemcrash of beschadiging van de gegevensopslag op de media, is het mogelijk na een herstart van het systeem om de toestand van het systeem te herstellen van vlak voor het falen door de transactie log opnieuw te verwerken.

In het kader van de vorige beurshandel bijvoorbeeld, betekent duurzaamheid dat zodra de overdracht van de aandelen van handelaar A naar handelaar B met succes is afgerond, het systeem die toestand moet weerspiegelen, zelfs als een systeemfout hierop volgt.

Of, bij de behandeling van de luchtvaartmaatschappij database, vluchten die zijn toegevoegd mogen niet verdwijnen uit de database in het geval van een systeemfout.

ACID eigenschappen in gedistribueerde database systemen

Het garanderen van ACID eigenschappen in een gedistribueerde transactie over een gedistribueerde databank, waarin geen enkel knooppunt (node) verantwoordelijk is voor alle gegevens die van invloed zijn op een transactie, geeft weer extra complicaties. Netwerkverbindingen kunnen mislukken, of een knooppunt kan misschien met succes zijn deel van de transactie voltooien en vervolgens is het nodig om de transactie terug te draaien door een falen op een andere knooppunt.

In gedistribueerde transacties, moeten alle deelnemende servers moeten alle servers samenwerken alvorens een vastlegging (commit) bevestigd kan worden. Dit wordt meestal gedaan door een twee-fase vastleggings-protocol (niet te verwarren met twee-fase vergrendeling). Dit protocol voorziet in atomiciteit voor gedistribueerde transacties om ervoor te zorgen dat elke deelnemer aan de transactie akkoord gaat of de transactie vastgelegd mag worden gepleegd of niet. Kort samengevat, in de eerste fase, één knooppunt (de coördinator) ondervraagt de andere knooppunten (de participanten) en alleen wanneer ze allemaal antwoorden dat ze klaar zijn doet de coördinator, in de tweede fase, de transactie formaliseren.

MySQL en de ACID eigenschappen

MySQL voldoet volledig aan de ACID vereisten voor een transactie-veilige RDBMS als volgt:

Atomiciteit wordt afgehandeld door het opslaan van de resultaten van transactionele statements (de gewijzigde rijen) in een geheugenbuffer (shadowing) en schrijft deze resultaten naar schijf en naar de binaire log van de buffer slechts eenmaal de transactie is vastgelegd (commited). Dit zorgt ervoor dat de statements in een transactie werken als een ondeelbare eenheid en hun effecten collectief gezien worden, of helemaal niet.

Consistentie wordt voornamelijk behandeld door het loggingmechanisme van MySQL(shadowing), die alle wijzigingen in de database registreert en een controle spoor (audit trail) voorziet voor het transactieherstel. Naast het logging proces, voorziet MySQL vergrendelingsmechanismen die ervoor zorgen dat alle tabellen, rijen en
indexen die deel uitmaken van de transactie lang genoeg geblokkeerd zijn door het initiërende proces om ofwel de transactie vast te leggen of de transactie terug te draaien.

Isolatie: Semafoor variabelen aan de server zijde en vergrendelingsmechanismen fungeren als verkeersleider om programma’s te helpen hun eigen isolatie mechanismen te beheren. Bijvoorbeeld, maakt MySQL’s InnoDB mechanisme voor dit doel gebruik van een finegrained rij-niveau vergrendeling. (Een semafoor is een variabele die gebruikt wordt voor het beheer van toegang, door meerdere processen tot een gemeenschappelijke bron (data, bijvoorbeeld) in een gelijktijdig systeem.)

Duurzaamheid: MySQL implementeert duurzaamheid door het onderhouden van een binair transactielogboekbestand die de wijzigingen in het systeem bijhoudt
tijdens de duur van een transactie (schaduw). Bij een hardwarestoring of abrupt afsluiten van het systeem, is het herstellen van verloren gegevens is een relatief eenvoudige taak met behulp de laatste back-up in combinatie met de log wanneer het systeem opnieuw wordt opgestart. Standaard zijn InnoDB tabellen 100 procent duurzaam (dat wil zeggen alle transacties die voor de crash in het systeem worden vastgelegd zijn gehouden te worden hersteld tijdens het herstelproces), terwijl MyISAM tabellen gedeeltelijke duurzaamheid biedt.

Transacties

MySQL ondersteunt native transacties enkel via zijn InnoDB of BerkeleyDB storage engine, wat betekent dat de volgende commando’s alleen gebruikt kunnen worden voor die bepaalde engine.

De standaard-type voor nieuwe tabellen in MySQL is InnoDB, maar u kunt desgewenst in MySQL expliciet bepalen dat u een InnoDB tabel wil door het toevoegen van de optionele ENGINE=InnoDB clausule aan uw CREATE TABLE commando.

Voor bestaande tabellen, kunt u het tabeltype wijzigen door middel van het ALTER TABLE commando, gevolgd door het opgeven van een nieuwe ENGINE clausule. Hier zijn enkele voorbeelden:

MySQL
ALTER TABLE flight ENGINE=INNODB;
(32 row(s) affected Records: 32 Duplicates: 0 Warnings: 0)
ALTER TABLE flightdep ENGINE=INNODB;
(108 row(s) affected Records: 108 Duplicates: 0 Warnings: 0)
ALTER TABLE flightclass ENGINE=INNODB;
(7 row(s) affected Records: 7 Duplicates: 0 Warnings: 0)

Let op: De ALTER TABLE opdracht werkt door een back-up van de gegevens in de tabel, te maken, de tabel te wissen, de tabel opnieuw aan te maken met de gespecificeerde aanpassingen, en vervolgens de gegevens in de back-up in te voegen in te nieuwe tabel. Een storing in een van deze stappen kan resulteren tot het verlies of beschadiging van uw gegevens. Daarom is het een goed idee om altijd back-up van de tabel voordat u het ALTER TABLE commando gaat gebruiken.

Een eenvoudige transactie

Om een transactie te initiëren en aan te geven de alle volgende statements beschouwd moeten worden als een eenheid van werk biedt MSSQL het BEGIN TRANSACTION statement en MySQL de START TRANSACTIONBEGIN WORK of BEGIN statements om het begin van een transactie markeren.

MSSQL MySQL
BEGIN TRANSACTION;

(Command(s) completed successfully.)

START TRANSACTION;
(0 row(s) affected)
BEGIN WORK;
(0 row(s) affected)
BEGIN;
(0 row(s) affected)

Meestal wordt het BEGIN TRANSACTION/START TRANSACTION commando gevolgd door de SQL-instructies die deel uitmaken van de transactie. Laten we aannemen dat de transactie bestaat uit het toevoegen van een nieuwe vlucht in het systeem en dat die stappen omvatten:

  1. het creëren van een record voor de vlucht,
  2. definiëren van de dag en het tijdstip van de vlucht vertrek, en
  3. definiëren klasse en zitplaats van de vlucht.
MSSQL MySQL
BEGIN TRANSACTION;

Command(s) completed successfully.

INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)
INSERT INTO flightclass 
  (FlightID, ClassID, MaxSeats, BasePrice)
VALUES (834, 'A', 20, 200);
(1 row(s) affected)
START TRANSACTION;
(0 row(s) affected)
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)
INSERT INTO flightclass 
  (FlightID, ClassID, MaxSeats, BasePrice)
VALUES (834, 'A', 20, 200);
(1 row(s) affected)

Kijk met een SELECT-query in deze tabellen om te zien of de gegevens correct zijn ingevoerd:

MSSQL MySQL
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
(No column name)
1 1
COUNT(FlightID)
1 1
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
(No column name)
1 1
COUNT(FlightID)
1 1

Zodra de SQL-statements allemaal zijn uitgevoerd, kunt u het ofwel de gehele transactie opslaan naar schijf met het commande COMMIT commando of alle gemaakte veranderingen ongedaan maken met het commando ROLLBACK. Hier is een voorbeeld van het terugdraaien:

MSSQL MySQL
ROLLBACK;
Command(s) completed successfully. 
ROLLBACK;
(0 row(s) affected)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
(No column name)
1 0
COUNT(FlightID)
1 0
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
(No column name)
1 0
COUNT(FlightID)
1 0

Opmerking: Als uw transactie gepaard gaat met wijzigingen in zowel transactionele en als niet-transactional tabellen, kan het deel van de transactie dat te maken heeft met de niet-transactionele tabellen niet worden teruggedraaid met een ROLLBACK opdracht. In een dergelijke situatie, zal MySQL een foutmelding teruggeven van een onvolledige rollback, zoals in het volgende voorbeeld:

MySQL
ROLLBACK;
(ERROR 1196: Some non-transactional changed tables couldn't be rolled back)

Nu, voer de transactie opnieuw uit, ditmaal met als doel om het op te slaan.

MSSQL MySQL
BEGIN TRANSACTION;

Command(s) completed successfully

INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)
INSERT INTO flightclass 
  (FlightID, ClassID, MaxSeats, BasePrice)
VALUES (834, 'A', 20, 200);
(1 row(s) affected)
START TRANSACTION;
(0 row(s) affected)
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)
INSERT INTO flightclass 
  (FlightID, ClassID, MaxSeats, BasePrice)
VALUES (834, 'A', 20, 200);
(1 row(s) affected)

Er is een interessant experiment die u op dit punt kunt uitvoeren. Open een andere client-verbinding met de server en controleer of de vorige SQL-query’s hebben geleid tot wijzigingen in de database.

Let op: In MSSQL staat het TRANSACTION ISOLATION LEVEL standaard op READ COMMITTED en de READ_COMMITTED_SNAPSHOT standaard OFF. U kan met een ALTER DATABASE de READ_COMMITTED_SNAPSHOT aan of uit zetten (let wel op dat alle andere verbindingen tot die database gesloten moeten zijn). Als de READ_COMMITTED_SNAPSHOT ON staat kan u via een andere verbinding werken op alle tabellen die nog geen doorgevoerde (committed) wijzingen hebben. U zal niet de waarden kunnen zien die in de transactie pijplijn zijn aangemaakt maar niet doorgevoerd zijn. Als kunt ook als u de READ_COMMITTED_SNAPSHOT wil aanpassen de tabelgevens lezen die in de transactiepijplijn zitten met behulp van het WITH(NOLOCK) en WITH(READPAST) sleutelwoord. Zie volgende websites voor meer uitgebreide informatie over dit onderwerp:

https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

https://www.mssqltips.com/sqlservertip/2977/demonstrations-of-transaction-isolation-levels-in-sql-server/

http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/

http://stackoverflow.com/questions/1248423/how-to-see-active-sql-server-connections

https://msdn.microsoft.com/en-us/library/ms173763.aspx

MSSQL MySQL
SELECT COUNT(FlightID)
FROM flight WITH(READPAST)
WHERE FlightID=834;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
(No column name)
1 0
COUNT(FlightID)
1 0

Dit is een voorbeeld van isolatie in actie. Zoals in de voorgaande paragraaf beschreven, betekent isolatie dat de resultaten van een transactie alleen zichtbaar worden wanneer de transactie succesvol is doorgevoerd. Omdat de transactie nog in volle gang is en het nog niet opgeslagen is op de harde schijf, is dit onzichtbaar voor andere cliënten van dezelfde database (Indien het zichtbaarheid tussen transacties gewenst is, kan dit worden bereikt door het instellen van een ander transactie isolatieniveau; Dit wordt in nader besproken in het hoofdstuk ‘Transactie isolatie niveau’s’).

De opdracht COMMIT zal alle gewijzigde records bewaren in de database. De opdracht COMMIT markeert ook het einde van de transactieblokkering. Zodra de transactie is doorgevoerd naar de database, zullen de doorgevoerde gegevens zichtbaar zijn voor andere cliënt sessies.

MSSQL MySQL
COMMIT;
Command(s) completed successfully
COMMIT;
(0 row(s) returned)

Stroomdiagram van een eenvoudige transactiecyclus

Kan ik een nieuwe transactie starten vanuit een bestaande transactie?

In MySQL kan men geen transacties nesten en zal betekenen dat de START TRANSACTION van de tweede transactie binnen de eerste transactie dat er automatisch impliciet een COMMIT wordt uitgevoerd van de eerste.

Op soortgelijke wijze zullen vele andere MySQL commando’s, wanneer aangeroepen, een COMMIT impliciet uitvoeren. Hier volgt een korte lijst:

  • CREATE DATABASE / CREATE TABLE
  • DROP DATABASE / TRUNCATE TABLE / DROP TABLE
  • CREATE INDEX / DROP INDEX
  • ALTER TABLE / RENAME TABLE
  • LOCK TABLES / UNLOCK TABELLEN
  • CREATE USER / DROP USER / RENAME USER
  • GRANT / REVOKE / SET PASSWORD
  • SET AUTOCOMMIT = 1

De AND CHAIN en RELEASE clausules

Standaard wacht MySQL op de volgende opdracht eens een transactie voltooid is. Echter, ondersteund de MySQL’s COMMIT en ROLLBACK commando’s twee extra clausules die gebruikt kunnen worden om te wijzigen wat er gebeurt eens een transactie is voltooid.

  • De AND CHAIN clausule zorgt ervoor dat MySQL onmiddellijk een nieuwe transactie start (met hetzelfde isolatie niveau als devorige) gevolgd door een commit of rollback.
  • De RELEASE clausule zorgt ervoor dat MySQL de verbinding verbinding verbreekt gevolgd door een commit of rollback.

Het toevoegen van de NO prefix aan een van deze twee clausules ontkracht de bewerking:
AND NO CHAIN en NO RELEASE.

MySQL
COMMIT AND NO CHAIN RELEASE;
(0 row(s) affected)
ROLLBACK AND CHAIN NO RELEASE;
(Error Code: 2013. Lost connection to MySQL server during query)

Tip: Het is ook mogelijk om dit gedrag aan te passen door het instellen van de MySQL completion_type variabele, hetzij op sessie basis met SET of globaal via het MySQL configuratiebestand.

MySQL
SHOW VARIABLES WHERE Variable_Name = "completion_type";
(1 row(s) affected)
Value_name Value
1 completion_type NO_CHAIN
SET completion_type = 0;
SET completion_type = 1;
SET completion_type = 'NO_CHAIN';
SET completion_type = 'CHAIN';
(0 row(s) affected)

Savepoints

De InnoDB storage engine ondersteunt een bijkomend nuttige functie die de mogelijkheid biedt om een transactie gedeeltelijk terug te draaien transactie in plaats van volledig. Dit wordt bereikt door het gebruik van opslagpunten: punten die door de gebruiker worden gedefinieerd kunnen worden gebruikt om onderliggende stadia binnen een transactie te markeren. Deze opslagpunten maken het mogelijk specifieke delen van een transactie terug te draaien in plaats van de gehele transactie.

Opslagpunten binnen een transactie worden ingesteld met het de SAVEPOINT commando dat een gebruiker-gedefinieerde identifier accepteert.

De ROLLBACK TO SAVEPOINT commando kan vervolgens worden gebruikt om een in uitvoering zijnde transactie terug te draaien naar het genoemde opslagpunt, wat betekent het omkeren van alle wijzigingen na het opslagpunt.

Savepoints: een voorbeeld

MySQL
START TRANSACTION;
(0 row(s) affected)
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
SAVEPOINT flight1;
(0 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)
SAVEPOINT flight2;
(0 row(s) affected)
INSERT INTO flightclass 
  (FlightID, ClassID, MaxSeats, BasePrice)
VALUES (834, 'A', 20, 200);
(1 row(s) affected)
SAVEPOINT flight3;
(0 row(s) affected)

Op dit moment zijn er drie opslagpunten, waarvan elk correspondeert met een wijziging van een tabel. Controleer of de tabellen inderdaad zijn gewijzigd voor en nadat u het systeem hebt teruggedraaid naar het bewaarpunt van flight2.

MySQL
SELECT COUNT(FlightID)
FROM flightclass
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 1
ROLLBACK TO SAVEPOINT flight2;
(0 row(s) affected)
SELECT COUNT(FlightID)
FROM flightclass
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 0

Merk op dat de veranderingen die zijn gemaakt in de andere tabellen nog steeds persistent zijn. Het is belangrijk om te weten dat de transactie nog steeds in uitvoering is en dat een opdracht ROLLBACK TO SAVEPOINT te geven de transactie niet doorvoert of terugdraait. Sluit de transactie af door ook alle overgebleven veranderingen terug te draaien.

MySQL
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 1
ROLLBACK;
(1 row(s) returned)
SELECT COUNT(FlightID)
FROM flightdep
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 0

Er zijn een aantal belangrijke dingen te onthouden over opslagpunten van het vorige voorbeeld:

  • Er kunnen meerdere opslagpunten per transactie worden gemaakt, mits zij elk een unieke identificatie hebben.
  • Het herhalen van een identificatie overschrijft het eerder ingesteld opslagpunt met detzelfde identificatie.
  • Het terugkeren naar een eerder opslagpunt zal de transactie niet te beëindigen. Om de transactie te beëindigen moet u gebruik maken van de COMMIT of ROLLBACK commando’s. Echter, het terugkeren naar een gespecificeerd opslagpunt verwijdert alle opslagpunten ingesteld na dat punt. Als het vermelde opslagpunt niet bestaat in de ROLLBACK TO SAVEPOINT opdracht, zal MySQL een fout genereren.
  • Een opslagpunt kan worden verwijderd met behulp van het RELEASE SAVEPOINT commando, dat een opslagpunt identificatie accepteert en dat opslagpunt van de stapel verwijdert. Merk op dat deze opdracht geen impliciete COMMIT of ROLLBACK uitvoert, zodat de transactie in uitvoering blijft totdat een expliciete COMMIT of ROLLBACK wordt uitgevoerd.

Beheren van transactioneel gedrag

MySQL biedt twee variabelen om het transactioneel gedrag te beheren:

  • de AUTOCOMMIT variabele en
  • de TRANSACTIE ISOLATION LEVEL variabele.

Beheren transactioneel gedrag: Automatisch vastleggen (commits)

Standaard doet MySQL de resultaten van elke SQL-query naar de database doorvoeren zodra deze is uitgevoerd. Dit wordt aangeduid als autocommit mode en dat is de reden dat je niet elke MySQL-sessie moet beginnen met een START TRANSACTIE statement of te beëindigen met een COMMIT of ROLLBACK. Of, om het anders te zeggen, MySQL behandelt elke query als een enkelvoudige-statement transactie.

Dit standaard gedrag kan worden gewijzigd via de speciale AUTOCOMMIT variabele, die de MySQL autocommit modus beheert. Je kunt de waarde van de autocommit variabele vinden door het uitvoeren van het volgende statement:

MySQL
SHOW VARIABLES WHERE Variable_Name = "AUTOCOMMIT";
-- default is "ON"
(1 row(s) returned)
Value_name Value
1 autocommit ON

Het volgende fragment toont, het uitschakelen van het MySQL-gedrag voor het intern doorvoeren van een COMMIT commando na elke SQLinteractie:

MySQL
SET AUTOCOMMIT = 0;
SET AUTOCOMMIT = 1;
SET AUTOCOMMIT = 'OFF';
SET AUTOCOMMIT = 'ON';

SET AUTOCOMMIT = 0 ;
(0 row(s) affected)
ROLLBACK AND CHAIN NO RELEASE;
(Error Code: 2013. Lost connection to MySQL server during query)

Hierop volgend zal elke wijziging op een tabel niet worden opgeslagen in de database totdat expliciet het COMMIT commando wordt gegeven. Eigenlijk zal het beëindigen van een MySQL-sessie ervoor zorgen dat zonder dat een COMMIT commando gegeven is, de database automatisch een ROLLBACK zal doen en alle wijzigingen ongedaan maken, waardoor al het werk gedaan tijdens deze sessie teniet wordt gedaan. Het volgende zal dit aantonen.

MySQL
SET AUTOCOMMIT = 0 ;
(0 row(s) affected)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 0
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)

Start een nieuwe sessie en controleer de tabel. Het zal de aangebrachte wijzigingen niet bevatten, omdat zij niet aan het eind van de laatste niet werden doorgevoerd.

MySQL
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 0

Opmerking: De autocommit variabele is een sessievariabele en staat altijd standaard op ‘1’ wanneer een nieuwe cliëntsessie begint. De autocommit variabele heeft alleen invloed op transactionele tabeltypes zoals InnoDB. Als het gaat om niet-transactionele tabeltypes zoals MyISAM, heeft de autocommit variabele geen effect en worden wijzigingen van die tabellen altijd onmiddellijk opgeslagen.

Beheren transactioneel gedrag: Isolatieniveau transacties

Een van de belangrijkste eigenschappen van een transactie geschikte RDBMS is zijn vermogen om de verschillende sessies te ‘isoleren’ die op elke instantie van de server worden uitgevoerd.

In een single-user omgeving, is de eigenschap grotendeels irrelevant. Er is niets te isoleren omdat meestal slechts één sessie actief is op elk moment. In het echt geldt voor complexere dat deze aanname zeer onwaarschijnlijk is.

In een multi-user omgeving, zullen op een bepaald moment meestal veel RDBMS sessies actief zijn. In voorbeeld van beurshandel die we eerder hebben besproken, is het onwaarschijnlijk dat er slechts één transactie plaatsvindt op een bepaald moment. Het is waarschijnlijker dat er honderden transacties tegelijk zullen plaatsvinden. In een dergelijke situatie, is het essentieel dat de RDBMS de transacties isoleert zodat zij niet met elkaar zullen interfereren, terwijl tegelijkertijd de prestatie van de database er niet onder zal lijden.

Om het belang van isolatie te begrijpen, bedenk wat er zou gebeuren als het niet was afgedwongen. In afwezigheid van transactie-isolatie, zouden verschillende SELECT-instructies verschillende resultaten binnen halen binnen de context van dezelfde transactie, omdat de onderliggende gegevens door andere transacties in de tussentijd werden aangepast. Dit zou leiden tot inconsistentie en zou het moeilijk maken om op een​​bepaalde resultaatset te vertrouwen of om te gebruiken als basis voor de berekeningen met enige mate van vertrouwen. Isolatie legt dus een zekere mate van afscherming tussen transacties om er zeker van te zijn dat een applicatie alleen consistente gegevens ziet in het kader van een transactie.

MySQL voorziet de volgende vier isolatieniveau’s volgens de ANSI/ISO SQL-specificatie:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Deze transactie isolatie niveau’s bepalen in welke mate andere transacties kunnen “zien” in een transactie in uitvoering. Ze zijn gerangschikt in hiërarchische volgorde, te beginnen met de minst veilige (en meest problematische) niveau’s en geleidelijk verschuivend naar het meest veilige niveau; te beginnen met de hoogste prestaties en geleidelijk verschuivend naar niveaus met lagere prestaties.

Deze isolatie niveaus kunnen gemanipuleerd worden met de TRANSACTION ISOLATION
LEVEL
 variabele, die nader wordt besproken in de paragraaf ‘Modifying the Transaction Isolation Level’.

Optimaliseren transactionele prestaties: Selecteren van geschikt isolatieniveau

De omgekeerde relatie tussen transactie isolatieniveau (transactie veiligheid) en de prestaties.

Het READ UNCOMMITTED isolatieniveau

Het READ-UNCOMMITTED isolatieniveau biedt een minimum aan afscherming tussen transacties. Naast het feit dat het kwetsbaar is voor ‘phantom reads‘ en ‘unrepeatable reads‘, kan een transactie op dit isolatieniveau gegevens lezen die nog niet door andere transacties zijn vastgelegd (committed). Als deze transactie nu gebruik maakt van de niet-vastgelegde wijzigingen van andere transacties als basis voor zijn eigen berekeningen, en die niet-vastgelegde wijzigingen worden vervolgens teruggedraaid door hun bovenliggende transacties, kan dit leiden tot aanzienlijke data corruptie.

Bekijk volgende afbeelding als voorbeeld. Omdat de tweede transactie in staat is de niet-vastgelegde veranderingen van de eerste transactie te zien, kan het aantal vluchten dat het ziet variëren tijdens de duur van de eerste transactie. Als gevolg, dat op een bepaald moment, de tweede transactie werkt met onjuiste gegevens, afhankelijk van of de eerste transactie de zijn wijzigingen vastlegt of terugdraait (vandaar de term “dirty read” voor dit soort fouten).

Het READ UNCOMMITTED isolatieniveau en een ‘dirty‘ leesopdracht

Het READ COMMITTED isolatieniveau

Minder veilig dan het REPEATABLE READ isolatieniveau is het READ COMMITTED isolatieniveau. Op dit niveau kan een transactie de vastgelegde wijzingen van andere transacties zien gedurende de duur van de transactie. Anders gezegd betekent dit dat meerdere SELECT statements binnen dezelfde transactie verschillende resultaten zou kunnen teruggeven als de corresponderende tabellen in de tussentijd worden gewijzigd door andere transacties.

De volgende afbeelding toont een voorbeeld hiervan. In dit geval, zal de tweede transactie de hele tijd geen enkel record zien gedurende de looptijd van de eerste transactie. Echter, zodra de eerste transactie haar veranderingen vastlegd, zal de tweede een vlucht zien, ook al is het nog in uitvoering.

Dit is natuurlijk een probleem, als de tweede transactie twee verschillende resultaten voor dezelfde handeling ziet, zal het niet weten welke de juiste is waarop het moet vertrouwen. Extrapoleer een beetje en ga ervan uit dat in plaats van een enkele transactie, veel transacties wijzigingen in de database vastleggen en je zult zien dat elke query die door een transactie zou een wordt uitgevoerd een ander resultaat zou kunnen geven (vandaar de term
‘unrepeatable read’ ‘voor dit soort situaties).

Het REPEATABLE READ isolatieniveau

Voor toepassingen die bereid zijn om een beetje van de de veiligheid in te leveren  voor betere prestaties, biedt MySQL het REPEATABLE READ isolatieniveau. Op dit niveau, zal een transactie geen wijzigingen zien die worden uitgevoerd door gelijktijdige transacties totdat het zichzelf heeft gesloten.

In dit geval kan de tweede transactie de nieuwe vlucht zien dat door de eerste is toegevoegd op het moment dat beide transacties zijn voltooid. Dit is in feite, de manier dat veel gebruikers verwachten hoe transacties werken en mag dan ook geen verrassing zijn dat dit standaard transactie isolatieniveau van MySQL is. De InnoDB storage engine doet dit door gebruik te maken van multiversioning om een momentopname van de query resultaten op te slaan als de query voor de eerste keer wordt uitgevoerd en zal deze snapshot vervolgens hergebruiken voor alle volgende queries totdat de transactie is vastgelegd.

Het SERIALIZABLE isolatieniveau

Dit SERIALIZABLE isolatieniveau biedt de maximale afscherming tussen transacties door de gelijktijdige transacties te behandelen alsof ze opeenvolgend na elkaar uitgevoerd waren.

Hier voegt de eerste transactie een nieuwe vlucht toe aan de database, terwijl de tweede het totale aantal vluchten probeert te bekijken. Echter, omdat MySQL deze transacties serieel aan het uitvoeren is, zal de INSERT operatie in de eerste transactie de tabel vergrendelen totdat de transactie is voltooid. Dit zal de SELECT operatie in de tweede transactie laten wachten tot de vergrendeling wordt losgelaten voordat het een resultaat kan verkrijgen.

Deze “serialized” benadering is de meest veilige om transacties te behandelen: Het opeenvolgend vergrendelen en ontgrendelen van de tabel zorgt ervoor dat elke transactie enkel de gegevens ziet die daadwerkelijk in de database zijn vastgelegd, zonder de mogelijkheid van ‘dirty reads’ of ‘unrepeatable reads’.

Echter vergt dit zijn tol: MySQL zal een prestatie slag krijgen als elke transactie op dit isolatieniveau uitgevoerd wordt vanwege de grote hoeveelheid bronnen die nodig zijn om de verschillende transactionele vergrendelingen op elk moment te verwerken.

Het aanpassen van het transactie isolatieniveau

U kunt het transactie isolatieniveau veranderen met behulp van de TRANSACTIE ISOLATION LEVEL variabele. MSSQL/MySQL staan standaard op het REPEATABLE READ isolatieniveau. U kunt dit veranderen met de opdracht SET, zoals in het volgende voorbeeld:

MSSQL MySQL
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
Command(s) completed successfully
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
(1 row(s) affected)

U kunt op elk moment de huidige waarde van het TRANSACTION ISOLATION LEVEL variabele verkrijgen met een SELECT, zoals in het volgende voorbeeld:

MSSQL MySQL
DBCC useroptions;
(13 row(s) affected) 
DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.
SELECT @@tx_isolation ;
(1 row(s) returned)
Set Option Value
1 textsize 2147483647
2 language us_english
3 dateformat mdy
4 datefirst 7
5 lock_timeout -1
6 quoted_identifier SET
7 arithabort SET
8 ansi_null_dflt_on SET
9 ansi_warnings SET
10 ansi_padding SET
11 ansi_nulls SET
12 concat_null_yields_null SET
13 isolation level read committed
@@tx_isolation
1 REPEATABLE-READ
SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' 
END AS transaction_isolation_level 
FROM sys.dm_exec_sessions 
where session_id = @@SPID
(1 row(s) affected)
transaction_isolation_level
1 ReadCommitted

Standaard wordt de waarde van de TRANSACTION ISOLATION LEVEL variabele ingesteld op een per-sessie basis, maar in MySQL kun je het ook voor alle sessies globaal instellen door het toevoegen van het GLOBAL sleutelwoord aan de opdracht SET, zoals in het volgende voorbeeld:

MySQL
SET GLOBAL TRANSACTION ISOLATION LEVEL
READ COMMITTED ;
(1 row(s) affected)

U kunt ook het standaard transactie isolatieniveau bij het opstarten instellen met het speciale –transaction-isolation argument aan het mysqld server proces.

Opmerking: U moet het SUPER privilege bezitten om het transactie isolatieniveau in te kunnen stellen.

Pseudo-transacties

Tot nu toe, heb je transacties gezien in de context van InnoDB tabellen, wat voor MySQL de enige opslagengine is die ACID-onderworpen transacties ondersteund. De oudere MySQL tabeltypes, die nog steeds in vele MySQL in gebruik zijn, bieden geen ondersteuning voor transacties, maar MySQL stelt gebruikers in staat nog steeds een primitieve vorm van transacties te implementeren door het gebruik van tabelvergrendelingen. In deze paragraaf worden deze ‘Pseudo-transacties’ in meer detail bekeken, met het oog op het aanbieden’enkele algemene richtlijnen voor het uitvoeren van veilige transacties met de bedoeling om enkele algemene richtlijnen te bieden met betrekking tot het uitvoeren van veilige transacties op niet-transactionele tabellen.

MySQL ondersteunt een aantal verschillende tabeltypes, en de beschikbare vergrendelings-mechanismen verschillen van type tot type. Daarom is een degelijk begrip van de verschillende beschikbare niveau’s van vergrendeling van essentieel belang voor het implementeren van een pseudo-transactie omgeving op MySQL niet-transactionele tabellen.

  • Tabelvergendelingen: De gehele tabel wordt door een cliënt voor een specifieke manier van toegang vergrendeld. Afhankelijk van het type vergrendeling, zullen andere cliënten geen gegevens in de tabel mogen invoegen, en kan het zelfs beperkt worden tot enkel het lezen van gegevens.
  • Paginavergrendelingen: Een bepaald aantal rijen (een zogenaamde pagina) van de tabel wordt vergrendeld. De vergrendelde rijen zijn enkel beschikbaar voor de thread die de vergrendeling heeft geïnitieerd. Als een andere thread naar deze rijen wil schrijven, moet het wachten totdat de vergrendeling wordt opgeheven. De rijen in andere pagina’s, blijven beschikbaar voor gebruik.
  • Rijvergrendelingen: Vergrendelingen op rijniveau bieden een fijnmazigere controle over vergrendelingen dan bijvoorbeeld tabelvergrendelingen of vergrendelingen op paginaniveau. In dit geval worden alleen de rijen die door de thread worden gebruikt vergrendeld, alle andere rijen in de tabel blijven beschikbaar voor de andere threads. In multi-user omgevingen, zal de rijniveau vergrendeling het aantal conflicten tussen threads verminderen, waardoor meerdere gebruikers gelijktijdig naar dezelfde tabel kunnen lezen en zelfs schrijven. Deze flexibiliteit moet echter wel worden gewogen tegen het feit dat het van de drie niveaus van vergrendeling de hoogste overhead legt op de prestaties.

Het MyISAM tabeltype ondersteunt enkel vergrendeling op tabelniveau, die de prestatiewinst biedt ten opzichte van vergrendeling op rij- of pagina-niveau in situaties met een groter aantal leesopdrachten dan schrijfopdrachten. Het InnoDB tabeltype voert automatisch rij-niveau vergrendeling uit in transacties.

Tabelvergrendelingen als vervanging voor transacties

Omdat MyISAM (en andere oudere MySQL tabelformaten) geen InnoDB-stijl COMMIT en ROLLBACK syntax ondersteunen, wordt elke wijziging in de database onmiddellijk opgeslagen op de harde schijf. Zoals eerder is vermeld, zal dit in een single-user scenario  niet veel problemen geven; echter in een multi-user scenario kan dat problemen veroorzaken, omdat het niet langer mogelijk is om ’transactiebubbles’ te creëren die
wijzigingen die door een cliënt worden gemaakt geïsoleerd worden van de wijzigingen die door andere cliënten worden gemaakt. In een dergelijke situatie, is de enige manier om de consistentie van de gegevens te waarborgen die door verschillende cliënt sessies wordt gezien een brute-force aanpak: Voorkom dat andere gebruikers toegang tot de tabellen  krijgen die veranderd kunnen worden voor de duur van de wijziging (door ze te blokkeren), en hen enkel toegang te geven eenmaal de veranderingen zijn voltooid.

Vorige paragrafen van dit hoofdstuk hebben al gesproken over de InnoDB engine, die
ingebakken ondersteuning biedt voor rij- en pagina-niveau vergrendeling om veilig en gelijktijdig transacties uit te voeren. Het MyISAM tabel type, biedt geen ondersteuning voor deze fijnmazige vergrendelingsmethoden. In plaats daarvan dienen expliciete tabelvergrendelingen worden ingesteld om te voorkomen dat gelijktijdige transacties inbreuk maakt op elkaars ruimte.

Het volgende voorbeeld stelt een alleen-lezen vergrendeling in op de vluchttabel:

MySQL
LOCK TABLE flight READ;
(0 row(s) affected)
COUNT(FlightID)
1 0

Het vergrendelen van meerdere tabellen tegelijk is niet ongebruikelijk. Dit is eenvoudig te realiseren door het specificeren van een door komma’s gescheiden lijst van tabelnamen en
vergrendelingstypes na de opdracht LOCK TABLE, zoals in het volgende voorbeeld:

MySQL
LOCK TABLES flight READ, flightdep WRITE;
(0 row(s) returned)

Het vorige statement vergrendelt de vluchttabel in de leesmodus en de flightdep tabel in schrijf-modus.

Tabellen kunnen ontgrendeld worden met een enkele UNLOCK TABLES commando, zoals in het volgende voorbeeld:

MySQL
UNLOCK TABLES;
(0 row(s) affected)

Het is niet nodig om de tabelnamen te vernoemen die ontgrendeld moeten worden. MySQL ontgrendelt automatisch alle tabellen die voorheen waren geblokkeerd via LOCK TABLES.

  • Er zijn twee soorten tabelvergrendelingen:
    leesvergrendelingen
  • schrijfvergrendelingen

 

De leesvergrendeling

Een leesvergrendeling op een tabel impliceert dat de thread (client) die de vergrendeling instelt gegevens kan lezen uit deze tabel, evenals andere threads ook kunnen doen. Echter kan geen enkele thread de vergrendelde tabel wijzigen door het toevoegen, bijwerken of verwijderen van gegevens zolang het vergrendeld actief is.

Hier is een eenvoudig voorbeeld die je kan je proberen om te zien hoe leesvergrendelingen werken. Begin met het plaatsen van een leesvergrendeling op de vluchttabel:

MySQL
LOCK TABLE flight READ;
(0 row(s) affected)

Probeer dan van deze te lezen:

MySQL
SELECT FlightID
FROM flight
LIMIT 0,4;
(4 row(s) returned)
FlightID
1 724
2 725
3 687
4 688

Nu, probeer dan hiernaar te schrijven:

MySQL
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
Error Code: 1099. Table 'flight' was locked with a READ lock and can't be 
updated

MySQL verwerpt de INSERT omdat de tabel is vergrendeld in de alleen-lezen-modus.

Hoe zit het met andere threads (cliënten) die toegang zoeken tot dezelfde tabel ? Voor deze threads, zal een leestopdracht (SELECT‘s) zonder probleem werken.

Echter het schrijven (INSERT‘s, UPDATE’s of DELETE‘s) zal leiden dat de initiërende thread stopt en zal wachten tot de vergrendeling wordt vrijgegeven voordat het verder zal gaan. Dus pas nadat de vergrendelings-thread UNLOCK TABLES commando uitvoert en zijn vergrendelingen opheft zal de volgende thread in staat zijn om door te gaan met zijn schrijfopdracht.

 

De schrijfvergrendeling

Een schrijfvergrendeling op een tafel impliceert dat de thread die de vergrendeling instelt, de gegevens in de tabel kan wijzigen, maar dat andere threads noch naar de tabel kunnen lezen of schrijven voor de duur van het vergrendeling.

Hier is een eenvoudig voorbeeld dat illustreert hoe schrijfvergrendelingen werken. Begin met het plaatsen van een schrijfvergrendeling op de vluchttabel:

MySQL (Session #1)
LOCK TABLE flight WRITE;
(0 row(s) affected)

Probeer vervolgens hiervan te lezen:

MySQL (Session #1)
SELECT FlightID
FROM flight
LIMIT 0,4;
(4 row(s) returned)
FlightID
1 724
2 725
3 687
4 688

Omdat er een schrijfvergrendeling op de tabel  zit, zouden schrijfopdrachten zonder probleem kunnen plaatsvinden:

MySQL (Session #1)
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)

Hoe zit dit nu met andere MySQL sessies ? Open een nieuwe cliënt sessie en probeer uit dezelfde tabel te lezen, terwijl de schrijfvergrendeling nog steeds actief is:

MySQL (Session #2)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
Running...

De MySQL client zal nu stoppen en wachten tot de eerste sessie zijn vergrendelingen loslaat voordat het de vorige opdracht kan uitvoeren.

Zodra de eerste sessie een UNLOCK TABLES commando uitgeeft, zal de SELECT commando dat aangeroepen is in de tweede sessie worden aanvaard voor verwerking, omdat de tabel niet langer geblokkeerd.

MySQL (Session #1)
UNLOCK TABLES;
(1 row(s) affected)

 

MySQL (Session #2)
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(4 row(s) returned)
FlightID
1 724
2 725
3 687
4 688

Merk op uit de uitvoer de tijd die nodig is om een eenvoudige SELECT-opdracht uit te voeren: Dit omvat de wachttijd die nodig is totdat de tabelvergrendeling worden vrijgegeven. Dit moet een van de belangrijkste nadelen illustreren van tabelvergrendelingen: Als een thread zijn vergrendelingen nooit vrijgeeft, zullen alle andere threads die toegang proberen te zoeken tot de vergrendelde tabellen blijven wachten op de ontgrendeling, wat leidt tot ernstige verslechtering van de prestaties in het geheel.

Welk type vergrendeling heeft een hogere prioriteit?

In situaties waarbij zowel lees- en schrijfvergrendelingen mee gepaard gaan, zal MySQL aan schrijfvergrendelingen een hogere prioriteit toekennen om ervoor te zorgen dat wijzigingen aan de tabel zo spoedig mogelijk wordt opgeslagen op de harde schijf. Dit vermindert het risico op dat wijzigingen verloren zouden gaan in het geval van een schijfcrash of een systeemfout.

Implementeren van Pseudo-transacties met tabelvergrendeling

Deze sectie zal nu een transactie illustreren door het gebruik van tabelvergrendelingen door het herschrijven van een van de eerdere voorbeelden transactionele vergrendelingen en MyISAM tabellen. In een eerder voorbeeld, bevatten de stappen, het creëren van een record voor de vlucht, het definiëren van de vlucht, de dag en tijd van vertrek en het definiëren van de klasse en de zitplaats van de vlucht.

Omdat elk van de drie betrokken tabellen worden gewijzigd wanneer een nieuwe vlucht wordt toegevoegd, moeten zij worden vergrendeld in schrijfmodus zodat andere threads niet interfereren met de transactie.

MySQL
LOCK TABLES 
  flight WRITE, flightdep WRITE, flightclass WRITE;
(0 row(s) affected)

Zoals eerder uitgelegd, houdt de schrijf-modus in dat andere threads niet kunnen lezen of kunnen schrijven naar de vergrendelde tabellen zolang het systeem vergrendeld is. Daarom moet de transactie kort maar krachtig zijn om vertragingen in de verzoeken om gegevens van deze tabellen te voorkomen.

MySQL
INSERT INTO flight (FlightID, RouteID, AircraftID)
VALUES (834, 1061, 3469);
(1 row(s) affected)
SAVEPOINT flight1;
(0 row(s) affected)
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (834, 4, '16:00');
(1 row(s) affected)

Controleer of de gegevens correct zijn ingevoerd met een SELECT:

MySQL
SELECT COUNT(FlightID)
FROM flight
WHERE FlightID=834;
(1 row(s) returned)
COUNT(FlightID)
1 1

Ontgrendel de tabellen, en je bent klaar!

MySQL
UNLOCK TABLES;
(1 row(s) affected)

Totdat de tabellen zijn ontgrendeld, zullen alle andere threads die toegang proberen te krijgen tot de drie vergrendelde tabellen gedwongen worden om te wachten. De finesse van de transactionele aanpak ontbreekt hier, waarbij pagina- en rij-niveau vergrendelingen andere cliënten toestaan om met de gegeven te werken, zelfs tijdens de duur van een transactie.

Dat gezegd hebbende, echter helpen tabelvergrendelingen om wijzigingen in verschillende cliënt-sessies van elkaar te isoleren (gebruikt enigszins een ietwat primitieve manier) en daarbij gebruikers helpen die vastzitten aan de oudere, niet-transactionele tabel types met het implementeren van een ‘bijna-transactionele’ omgeving voor hun toepassing.