Transacties

Uit Yapf

Inleiding

Het woord "transactie" betekenthandeling en binnen databases staat het voor het uitvoeren van één of meer queries die nodig zijn om een taak in de database te volbrengen. Zo'n taak kan zijn het toevoegen van een bericht in een forum of het bijwerken van een score in een spel. Databases hebben en speciale manier om deze handelingen te kunnen verrichten en dat heet transcations.

Isolatie, Isolation

Het probleem dat zich voordoet wanneer één handeling uit meerdere queries bestaat is dat elke query een deel van de handeling uitvoert en de handeling dus pas compleet is wanneer de laatste query is gedraaid. Wanneer een ander proces de data opvraagt terwijl de handeling nog wordt uitgevoerd dan ziet dat proces halfbakken gegevens, met allerlei nare gevolgen van dien. Denk aan een prijsupdate in een winkel, als je vier groepen prijzen bijwerkt met vier queries dan kan een winkelwagenscript tijdens die update voor de ene groep de oude prijs doorkrijgen en voor de andere groep de nieuwe, en wanneer hij het totaal opvraagt is inmiddels de oude groep ook verandert en klopt er helemaal niets meer van.

Om dit te voorkomen zijn transactions bedacht. Met een transaction vertel je de database dat de veranderingen die de queries die je binnen die transaction uitvoert moeten worden verstopt voor de buitenwereld. In vaktermen heet dat 'isolation', de records die je binnen een transactie bewerkt worden tijdelijk geisoleerd van de buitenwereld zodat de buitenwereld de veranderingen niet kan zien tot de transactie klaar is.

Isolatieniveaus, Isolation levels

De isolation kan op verschillende manieren gebeuren, al zul je in de praktijk zelden iets anders willen dan de standaard instelling.

Read committed

Dit is voor veel databases het standaard isolation level. Data die binnen een open transactie is veranderd is niet zichtbaar voor queries die niet in die open transactie worden uitgevoerd. Oftewel, veranderingen die binnen een transactie worden gedaan zijn niet zichtbaar buiten de transactie. Pas wanneer de transactie met COMMIT wordt afgesloten worden de veranderingen zichtaar voor iedereen.

Dirty Read

Een dirty-read is het lezen nog niet gecommitte data. Veranderingen die binnen een transactie worden gedaan zijn nu ook voor de COMMIT al zichtbaar voor queries van buiten de transactie.

Dit wil je in principe dus nooit doen omdat je data ziet die nog niet compleet is en zelfs nog geROLLBACKt kan worden.

Q&A: Waarom bestaat deze mogelijkheid dan? Een transactie kan records locken voorkomen dat anderen het recod kunnen aanspreken tot de transactie klaar is. Als het niet echt van belang is dat de data 100% uptodate is dan kun je met dirty-read de data al gaan lezen voordat de transactie klaar is. Dat scheelt simpelweg wat tijd.

Maar nogmaals, dit gebruik je in het dagelijks leven niet.

Repeatable Read

De naam zegt het al, herhaalbaar lezen alle records die door de queries binnen de transaction worden gebruik voor lees operaties worden gelocked. Hierdoor kan binnen de transaction een select query meerdere keren gedraaid worden zonder dat de data in de tussentijd door andere queries veranderd kan worden.

Dit isolation level geeft veel zekerheid, maar is ook trager omdat er erg veel lockes gemaakt kunnen worden en alle andere queries die gelockte records willen lezen moeten wachten tot het lock is opgeheven.

Serializable

Hierbij ziet een transaction alleen die data die gecommit was voordat de eerste query van deze transaction werd uitgevoerd. Veranderingen die worden gecommit tijdens deze transaction wordt dus niet gezien. Dit lijkt een beetje op Repeatable-Read, met als verschil dat Repeatable-Read de buitenwereld verbied records te veranderen terwijl Serializable de veranderingen gewoon niet ziet. Hierbij wordt dus als het ware een kopie van de oude data bewaard en die blijf je terugkrijgen tot de transactie klaar is.

Hoe flikt een database die isolation?

