Transacties

Op deze pagina:

Elke uitgevoerde SQL is een transactie. Transacties geven SQL een alles-of-niets megelijkheid bij het uitvoeren van aanpassingen in een database. Als een query bijvoorbeeld 500 records moet aanpassen en ergens halverwege er een storing optreedt waardoor het systeem crasht, dan blijkt er geen enkele aanpassing gedaan te zijn als het systeem weer opstart. Dit is met opzet, het is alles-of-niets. Transacties zorgen er voor dat query's in zijn geheel worden uitgevoerd, als het niet in zijn geheel kan, dan helemaal niet, zodat de query overnieuw gedaan kan worden zonder eerst te moeten gaan uitzoeken welk records wel of niet zijn aangepast.

Elke query is al een impliciete transactie. Je kunt ook meerdere query's bij elkaar in een expliciete transactie doen. Alle query's in de transactie worden dan als één geheel gezien en allemaal wel of allemaal niet uitgevoerd. Dit doe je door de query's in een BEGIN ... COMMIT blok te plaatsen.

Voorbeeld in psql:

--de array array_test bevat al een rij, 
--dezelfde rij nog eens toevoegen in 
--een expliciet transactieblok:
test=# BEGIN;
BEGIN
test=# INSERT INTO array_test VALUES (
test(# ARRAY [1,2,3,4,5],
test(# ARRAY [[1,2],[3,4]],
test(# ARRAY [[[1,2],[3,4]],[[5,6], [7,8]]]
test(# );
INSERT 0 1
test=# COMMIT;
COMMIT
test=# SELECT * FROM array_test;
    col1     |     col2      |             col3              
 -------------+---------------+-------------------------------
 {1,2,3,4,5} | {{1,2},{3,4}} | {{{1,2},{3,4}},{{5,6},{7,8}}}
 {1,2,3,4,5} | {{1,2},{3,4}} | {{{1,2},{3,4}},{{5,6},{7,8}}}
(2 rows)

test=# 

Als je een transactie bent begonnen, maar nog geen COMMIT hebt gedaan, dan kan je een ROLLBACK doen om de hele transactie ongedaan te maken (psql):

test=# BEGIN;
BEGIN
test=# INSERT INTO array_test VALUES (
test(# ARRAY [1,2,3,4,5],
test(# ARRAY [[1,2],[3,4]],
test(# ARRAY [[[1,2],[3,4]],[[5,6], [7,8]]]
test(# );
INSERT 0 1
test=# --COMMIT;
test=# ROLLBACK;
ROLLBACK
test=# 

Zolang een transactie nog geen COMMIT heeft gehad, is deze alleen zichtbaar voor de gebruiker die de transactie invoert. Pas na een COMMIT zijn de gevolgen van de transactie zichtbaar voor andere gebruikers.

Welke COMMITs zichtbaar zijn tijdens een transactie

Je ziet alleen transacties van anderen die een COMMIT hebben gehad. Het kan gebeuren dat je een transactie begint en dat ondertussen iemand een COMMIT uitvoert voordat jouw transactie is afgelopen. Als in jouw transactie 2 keer dezelfde SELECT wordt uitgevoerd, een keer voor en een keer na de COMMIT van de andere gebruiker, dan zal je twee verschillende resultaten krijgen op die zelfde SELECT. Dit komt omdat het default isolation level op READ COMMITTED staat, wat er voor zorgt dat je zodra er iets geCOMMIT wordt, je dat ook meteen ziet. Dit is aan te passen door het isolation level op Serializable te zetten. In psql:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SERIALIZABLE isolation voorkomt dat je COMMITs van anderen ziet nadat je eigen transactie met de eerste query is begonnen. Voor SELECT query's geeft dit een beeld van de database alsof deze is bevroren sinds het begin van je transactie. Voor UPDATE en DELETE statements forceert SERIALIZABLE het uitvoeren van deze query's achter elkaar, dus niet gelijktijdig. Indien twee tranacties dezelfde rij proberen aan te passen, dan krijgt één van de transacties een rollback.

Savepoint

Je kunt in een transactie een savepoint instellen zodat je als er nog geen commit is geweest een rollback tot aan dat savepoint kunt doen.Voorbeeld in psql:

test=# BEGIN;
BEGIN
test=# CREATE TABLE nieuw(n int);
CREATE TABLE
test=# INSERT INTO nieuw VALUES (1);
INSERT 0 1
test=# SAVEPOINT mijn_savepoint;
SAVEPOINT
test=# INSERT INTO nieuw VALUES (2);
INSERT 0 1
test=# INSERT INTO nieuw VALUES (3);
INSERT 0 1
test=# SELECT * FROM nieuw;
 n 
 ---
 1
 2
 3
(3 rows)

test=# ROLLBACK TO mijn_savepoint;
ROLLBACK
test=# SELECT * FROM nieuw;
 n 
 ---
 1
(1 row)

test=# COMMIT;
COMMIT
test=# 

Errors

Als je een foutmelding krijgt, dan wordt de hele transactie teruggedraaid. Als je een savepoint hebt ingesteld, dan wordt, als er na het savepoint een error optreedt, de transactie teruggedraaid tot aan dat savepoint.

Verwante artikelen