Transactions

The data access layer ran a single query safely. But a task often does not consist of a single query: money leaves one account and is added to another. If something goes wrong between the two writes, the first write stays and the second does not. The question is this: when a write is interrupted halfway, whose hands is the result left in?

The Databases course covered transactions and the ACID properties; there the question was which component atomicity comes from. That question is not asked again here. The deviation is applied and the result is observed: when the second write fails, does the balance total change, and if it does, through whose deviation? The decision order is the same as before: client, third party, environment; if none of them can change the result, the server. The party cell of this lesson is the server: once a transaction is open, the deviation can no longer change the result. As in every lesson, the core file is given verbatim.

<?php
// party.php — who can change this result?
declare(strict_types=1);

// The parties outside the code, in decision order: every request, every external call, every deployment.
const EXTERNAL_PARTIES = ['client', 'third party', 'environment'];

/** Computes the result once without deviation, then once with each external party's deviation. */
function probe(callable $result, array $deviations): array {
    $baseline = $result([]);
    $changed = [];
    foreach (EXTERNAL_PARTIES as $party) {
        if (array_key_exists($party, $deviations)) {
            $changed[$party] = $result([$party => $deviations[$party]]) !== $baseline;
        }
    }
    return $changed;
}

/** The first external party that can change the result; the server if none can. */
function party(array $changed): string {
    foreach (EXTERNAL_PARTIES as $p) {
        if ($changed[$p] ?? false) return $p;
    }
    return 'server';
}

function partyReport(string $name, callable $result, array $deviations): string {
    $changed = probe($result, $deviations);
    $lines = [];
    foreach ($changed as $p => $c) $lines[] = $p . ($c ? ' changed' : ' could not change');
    $owner = party($changed);
    echo "$name → $owner\n    ", implode(' · ', $lines), "\n";
    return $owner;
}

DA5. Balances are integers and the balance >= 0 constraint stays in the table; the two accounts a and b start with balances of one hundred and fifty, so the total is always one hundred fifty. DA6. The environment’s deviation is a constraint that the party performing the deployment places on the table: every update written to account b is rejected. This makes the second write fail the same way every time. DA7. The client sends only the amount to be transferred; its deviation is sending twenty-five instead of forty. DA8. The result is the sum of the two balances after the transfer attempt.

The Transfer Without a Transaction

A transaction is a wrapper that runs several writes as a single unit. In PDO it is three calls: beginTransaction() opens the transaction, commit() makes what was written final, and rollBack() undoes what was written since the transaction was opened. First look at the case without a transaction. The file below sets up the accounts and writes the transfer as two separate updates; when the $transactional flag is on, the same two updates are placed inside a transaction.

<?php
// accounts.php — two accounts and a transfer
declare(strict_types=1);

function setUpAccounts(?string $constraint = null): PDO {
    $pdo = new PDO('sqlite::memory:');
    $pdo->exec('CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))');
    $pdo->exec("INSERT INTO accounts VALUES ('a', 100), ('b', 50)");
    if ($constraint !== null) $pdo->exec($constraint);
    return $pdo;
}

function total(PDO $pdo): int {
    return (int) $pdo->query('SELECT SUM(balance) FROM accounts')->fetchColumn();
}

function updateBalance(PDO $pdo, string $name, int $delta): void {
    $pdo->prepare('UPDATE accounts SET balance = balance + :delta WHERE name = :name')
        ->execute(['delta' => $delta, 'name' => $name]);
}

function transfer(PDO $pdo, int $amount, bool $transactional): void {
    if ($transactional) $pdo->beginTransaction();
    try {
        updateBalance($pdo, 'a', -$amount);
        updateBalance($pdo, 'b', $amount);
        if ($transactional) $pdo->commit();
    } catch (PDOException $e) {
        if ($transactional) $pdo->rollBack();
    }
}

The deviation is a constraint that comes from the environment: every update that writes to account b is rejected with RAISE(ABORT). The first write subtracts the amount from account a, the second is rejected, and without a transaction the first write stays.

<?php
// without-transaction.php — the transfer without a transaction
declare(strict_types=1);
require 'party.php';
require 'accounts.php';

const B_CONSTRAINT = "CREATE TRIGGER b_lock BEFORE UPDATE ON accounts WHEN NEW.name = 'b'
               BEGIN SELECT RAISE(ABORT, 'rejected'); END";

function transferResult(array $d, bool $transactional): int {
    $pdo = setUpAccounts($d['environment'] ?? null);
    transfer($pdo, $d['client'] ?? 40, $transactional);
    return total($pdo);
}

partyReport('total, without transaction', fn(array $d) => transferResult($d, false), ['client' => 25, 'environment' => B_CONSTRAINT]);

total, without transaction → environment
    client could not change · environment changed

The baseline total is one hundred fifty. When the client changes the amount, the total still stays one hundred fifty, because the amount moves between the two accounts and does not affect the total. When the environment places the constraint, however, the first write stays, the second does not go through, and the total drops. The result belongs to the environment.

The Transfer With a Transaction

The same deviation, the same constraint, the same two updates; the only difference is the $transactional flag. When the second write is rejected, the catch block calls rollBack() and what was written since the beginning of the transaction is undone.

<?php
// with-transaction.php — the same transfer inside a transaction
declare(strict_types=1);
require 'party.php';
require 'accounts.php';