Op het moment dat je binnen een transaction een record verandert (insert/update/delete) wordt het 'oude' record (als dat bestond) in een buffer geplaatst. Daarna wordt het nieuwe record in de tabel veranderd/ingevoerd zoals dat zonder transaction ook zou gebeuren. De rest van de database gebruikers worden nu naar die buffer verwezen ipv naar de echte records. Zij zien dus nog steeds de oude data zoals het was voordat je de transcation begin, en zo is jouw transaction 'geisoleerd' van de rest van de database. Pas als jij in jouw transaction een 'COMMIT' geeft worden de gebufferde records weggegooid en wordt er weer verwezen naar de echte records.

Als je een ROLLBACK geeft dan worden de gebufferde records weer teruggezet in de database en is alles weer bij het oude.

Q: Waarom worden de oude records in een buffer gezet en niet de nieuwe, bij rollback is het toch sneller om een buffer te legen dan om de echte records terug te veranderen?

Klopt, buffers weggooien is sneller, maar uit de praktijk blijkt dat 90% van alle transactions eindigt met een COMMIT en 10% eindigt met ROLLBACK. Je hebt dus veel meer aan een snelle COMMIT dan aan een snelle ROLLBACK.

Overigens verschilt dit per database, sommigen geloven heilig in de snelheid van COMMIT, anderen in ROLLBACK. Sommige dabases bufferen de oude records, anderen bufferen de nieuwe.

Q: En wat gebeurt er als de database crasht voordat er COMMIT of ROLLBACK wordt gegeven?

Intern wordt door de database gelogd of een transaction al een COMMIT had gekregen of nog niet. Dat heuglijke feit wordt door de database pas in het log geschreven nadat de transaction geverifieerd op disk is bijgewerkt. Dus _als_ in het log staat dat de transaction compleet is dan _is_ hij ook compleet.

Op het moment dat de database wordt opgestart zal de database in zijn log controleren of er transactions waren die voor het afsluiten van de database nog geen COMMIT of ROLLBACK hadden gekregen. Die transactions worden dan door de database als mislukt beschouwd (bij het opstarten is er geen enkele verbinding met een client, dus er kan ook geen COMMIT meer volgen vannaf een applicatie) en de database zal de transactions een ROLLBACK geven. Alle niet-afgemaakte transactions worden dus bij een crash teruggerold.

Zo blijft de integriteit van je database bewaard, ook bij een crash.

Transactions in PostgreSQL

7.x

PostgreSQL 7 kent momenteel alleen de 'READ COMMITTED' en 'SERIALIZABLE' levels, maar in de praktijk zul je merken dat 'READ COMMITED' (de default) vrijwel altijd genoeg is.

De implementatie van een transcation is erg eenvoudig:

-- begin een transaction:
BEGIN;

-- Sloop alle records uit een tabel:
DELETE FROM tabel;

-- Bevestig: geen records meer in de tabel:
SELECT COUNT(1) FORM tabel;

-- rol de transactie terug want de records moeten helemaal niet gewist:
ROLLBACK;

-- Bevestig: alle record zijn weer terug:
SELECT COUNT(1) FORM tabel;
[/CODE]

8.x

Met ingang van PostgreSQL 8 is het ook mogelijk om geneste transactions te gebruiken. In PgSQL heten de geneste transactions 'savepoints'. Het werkt eenvoudig; binnen een transaction kun je op elk willekeurig moment een 'savepoint' definieren. Daarna kun je op elk willekeurig moment een rollback uitvoeren tot aan elk willekeurig savepoint.

Noot: je kunt / hoeft niet voor elk savepoint een commit uitvoeren. Als je voor een savepoint geen rollback doet dan wordt hij aan het einde van de hele transaction ofwel gecommmit ofwel gerollbackt tegelijk met de 'buitenste' transacion.

voorbeeld (gejat uit handleiding):

BEGIN;
-- Haal 100 van de rekening van 'Alice'
UPDATE accounts SET balance = balance - 100.00 WHERE name =
'Alice';

-- Maak een savepoint zodat we kunnen rollbacken tot dit punt
SAVEPOINT my_savepoint;

-- Voeg 100 toe aan de rekening van 'Bob'
UPDATE accounts SET balance = balance + 100.00 WHERE name =
'Bob';

-- Oeps, dat moest niet Bob zijn maar 'Wally'
-- Rollback tot het savepoint van voordat we 'Bob' updaten
ROLLBACK TO my_savepoint;

