Transacties en rollbacks

Kennisclip

Transacties

Wat is een transactie?

Bij het woord "transactie" denk je waarschijnlijk aan een uitwisseling van geld en/of goederen. Belangrijk bij zo'n uitwisseling is dat ze volledig wordt afgewerkt, of volledig wordt geannuleerd. Soms is dit ook van belang in een SQL-script.

Een voorbeeld: Je gaat naar de dokter en betaalt met Bancontact. Op dat moment gaat het bedrag op jouw rekening naar beneden en gaat het bedrag op de rekening van de dokter naar omhoog. Dit kan misschien voorgesteld zijn met UPDATE-statements. Bijvoorbeeld iets als dit:

update Rekeningen set Totaal = Totaal - Bedrag where Rekeningen.Eigenaars_Id = <id-patient>;
update Rekeningen set Totaal = Totaal + Bedrag where Rekeningen.Eigenaars_Id = <id-arts>;

Het is absoluut ontoelaatbaar dat slechts één van deze instructies uitvoert en de andere niet. Dat zou betekenen dat iemands geld zomaar verdwijnt of zomaar aangroeit. Maar, als je deze instructies zomaar in een script plakt en de tweede loopt fout (bijvoorbeeld omwille van een storing, of omdat de rekening van de dokter niet meer actief is, of tal van andere mogelijke redenen), is dat wel wat gebeurt: als een instructie fout loopt, is het effect van de voorgaande instructies nog steeds zichtbaar.

Om dit te vermijden, geven we aan dat de instructies "alles of niets" zijn. De technische term hiervoor is atomair, omdat men vroeger dacht dat atomen ondeelbaar waren. Dat aangeven doen we door de instructies aan te duiden als een transactie met de tekst START TRANSACTION. Na de laatste instructie zetten we dan COMMIT om het einde van de transactie aan te geven. Via de instructie ROLLBACK kan je voorlopige wijzigingen ongedaan maken. Deze instructie voer je normaal uit in een handler.

We behandelen in deze cursus niet het "isolation level" van transacties. Dit geeft aan hoe je moet omspringen met transacties die tegelijkertijd lopen en dezelfde data gebruiken. Dit is complexer dan gewoon "alles of niets doen". Als je denkt dat dit mogelijk is in jouw systeem, moet je het juiste isolatieniveau instellen. Meer uitleg over wat fout kan lopen en welk niveau je best kiest, vind je hier. Dit is geen vereiste leerstof.

Voorbeeld 1 (hoe het moet)

Onderstaande code start uitdrukkelijk een transactie en de handler bevat een ROLLBACK. Tijdens de transactie doet zich een fout voor die afgehandeld kan worden door de handler:

use aptunes;
delimiter $$
CREATE PROCEDURE RollbackDemo1()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: stored procdure is beëindigd en alle wijzigingen zijn ongedaan gemaakt.';
    END;
    start transaction;
    insert into Genres (Naam)
    values
    ('Metal');
    signal sqlstate '45000';
    commit;
END$$

Voer de code uit om de procedure te creëren. Kijk welke genres momenteel in je database zitten. Voer vervolgens een CALL van deze procedure uit. Het resultaat? Er is geen nieuwe genre toegevoegd aan het systeem. De INSERT is wel uitgevoerd, maar alleen op een voorlopige wijze. De COMMIT is niet bereikt en er heeft zich een ROLLBACK voorgedaan, dus de wijziging is niet definitief gemaakt.

Voorbeeld 2 (hoe het niet moet)

use aptunes;
delimiter $$
CREATE PROCEDURE RollbackDemo2()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: stored procdure is beëindigd en alle wijzigingen zijn ongedaan gemaakt.';
    END;
    insert into Genres (Naam)
    values
    ('Metal');
    signal sqlstate '45000';
END$$

Als je deze procedure definieert en oproept, zie je dat het nieuwe genre wel is toegevoegd. Dat komt omdat je geen transactie hebt gestart. Als je dat niet doet, is elk statement standaard definitief. Stored procedures vormen niet vanzelf een transactie.

Voorbeeld 3 (hoe het niet moet)

Verwijder eerst even zelf het nieuwe genre en gebruik dan volgende procedure:

use aptunes;
delimiter $$
CREATE PROCEDURE RollbackDemo3()
BEGIN
    insert into Genres (Naam)
    values
    ('Metal');
    signal sqlstate '45000';
END$$

Hier hebben we de handler weggelaten ten opzichte van het vorige voorbeeld. Omdat in het vorige voorbeeld de INSERT niet ongedaan werd gemaakt, gebeurt dat hier zeker niet. Er is niet eens een rollback (die in het vorige voorbeeld dus al niets deed). Wat wel anders is: hier is geen handler. Dus de fout wordt niet afgehandeld binnen de stored procedure en levert een "klassieke" fout in je MySQL Workbench.

Voorbeeld 4 (om voor op te letten)

Volgend voorbeeld is geen stored procedure:

use aptunes;
start transaction;
insert into Genres(Naam) values ('Chanson');
signal sqlstate '45000';
commit;

In MySQL hangt het effect hier af van de omgeving (bv. command line client, Workbench, nog andere omgevingen waarin je SQL-commando's kan intypen en uitvoeren). In sommige omgevingen zorgen fouten in transacties vanzelf voor een ROLLBACK en in sommige niet. Als je zeker wil zijn van het gewenste gedrag, programmeer het dan met behulp van een handler. Op mijn systeem blijft dit nieuwe genre in het systeem staan, maar bij jou zou het afwezig kunnen zijn! Meer informatie vind je hier.

Let wel op: hier kan je eigenlijk geen handler zetten want er is geen stored procedure. Maar voor een stored procedure gelden dezelfde regels.

We herhalen het nogmaals: in MySQL is de body van een stored procedure geen transactie. Als je een rollback wil kunnen doen, moet je dus START TRANSACTION schrijven en eindigen met een COMMIT.