How Database Transactions Work in PHP for Atomic Operations

A transaction is not an undo button. It guarantees one thing: all database writes inside it complete together, or the database reverts to its prior state. File writes, emails, API calls stay done. That distinction separates a recoverable failure from a weekend firefight.

MySQL InnoDB calls this atomicity. It delivers it through a redo log and an undo log. When your code calls PDO::beginTransaction, InnoDB starts recording changes. PDO::commit makes them permanent. PDO::rollBack replays the undo log and the tables return to where they were. Your PHP code never sees the mechanism. It sees three methods and the contract they enforce.

PDO::beginTransaction opens. PDO::commit makes changes durable. PDO::rollBack discards them. Every write between begin and either commit or rollBack belongs to the atomic unit. Reads inside the unit see uncommitted changes. No other session sees them until the commit succeeds.

Write the Pattern with Named Parameters

Consider a funds transfer. You debit one account and credit another. Without a transaction, a crash after the debit deletes money. With one, both updates survive or neither does.

$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from');
    $stmt->execute(['amount' => 500, 'from' => 1001]);
    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to');
    $stmt->execute(['amount' => 500, 'to' => 2002]);
    $pdo->commit();
} catch (\Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

The catch block rolls back and re-throws. Swallowing the exception hides the root cause for weeks. Always re-throw or log before rolling back.

Handle Deadlocks with a Retry Loop

InnoDB detects deadlocks when two operations hold locks the other needs. It picks a victim, rolls it back, and throws a deadlock error. Catch that specific condition and retry the entire unit. Cap retries at three. Beyond three, the deadlock points to a schema design problem, not a transient conflict. Pause one second between attempts. That gives the competing operation time to finish.

Atomicity handles all-or-nothing writes. Consistency ensures the database moves from one valid state to another. Isolation controls what uncommitted data other sessions see. Durability means commited writes survive a power failure. MySQL InnoDB delivers all four when your PHP code uses transactions correctly.

Consistency means foreign key constraints and CHECK clauses fire at commit time, not before. An operation that inserts a child row before its parent fails at commit unless you defer constraint checking. InnoDB does not support deferred constraints. Insert the parent row first. Isolation depends on the isolation level, not on the transaction itself. Changing the level mid-operation is allowed but rarely useful.

Durability relies on the InnoDB doublewrite buffer and the binary log. On a shared cPanel host the binary log is often disabled. Point-in-time recovery after a crash becomes impossible. Committed writes in the tablespace still survive. For a developer on a typical shared host the practical takeaway: durability is weaker than advertised but still stronger than a file write.

The isolation level determines which anomalies your operation tolerates. The four levels, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, run fastest to slowest, dirtiest to safest. MySQL InnoDB defaults to REPEATABLE READ. That default prevents non-repeatable reads and phantom rows. It does not prevent all serialisation anomalies.

Use READ COMMITTED for API Workloads

READ COMMITTED matches most web request patterns. Each statement sees only committed data, even if another session commits between yours. This blocks dirty reads but permits non-repeatable reads. For a balance check followed by a withdrawal, you need REPEATABLE READ or a locking read. For a dashboard query aggregating recent orders, READ COMMITTED suffices and avoids the gap locks REPEATABLE READ imposes.

Set the level with a session-level SET TRANSACTION ISOLATION LEVEL statement before opening. It persists for the connection lifetime but resets on reconnect. Always set it explicitly after opening a new connection if your application needs a non-default level.

A rollback undoes database writes. It does not delete files. It does not recall emails. It does not cancel API requests. If your code writes a receipt to disk and the operation fails, the file stays on disk. If it fires a payment confirmation email and a deadlock follows, the email is already in the recipient's inbox. The rollback is silent about these external actions.

File System Writes Corrupt State

Writing files inside the block is a pattern that corrupts application state. The file represents a fact the database no longer supports. Commit first, then write the file. If the file write fails, you have a database record with no corresponding file. That is recoverable. A file with no database record is not.

Emails and API Calls Are Irreversible

Once an SMTP server accepts a message, you cannot claw it back. Once an HTTP request returns a 200, the remote system has acted. The compensating pattern is your only recourse. Write a second operation that reverses the logical effect of the first: issue a refund, send a correction email. This reversal code must live outside the original block and must handle the case where the original commit succeeded but the reversal fails.

Savepoints for Partial Rollbacks

PDO supports savepoints for partial rollbacks within a larger unit. A savepoint marks a point to which you can roll back without discarding earlier work. This is not a true nested transaction. Rolling back to a savepoint does not release locks acquired after it in InnoDB. Those locks persist until the outer unit ends. Use savepoints for multi-step operations where one step failure should not abort everything, such as a bulk import that skips invalid rows. Do not use them to simulate autonomous operations. InnoDB does not support them.

Keep the operation open for the shortest possible time. Every millisecond holds locks that block other sessions. An operation that spans an HTTP call to a third-party API can hold row locks for seconds. On a busy site, that cascades into connection pool exhaustion. Avoid beginTransaction, curl_exec, commit. Use curl_exec, beginTransaction, write based on the result, commit.

Touch the fewest tables necessary. InnoDB uses row-level locking, but gap locks and next-key locks under REPEATABLE READ can block inserts on adjacent rows. An operation that updates a single row in a narrow index is fast and unlikely to deadlock. One that scans a wide range and updates multiple tables is a deadlock candidate.

When an operation includes a SELECT followed by an UPDATE based on the result, use SELECT ... FOR UPDATE. This takes an exclusive lock on the selected rows and prevents another session from modifying them between your read and your write. Without this locking read, two sessions can read the same row, both decide to update it, and one update overwrites the other silently. That race condition is hard to reproduce and harder to diagnose.

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Row	Locking Overhead
READ UNCOMMITTED	Possible	Possible	Possible	Very Low
READ COMMITTED	Not Possible	Possible	Possible	Low
REPEATABLE READ (Default)	Not Possible	Not Possible	Not Possible	Medium
SERIALIZABLE	Not Possible	Not Possible	Not Possible	High

Write inside a transaction, roll back, and assert the database state outside it. PHPUnit handles this with a fresh connection per test. Begin, perform the write, roll back, then query with a separate connection to verify the row count is unchanged.

Testing external side effects demands a different approach. Mock the file system or mailer. Assert that the mock is called only when the operation commits, never when it rolls back. Simulate a failure after the external call and verify that no database changes persist. This is the only way to catch the pattern where the external call sits inside the block.