PHP From ZeroModern PHP course

databases storage and caching

Transactions

A transaction groups related database changes so they either all commit or all roll back. Use one when partial completion would leave data dishonest.

Common examples include moving money or credits, creating an order with its items, registering a user with profile rows, reserving stock, and writing an audit record alongside the main change.

Basic Transaction

PHP example
<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)');
$pdo->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 200)');

$pdo->beginTransaction();

try {
    $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id')
        ->execute(['amount' => 500, 'id' => 1]);

    $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :id')
        ->execute(['amount' => 500, 'id' => 2]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

echo $pdo->query('SELECT balance FROM accounts WHERE id = 2')->fetchColumn() . PHP_EOL;

// Prints:
// 700

If the second update fails, the first update is rolled back too.

Commit And Rollback

commit() makes changes permanent. rollBack() abandons the changes made since beginTransaction().

PHP example
<?php

declare(strict_types=1);

function transactionOutcome(bool $throws): string
{
    return $throws ? 'roll back' : 'commit';
}

echo transactionOutcome(false) . PHP_EOL;
echo transactionOutcome(true) . PHP_EOL;

// Prints:
// commit
// roll back

In real code, always make sure exceptions inside a transaction lead to rollback.

Validate Before Writing

A transaction is not a replacement for validation. Check business rules before or inside the transaction depending on the race risk.

For example, a transfer needs to check that the amount is positive and that the source account has enough balance. In a busy system, the balance check and update must be protected from concurrent requests.

PHP example
<?php

declare(strict_types=1);

function validTransferAmount(int $amount): bool
{
    return $amount > 0;
}

var_dump(validTransferAmount(200));
var_dump(validTransferAmount(0));

// Prints:
// bool(true)
// bool(false)

Isolation And Concurrency

Transactions also interact with concurrency. Two requests may try to update the same rows at the same time. Databases use locks and isolation levels to decide what each transaction can see and when writes wait.

A junior developer does not need to memorise every isolation level, but they should know that:

  • Transactions do not automatically solve every race condition.
  • Long transactions can block other work.
  • Reads used to make write decisions may need locks or atomic update conditions.
  • Deadlocks can happen and may need retry logic.

What To Check

Before moving on, make sure you can:

  • Explain why a multi-step write needs a transaction.
  • Use beginTransaction(), commit(), and rollBack().
  • Roll back when an exception happens.
  • Keep transactions short.
  • Explain why validation and concurrency still matter.

Practice

Practice: Transfer Credits In A Transaction

Write a PHP script that transfers credits between two accounts using a transaction.

Requirements

  • Use SQLite in memory.
  • Create two account rows.
  • Validate that the transfer amount is positive.
  • Subtract from one account and add to the other inside one transaction.
  • Commit on success.
  • Roll back on failure.
  • Print balances after a successful transfer.
Show solution

This solution keeps the two balance updates inside one transaction.

PHP example
<?php

declare(strict_types=1);

function transferCredits(PDO $pdo, int $fromAccountId, int $toAccountId, int $amount): void
{
    if ($amount <= 0) {
        throw new InvalidArgumentException('Transfer amount must be positive.');
    }

    $pdo->beginTransaction();

    try {
        $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id')
            ->execute(['amount' => $amount, 'id' => $fromAccountId]);

        $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :id')
            ->execute(['amount' => $amount, 'id' => $toAccountId]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)');
$pdo->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 200)');

transferCredits($pdo, 1, 2, 300);

foreach ($pdo->query('SELECT id, balance FROM accounts ORDER BY id') as $row) {
    echo $row['id'] . ':' . $row['balance'] . PHP_EOL;
}

// Prints:
// 1:700
// 2:500

In a production transfer, you would also check available balance and protect against concurrent transfers from the same account.