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 saved

Change and check

Replace the second key with a different one. The commit should now succeed and the final count should become two.