-- En voeg nu 100 toe bij de rekening van de juiste persoon
UPDATE accounts SET balance = balance + 100.00 WHERE name =
'Wally';

-- Alles ok, commit de hele bups.
COMMIT;

De handeling die tussen het setten van het savepoint en de 'ROLLBACK TO' staat wordt nu gerollbackt, terwijl alles wat daarbuiten stond wel gewoon wordt gecommit.

Een voorbeeld in SQL (zonder PHP)

Stel ik heb een database met een tabel:

CREATE TABLE producten (
naam VARCHAR(255),
id INT4,
prijs FLOAT4
);

Nu ga ik via PgSQL en SSH, dus de commandline interface van PgSQL, met twee verbindingen tegelijk de database in, verbinding A en verbinding B.

Via verbinding A wil ik een nieuw product in gaan voeren, dus ik begin een transaction met "BEGIN".

De prompt van de commandline interface is "test=#" omdat ik in de 'test' database zit. Alles na deze prompt is ingetikt, alles op losse regels komt van de database terug.

test=# BEGIN;
BEGIN
test=# SELECT * FROM producten;
naam | id | prijs
------+----+-------
(0 rows)

Volgens verbinding A is de tabel dus leeg.

Via Verbinding B ga ik nu controleren wat de buitenwereld te zien krijgt:

test=# SELECT * FROM producten;
naam | id | prijs
------+----+-------
(0 rows)

Ook hier is de tabel nog leeg.

Nu ga ik via A een record invoeren. Ik zit nog in de transaction!

test=# INSERT INTO producten (id,
naam, prijs) VALUES (1,
'kapzaag', 14.60);
INSERT 1582973 1

Het getal achter INSERT is het OID, dat kan verschillen. De '1' geeft aan dat er 1 record is ingevoerd.

Even controleren via verbinding A:

test=# SELECT * FROM producten;
naam | id | prijs
---------+----+-------
kapzaag | 1 | 14.6
(1 row)

Volgens verbinding A bestaat het record.

Maar wat ziet verbinding B?

test=# SELECT * FROM producten;
naam | id | prijs
------+----+-------
(0 rows)

Sapperdeflap [1], volgens verbinding B is de tabel nog steeds leeg.

En dat komt uiteraard omdat verbinding A nog in een transaction zit en nog geen commit heeft gegeven.

Dus doen we dat even op verbinding A:

test=# COMMIT;
COMMIT

En als ik dan op verbinding B kijk of er iets in de tabel zit:

test=# SELECT * FROM producten;
naam | id | prijs
---------+----+-------
kapzaag | 1 | 14.6
(1 row)

Daar is het record.

Voordat de COMMIT op verbinding A werd gegeven kon dus niemand behalve verbinding A zien dat het record bestond.

Transactions met PHP

Om transactions te kunnen gebruiken moet allereerst natuurlijk je database transactionjs ondersteunen. MySQL is praktisch de enige database die het standaard niet kan, de rest kan het vrijwel allemaal out-=of-the-box.

Als je met MYSQL transactions wilt gebruiken dan moet je in je database tabellen maken van het InnoDB type. Meer informatie hierover vind je in je mysql handleiding: http://www.mysql.com/doc/en/Table_types.html

Om transactions daadwerkelijk te gebruiken hoef je alleen de woorden 'BEGIN', 'COMMIT' en 'ROLLBACK' als queries te laten uitvoeren. Let wel, deze moeten als aparte queries worden uitgevoerd, ze mogen geen deel uitmaken van andere queries.

Het principe is in PHP dan:

<?php
mysql_query('BEGIN');
mysql_query('DELETE FROM tabel');
mysql_query('ROLLBACK');
?>

Je kunt dan in PHP controleren of alles goed is gegeaan en op basis daarvan en COMMIT of ROLLBACK geven:

<?php
$bAllesOk = true;
if (!mysql_query('BEGIN'))
{
  $bAllesOk = false;
}
else
{
  if (!mysql_query('DELETE FROM tabel'))
  {
    $bAllesOk = false;
  }
 
  if ($bAllesOk == true)
  {
    mysql_query('COMMIT');
  }
  else
  {
    mysql_query('ROLLBACK');
  }
}
?>

Let ook op dat je moet controleren of het beginnen van de transaction wel gelukt is, anders kan de hele operatie niet doorgaan.