Skip to content
Tutorials Center
My learning

Topics

Lesson 91 of 147 ยท Databases

Transactions

Learn database transactions in PHP with PDO - beginTransaction, commit and rollBack, the try/catch pattern, a money transfer, savepoints, speed and isolation.

Some jobs take more than one SQL statement, and they only make sense if every statement succeeds. Moving money is the classic case: you take 30 dollars from one account and add 30 to another. If the program crashes between the two updates, money has vanished or been created out of thin air. A transaction solves this. It groups statements into one unit that the database applies completely or not at all.

PDO gives you three methods for this: beginTransaction(), commit() and rollBack(). They're simple to call, but using them correctly takes a pattern: exceptions, a try/catch, and a clear rule about who starts and who finishes the transaction. This lesson builds that pattern step by step with a money transfer, then covers savepoints, the big speed win for bulk inserts, and what isolation means for code that runs on a busy server.

You should be comfortable with prepared statements and CRUD with PDO first. For the SQL side of the topic, see the SQL tutorial's Transactions lesson.

What a transaction guarantees

Database people describe transactions with four letters, ACID:

  • Atomic: all statements take effect, or none do.
  • Consistent: the database moves from one valid state to another. Constraints such as CHECK (balance >= 0) hold at the end.
  • Isolated: other connections don't see your half-finished work.
  • Durable: once commit() returns, the change survives a crash or power cut.

For a PHP developer, atomicity is the part you use every day. You start a transaction, run your statements, and then either commit (make everything permanent) or roll back (throw everything away, as if you never ran it).

The problem: a transfer without a transaction

Here is a small bank. Balances are stored as integer cents, which avoids floating-point rounding errors (see Math Functions). A CHECK constraint forbids negative balances. The transfer function adds money to the receiver first, then takes it from the sender:

PHP A transfer that breaks halfway

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output:

TEXT
Transfer failed: SQLSTATE[23000]: Integrity constraint violation: 19 CHECK constraint failed: balance >= 0
Ana     50.00
Ben    100.00

The debit failed, as it should, but the credit had already run. Ben received 80.00 that nobody paid. Without a transaction, every statement is committed the moment it runs. This mode is called autocommit, and it's PDO's default.

beginTransaction, commit and rollBack

The fix is to wrap both updates in a transaction:

  1. $pdo->beginTransaction() turns autocommit off for this connection until the transaction ends.
  2. Run your statements as usual.
  3. $pdo->commit() makes all of them permanent at once.
  4. If anything goes wrong, $pdo->rollBack() undoes everything since beginTransaction().

Since PHP 8.0, PDO's default error mode is PDO::ERRMODE_EXCEPTION, so a failing statement throws a PDOException. That's exactly what you need: the exception jumps straight to your catch block, where you roll back.

PHP The same transfer, now atomic

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The failed transfer leaves both balances untouched (Ana 50.00, Ben 20.00), and the valid one moves exactly 15.00. Three details in this code matter:

  • The catch block catches Throwable, not only PDOException. Any error, such as a TypeError or your own DomainException, must roll back too.
  • It rethrows the exception after rolling back. The transfer function's job is to keep the data consistent; deciding what message the user sees belongs to the caller. Swallowing the exception would hide the failure.
  • beginTransaction() sits before the try. If starting the transaction fails, there's nothing to roll back.

A transaction that is never committed is rolled back when the connection closes, which happens at the end of every PHP request. So if you forget commit(), your writes silently disappear. If you see "my INSERT runs without errors but the row never appears", look for a missing commit().

A reusable helper

Writing the same try/catch around every transaction gets repetitive, and repetition is where bugs creep in. A small helper takes a callback, runs it inside a transaction and returns its result. Frameworks ship the same idea (Laravel's DB::transaction(), Doctrine's wrapInTransaction()).

This version also checks the business rule in PHP and throws a DomainException with a clear message, then records the transfer in a log table and returns the new log row's ID:

PHP A transaction helper with a callback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output shows two successful transfers and two rejections, and the final balances are 4250, 2750. The interesting case is the transfer to account 7: the debit from account 1 had already run when the code discovered that account 7 doesn't exist. Because the debit happened inside the transaction, the rollback restored the money. The balances still add up to 7000, the same total as at the start.

The @template lines in the DocBlock tell static analysers like PHPStan that transaction() returns whatever the callback returns. PHP itself ignores them; The PHP Type System in Depth explains this.

Transactions make bulk writes fast

Transactions aren't only about safety. In autocommit mode, a database that stores data on disk must make every single INSERT durable before it returns, which usually means waiting for the disk to confirm the write. Inside one transaction, that work happens once, at commit().

This example writes 200 rows to a real SQLite file in the server's temporary folder, first in autocommit mode, then inside one transaction:

PHP Bulk inserts with and without a transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

On our server the transaction version is typically a hundred times faster or more; the exact numbers change on every run. The same effect shows up in MySQL and PostgreSQL. When you import a CSV file or seed test data, wrap the loop in a transaction. For very large imports, commit every few thousand rows so a single transaction doesn't grow huge and hold locks for minutes.

Rules and errors to know

PDO enforces a few rules, and each one throws a PDOException when you break it:

Situation What happens
beginTransaction() while a transaction is active Throws "There is already an active transaction"
commit() or rollBack() with no active transaction Throws "There is no active transaction"
A statement fails inside a transaction Throws; the transaction stays open until you roll back
The script ends with an open transaction PDO rolls it back automatically

