
Wikis & learning
Run a related set of writes in one transaction
A batch of related changes should either commit together or leave the database in its earlier state. A unique constraint gives the example a predictable way to encounter a failed insert.
A batch of related changes should either commit together or leave the database in its earlier state. A unique constraint gives the example a predictable way to encounter a failed insert.
Walkthrough
The transaction begins before the first write. Both executions reuse the prepared statement. When a duplicate is rejected, the catch block rolls back the transaction and the row count returns to its starting value.
PHP example
<?php
$db = new PDO('sqlite::memory:');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec('CREATE TABLE tags (name TEXT UNIQUE)');
$db->beginTransaction();
try {
$save = $db->prepare('INSERT INTO tags VALUES(?)');
foreach (['php', 'php'] as $name) { $save->execute([$name]); }
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
}
echo $db->query('SELECT COUNT(*) FROM tags')->fetchColumn(), ' saved';Expected output
0 savedChange and check
Replace the second key with a different one. The commit should now succeed and the final count should become two.
Discussion
Sign in to participate.



