Transacties met mysqli: commit() en rollback()
Transacties klinken de doorgewinterde databasegebruiker waarschijnlijk zeer bekend in de oren, maar niet iedereen zal weten wat je met dit principe kunt. Simpel gezegd heb je bij transacties in een database de mogelijkheid om een reeks queries uit te voeren zonder dat je eventuele aanpassingen definitief doorvoert. Aan het einde van deze reeks kun je COMMIT gebruiken om de wijzigingen definitief door te voeren of ROLLBACK om alle wijzigingen terug te draaien.
Transacties kun je volledig verwerken in je sql query, maar de mysqli extensie biedt daar een andere manier voor. Voor het gebruik van deze methode zul je de InnoDB engine van MySQL moeten gebruiken. De andere engines zoals MyISAM ondersteunen deze functionaliteit niet.
In het volgende voorbeeld zal ik een transactie gebruiken om verschillende records in een database in te voegen.
Voorbeeld 14: Transactie met mysqli
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Piet')");
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Karel')");
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Kees')");
$mysqli->commit();
?>
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Piet')");
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Karel')");
$mysqli->query("INSERT INTO tabel (naam) VALUES ('Kees')");
$mysqli->commit();
?>
Dit is de standaard manier waarop een transactie opgebouwd wordt. Allereerst zetten we met behulp van de autocommit() methode de waarde van autocommit op FALSE. Dit zorgt ervoor dat queries nog niet definities uitgevoerd worden in de database. Vervolgens voeren we de betreffende queries uit en gebruiken we de commit() methode om de aanpassingen definitief door te voeren.
Nu is dit echt het meest basale voorbeeld dat geen enkel voordeel heeft ten opzichte van het normaal uitvoeren van deze queries. Wat hebben we dan eigenlijk aan transacties en hoe kunnen we daar ons voordeel mee doen?
Allereerst kun je transacties gebruiken om een reeks queries ongedaan te maken zodra er een query niet correct uitgevoerd wordt.
Voorbeeld 15: Ongedaan maken van een reeks queries
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?php
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
// Queries definieren
$queries[] = "INSERT INTO tabel (naam) VALUES ('Piet')";
$queries[] = "INSERT INTO tabel (naam) VALUES ('Karel')";
$queries[] = "INSERT INTO tabel (naam) ('Kees')";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Voer de queries uit en controleer of ze gelukt zijn
foreach($queries as $query)
{
if(!$mysqli->query($query))
{
$errors[$query] = $mysqli->error;
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
foreach($errors as $sql => $error)
{
echo '<pre>'.$error.' in SQL: '.$sql.'</pre>';
}
}
else
{
$mysqli->commit();
}
?>
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
// Queries definieren
$queries[] = "INSERT INTO tabel (naam) VALUES ('Piet')";
$queries[] = "INSERT INTO tabel (naam) VALUES ('Karel')";
$queries[] = "INSERT INTO tabel (naam) ('Kees')";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Voer de queries uit en controleer of ze gelukt zijn
foreach($queries as $query)
{
if(!$mysqli->query($query))
{
$errors[$query] = $mysqli->error;
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
foreach($errors as $sql => $error)
{
echo '<pre>'.$error.' in SQL: '.$sql.'</pre>';
}
}
else
{
$mysqli->commit();
}
?>
De reeks queries die we achter elkaar willen uitvoeren zetten we in de array $queries. We zetten autocommit wederom op FALSE en gebruiken nu een foreach loop om de queries uit te voeren. Als er nu een query mislukt, wordt er een foutmelding toegevoegd aan de $errors array.
Door vervolgens te controleren of de $errors array bestaat, weten we direct of er fouten opgetreden zijn. Zo ja, dan gebruiken we de rollback() methode om alle queries ongedaan te maken en geven we de opgetreden fouten weer. Zijn er geen fouten opgetreden, dan gebruiken we de commit() methode om de aanpassingen definitief door te voeren.
In dit geval zal er niets in onze tabel veranderen omdat de laatste query zeker zal mislukken.
Voorbeeld 16: Werking ROLLBACK
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$verwijder = "DELECT FROM klanten";
$selecteer = "SELECT COUNT(*) AS aantal FROM klanten";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Verwijder alle gegevens uit de klanten tabel
$mysqli->query($verwijder);
// Kijk hoeveel records er in de klanten tabel staan
$result = $mysqli->query($selecteer);
$row = $result->fetch_assoc();
echo 'Er staan '.$row['aantal'].' records in de klanten tabel <br>';
// ROLLBACK
$mysqli->rollback();
// Kijk wederom hoeveel records er in de klanten tabel staan
$result = $mysqli->query($selecteer);
$row = $result->fetch_assoc();
echo 'Er staan '.$row['aantal'].' records in de klanten tabel <br>';
?>
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$verwijder = "DELECT FROM klanten";
$selecteer = "SELECT COUNT(*) AS aantal FROM klanten";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Verwijder alle gegevens uit de klanten tabel
$mysqli->query($verwijder);
// Kijk hoeveel records er in de klanten tabel staan
$result = $mysqli->query($selecteer);
$row = $result->fetch_assoc();
echo 'Er staan '.$row['aantal'].' records in de klanten tabel <br>';
// ROLLBACK
$mysqli->rollback();
// Kijk wederom hoeveel records er in de klanten tabel staan
$result = $mysqli->query($selecteer);
$row = $result->fetch_assoc();
echo 'Er staan '.$row['aantal'].' records in de klanten tabel <br>';
?>
In dit scriptje laat ik zien wat de rollback() methode nou precies doet. Allereerst maar eens de output van het script:
Code (php)
1
2
2
Er staan 0 records in de klanten tabel
Er staan 3 records in de klanten tabel
Er staan 3 records in de klanten tabel
Nadat we eerst alle records uit de klanten tabel verwijderd hebben, kijken we hoeveel records nog aanwezig zijn. Zoals het eerste resultaat ook aangeeft is dat er geen een meer. Echter na het uitvoeren van de rollback() methode staan er weer records in de klanten tabel. Het blijkt dus dat onze eerder uitgevoerde DELETE query ongedaan is gemaakt.
Let op dat ik dit script helemaal geen foutafhandeling toegepast heb. Normaal gesproken mag dat natuurlijk niet ontbreken!
Om het gebruik van transacties verder te verduidelijken, zal ik een voorbeeld uit de boeken geven. Dit voorbeeld betreft een geldtransactie tussen twee bankrekeningen van respectievelijk persoon A en B.
Het gaat hier om een overschrijving van 1000 euro van de rekening van persoon A naar die van persoon B. De voorwaarde waarop dit uitgevoerd kan worden is dat persoon A genoeg geld op zijn rekening heeft.
Voorbeeld 17: Overschrijving tussen twee rekeningen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<?php
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
// Queries
$queries[] = "UPDATE rekeningen SET saldo = saldo - 1000 WHERE klant = 'A'";
$queries[] = "UPDATE rekeningen SET saldo = saldo + 1000 WHERE klant = 'B'";
// Controle query
$controle = "SELECT saldo FROM rekeningen WHERE klant = 'A'";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Uitvoeren van queries en controleren of ze gelukt zijn
foreach($queries as $query)
{
if(!$result = $mysqli->query($query))
{
$errors[$query] = $mysqli->error;
}
if($mysqli->affected_rows == 0)
{
$errors[$query] = 'Er zijn geen rijen gewijzigd in SQL';
}
}
// Uitvoeren controle query en controleren waarde
if(!$result = $mysqli->query($controle))
{
$errors[$controle] = $mysqli->error;
}
else
{
$row = $result->fetch_assoc();
if($row['saldo'] < 0)
{
$errors[$controle] = 'Het saldo op een rekening is niet toereikend.';
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
foreach($errors as $sql => $error)
{
echo '<pre>'.$error.' SQL: '.$sql.'</pre>';
}
}
else
{
$mysqli->commit();
}
?>
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
// Queries
$queries[] = "UPDATE rekeningen SET saldo = saldo - 1000 WHERE klant = 'A'";
$queries[] = "UPDATE rekeningen SET saldo = saldo + 1000 WHERE klant = 'B'";
// Controle query
$controle = "SELECT saldo FROM rekeningen WHERE klant = 'A'";
// Zet autocommit op FALSE
$mysqli->autocommit(FALSE);
// Uitvoeren van queries en controleren of ze gelukt zijn
foreach($queries as $query)
{
if(!$result = $mysqli->query($query))
{
$errors[$query] = $mysqli->error;
}
if($mysqli->affected_rows == 0)
{
$errors[$query] = 'Er zijn geen rijen gewijzigd in SQL';
}
}
// Uitvoeren controle query en controleren waarde
if(!$result = $mysqli->query($controle))
{
$errors[$controle] = $mysqli->error;
}
else
{
$row = $result->fetch_assoc();
if($row['saldo'] < 0)
{
$errors[$controle] = 'Het saldo op een rekening is niet toereikend.';
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
foreach($errors as $sql => $error)
{
echo '<pre>'.$error.' SQL: '.$sql.'</pre>';
}
}
else
{
$mysqli->commit();
}
?>
De UPDATE queries zorgen ervoor dat er 1000 van de rekening van persoon A gehaald wordt en bij het saldo van persoon B opgeteld wordt. De controle query haalt ter controle het nieuwe saldo persoon A op, dit mag immers niet negatief zijn.
Allereerst voeren we de update queries uit, kijken we of ze gelukt zijn en of er daadwerkelijk gegevens in de database aangepast zijn. Vervolgens voeren we de controle query uit en controleren we het saldo van persoon A.
Als er ergens in deze transactie fouten opgetreden zijn, draaien we alles terug met rollback() en geven we de foutmeldingen weer. Is het goed gegaan, dan maken we de overboeking definitief met de commit() methode.
Het gebruik van een transactie is hier een vereiste omdat het totale saldo in de tabel natuurlijk gelijk moet blijven. Er kan niet zomaar geld aangemaakt of vernietigd worden. Het grote voordeel is daarnaast dat tijdens de transactie het voor andere gebruikers niet mogelijk is om deze gegevens te wijzigen. Het kan dus nooit zo zijn dat twee gebruikers precies tegelijkertijd dezelfde gegevens wijzigen waardoor de data mogelijk corrupt kan raken.
« vorige pagina | volgende pagina »
Inhoudsopgave
- Inleiding
- Transacties met mysqli: commit() en rollback()
- Proceduraal vs. Object georienteerd
- Uitbreiden van de mysqli klasse
- Object georienteerde interface van mysqli
- Slotwoord en referenties
- Meerdere queries tegelijk uitvoeren
- Prepared statements