$pdo->inTransaction() returns true while a transaction is open. The helper above uses it so that rollBack() never throws a second exception that would hide the first one.

MySQL commits implicitly when it runs DDL statements such as CREATE TABLE, ALTER TABLE or DROP TABLE. If you put one inside a transaction, MySQL silently ends the transaction right there, and since PHP 8.0 your later commit() throws "There is no active transaction". Keep schema changes out of data transactions. PostgreSQL and SQLite can roll back DDL, but code that has to work everywhere shouldn't rely on that.

Savepoints: partial rollbacks

PDO doesn't support nested transactions; a second beginTransaction() throws. When you need to undo only part of a transaction, use SQL savepoints. SAVEPOINT name marks a point, ROLLBACK TO SAVEPOINT name undoes everything after it, and the outer transaction carries on. MySQL, PostgreSQL, SQLite and SQL Server all support savepoints (SQL Server spells them SAVE TRANSACTION).

Here an order import keeps the valid lines and skips the broken one, while the whole import is still one transaction:

PHP Skipping bad rows with a savepoint

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output ends with Rows saved: 2. RELEASE SAVEPOINT forgets a savepoint you no longer need. Some libraries use savepoints to emulate nested transactions: the outermost call runs beginTransaction(), and inner calls create savepoints instead.

Isolation: other users see what?

On a real web server, many PHP processes talk to the database at the same time. Isolation decides how much one transaction can see of another's unfinished work. The SQL standard defines four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. MySQL's InnoDB engine defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED. The SQL tutorial explains the levels in depth in Isolation and Locking.

The practical PHP lesson is about read-then-write code like the helper above: read a balance, check it, then update it. Two requests can read the same balance at the same moment, both pass the check, and both debit. Two common fixes:

PHP
// 1. Lock the row while you read it (MySQL, PostgreSQL). Other transactions
//    that want this row wait until you commit or roll back.
$select = $pdo->prepare('SELECT balance FROM accounts WHERE id = ? FOR UPDATE');

// 2. Let one UPDATE check and change the value atomically, then check rowCount().
$debit = $pdo->prepare(
    'UPDATE accounts SET balance = balance - :cents WHERE id = :id AND balance >= :cents'
);
$debit->execute(['cents' => $cents, 'id' => $from]);
if ($debit->rowCount() !== 1) {
    throw new DomainException('Insufficient funds.');
}

SQLite has no FOR UPDATE; it locks the whole database file for writing instead, which is why it's a fine choice for small sites and tests but not for heavy concurrent writes.

When two transactions each wait for a row the other one holds, the database detects a deadlock and aborts one of them (MySQL error 1213, SQLSTATE 40001). The right response is to retry the whole transaction a few times:

PHP
for ($attempt = 1; ; $attempt++) {
    try {
        return transaction($pdo, $work);
    } catch (PDOException $e) {
        $isDeadlock = in_array($e->getCode(), ['40001', '40P01'], true);
        if (!$isDeadlock || $attempt === 3) {
            throw $e;
        }
        usleep(random_int(10_000, 50_000)); // wait a moment, then try again
    }
}

Keep transactions short: never wait for user input, call a slow external API or send email while a transaction holds locks. Do the slow work before beginTransaction() or after commit().

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A script calls beginTransaction(), runs two INSERTs without errors, and then ends without calling commit(). What is in the table afterwards?

    An uncommitted transaction is rolled back when the connection closes at the end of the script. Forgetting commit() makes writes silently disappear.
  2. 2. Why does the catch block in the transfer function catch Throwable instead of only PDOException?

    Any failure in the middle of the work must undo the earlier statements, not just database errors. After rolling back, the code rethrows the exception.
  3. 3. What happens if you call beginTransaction() twice on the same PDO connection?

    PDO has no nested transactions. Use SQL savepoints when you need to undo only part of a transaction.
  4. 4. Inserting 10,000 rows into an on-disk database is slow. What is the simplest big improvement?

    In autocommit mode each INSERT is made durable separately. One transaction makes them durable once, at commit(), which is often orders of magnitude faster.
  5. 5. In MySQL, you run CREATE TABLE inside a transaction and later call commit(). What happens in PHP 8?

    MySQL commits implicitly before DDL statements, so the transaction is already over. Since PHP 8.0, PDO reports that with "There is no active transaction".

Summary

  • A transaction makes several statements atomic: commit() keeps all of them, rollBack() undoes all of them.
  • Without one, PDO runs in autocommit mode and every statement is permanent immediately, so a failure halfway leaves broken data.
  • The pattern: beginTransaction(), then try { ...; commit(); } catch (Throwable $e) { rollBack(); throw $e; }. Wrap it in a helper that takes a callback.
  • An uncommitted transaction is rolled back when the script ends. A missing commit() makes writes vanish.
  • PDO has no nested transactions; use SAVEPOINT / ROLLBACK TO SAVEPOINT for partial rollbacks.
  • One transaction around a bulk insert is dramatically faster than autocommit.
  • MySQL commits implicitly on DDL. Keep schema changes out of data transactions.
  • For read-then-write logic under concurrency, lock rows with SELECT ... FOR UPDATE or use a conditional UPDATE and check rowCount(). Retry on deadlocks, and keep transactions short.

Next: Using MySQLi