const B_CONSTRAINT = "CREATE TRIGGER b_lock BEFORE UPDATE ON accounts WHEN NEW.name = 'b'
               BEGIN SELECT RAISE(ABORT, 'rejected'); END";

function transferResult(array $d, bool $transactional): int {
    $pdo = setUpAccounts($d['environment'] ?? null);
    transfer($pdo, $d['client'] ?? 40, $transactional);
    return total($pdo);
}

partyReport('total, with transaction', fn(array $d) => transferResult($d, true), ['client' => 25, 'environment' => B_CONSTRAINT]);

total, with transaction → server
    client could not change · environment could not change

The environment still makes the second write fail, but the result no longer changes: the first write has also been undone and the total is one hundred fifty. Because no external party can change the result, the result passed to the server. The defense mechanism, that is the transaction, moved the result from the environment to the server. Here the strength of the transaction comes not from the code written itself but from the code placing its commit() and rollBack() calls in the right places.

The State of the Transaction

Whether a transaction is open is asked with inTransaction(). PDO does not nest transactions: calling beginTransaction() a second time inside an open transaction, and calling commit() or rollBack() when no transaction is open, both throw a PDOException. When a nested section needs to be made reversible, a savepoint takes this on; a savepoint is written as a query, and ROLLBACK TO undoes only what comes after that point.

<?php
// state.php — the state of the transaction, double begin and a savepoint
declare(strict_types=1);
require 'accounts.php';

$pdo = setUpAccounts();
echo 'initially open: ', var_export($pdo->inTransaction(), true), "\n";

try { $pdo->commit(); } catch (PDOException $e) { echo 'commit without transaction: ', get_class($e), "\n"; }

$pdo->beginTransaction();
try { $pdo->beginTransaction(); } catch (PDOException $e) { echo 'double begin: ', get_class($e), "\n"; }

updateBalance($pdo, 'a', -10);
$pdo->exec('SAVEPOINT half');
updateBalance($pdo, 'a', -30);
echo 'a before rollback to savepoint: ', $pdo->query("SELECT balance FROM accounts WHERE name = 'a'")->fetchColumn(), "\n";
$pdo->exec('ROLLBACK TO half');
echo 'a after rollback to savepoint: ', $pdo->query("SELECT balance FROM accounts WHERE name = 'a'")->fetchColumn(), "\n";
$pdo->commit();
echo 'committed, total: ', total($pdo), ', open: ', var_export($pdo->inTransaction(), true), "\n";

initially open: false
commit without transaction: PDOException
double begin: PDOException
a before rollback to savepoint: 60
a after rollback to savepoint: 90
committed, total: 140, open: false

The drop of thirty after the savepoint was undone, the drop of ten before it stayed, and commit() made only that one final. The total is one hundred forty, because the ten subtracted from account a was not added to account b. This block has no transfer with two writes; a one-way subtraction is written to show the behavior of the savepoint.

The Bounding Measurement

Opening a transaction is not the whole of the protection. Write the same transfer once more, this time without the rollBack() call: the catch block swallows the exception and the transaction stays open. What happens to the transaction when the connection closes cannot be observed in an in-memory database; the database disappears with the connection and a file database is not opened. What can be observed is the total read from the same connection while the transaction is still open.

<?php
// half.php — the transaction is open, rollBack is not called
declare(strict_types=1);
require 'party.php';
require 'accounts.php';

const B_CONSTRAINT = "CREATE TRIGGER b_lock BEFORE UPDATE ON accounts WHEN NEW.name = 'b'
               BEGIN SELECT RAISE(ABORT, 'rejected'); END";

function halfResult(array $d): int {
    $pdo = setUpAccounts($d['environment'] ?? null);
    $pdo->beginTransaction();
    try {
        updateBalance($pdo, 'a', -($d['client'] ?? 40));
        updateBalance($pdo, 'b', $d['client'] ?? 40);
        $pdo->commit();
    } catch (PDOException $e) {
        // exception caught but the rollback was forgotten
    }
    return total($pdo);
}

partyReport('total, no rollback', 'halfResult', ['client' => 25, 'environment' => B_CONSTRAINT]);

$pdo = setUpAccounts(B_CONSTRAINT);
$pdo->beginTransaction();
try { updateBalance($pdo, 'a', -40); updateBalance($pdo, 'b', 40); } catch (PDOException $e) {}
echo 'transaction still open: ', var_export($pdo->inTransaction(), true), "\n";

total, no rollback → environment
    client could not change · environment changed
transaction still open: true

Even with the transaction open, the environment’s deviation changed the total. When rollBack() is not called, a transaction undoes nothing for as long as it stays open; it only postpones the commit. PDO rolls back a transaction left open on its own when the script ends or when the connection closes, but until that moment the total read from the same connection is already reduced. The result was not moved to the server; it stayed with the environment. Opening a transaction and closing it correctly are not the same thing.

Summary

• A transaction runs several writes as a single unit with beginTransaction(), commit() and rollBack().
• In a transfer without a transaction, when the second write fails the first write stays and the balance total changes; this deviation comes from the environment.
• In the same transfer with a transaction, rollBack() also undoes the first write and the total is not affected by the deviation; in this lesson the party is the server.
• A transaction on which rollBack() is not called stays open and the result stays with the environment.
• Leaving the effect of a write half done hands the result over to one of the external parties.

Next Step

The transaction protected the consistency of the data, but where do the tables themselves come from? The schema is also the result of a write, and it may not be the same in every environment. The next lesson handles who determines the schema through migration management.