SQL Transactions and ACID

A database transaction works like a transfer slip at a bank counter. The slip records money leaving one account and entering another, and the clerk completes the transfer only when both entries can be posted. A half-finished slip cannot become the final ledger.

ACID describes the guarantees that make this possible. The letters stand for atomicity, consistency, isolation, and durability. Together they define how a database handles related changes, concurrent requests, errors, and a successful commit.

What ACID Means

A transaction groups SQL statements into one unit of work. PostgreSQL's transaction tutorial describes that unit as all or nothing: intermediate states stay hidden from concurrent transactions, and a failure can leave every step without effect.

• Atomicity keeps all related steps together. Every step commits, or every step rolls back.
• Consistency keeps constraints and other database rules true before and after the transaction.
• Isolation controls what concurrent transactions can observe and how their changes interact.
• Durability protects a committed result against the failures covered by the database and its configuration.

The transfer slip provides one model for all four properties. Atomicity keeps the debit and credit together. Consistency prevents an invalid balance or missing account reference. Isolation keeps another clerk from acting on a half-posted transfer. Durability means a completed transfer remains recorded after the database confirms it.

Atomicity Makes the Transfer All or Nothing

Atomicity prevents a multi-step operation from stopping in a misleading middle state. A transfer must not debit the sender and then lose the credit because a later statement fails.

This example assumes account_entries.account_id has a foreign key to accounts.id. The second insert fails because account 999999 does not exist, so the application rolls back the debit as well.

BEGIN;

UPDATE accounts
SET balance_cents = balance_cents - 5000
WHERE id = 1;

INSERT INTO account_entries (account_id, amount_cents)
VALUES (999999, 5000); -- foreign-key error

ROLLBACK;

After ROLLBACK, account 1 has its original balance. The database has not accepted one line from the transfer slip while rejecting the other.

Applications must still handle transaction errors correctly. An error does not give every database product and client library identical behavior, so the error path should issue ROLLBACK and discard the failed unit of work.

Consistency Keeps Every Rule True

Consistency means the transaction moves the database from one valid state to another. The database can enforce only the rules expressed through constraints, data types, triggers, or correct transaction logic.

A check constraint can stop the transfer slip from taking an account below zero:

ALTER TABLE accounts
ADD CONSTRAINT balance_cannot_be_negative
CHECK (balance_cents >= 0);

BEGIN;
UPDATE accounts
SET balance_cents = balance_cents - 15000
WHERE id = 1; -- fails when the balance is 10000
ROLLBACK;

The failed update leaves the prior balance intact. A foreign key can protect account references, while a unique constraint can stop the same transfer identifier from being recorded twice.

Application validation still improves error messages, but it cannot replace database constraints. Another controller, import script, background worker, or administration tool can write to the same tables. Put the final rule where every writer must obey it.

Some rules span several rows and need both constraints and transaction logic. A transfer can lock the source account, verify the available balance, and write both ledger entries before committing. The transaction keeps those checks and changes together while the constraints reject invalid values at the final database boundary.

Isolation Controls Concurrent Changes

Isolation matters when two requests reach the same rows at nearly the same time. Without the right transaction behavior, both can read the old balance or inventory count and both can decide that their change is safe.

A locking read makes the account row part of the transaction's protected work:

BEGIN;

SELECT balance_cents
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance_cents = balance_cents - 5000
WHERE id = 1;

COMMIT;

A competing transaction that tries to lock the same row must wait until this transaction commits or rolls back. The second clerk cannot take the same ledger page and silently post against an earlier balance.

Isolation levels change which concurrency anomalies are possible. PostgreSQL documents dirty reads, nonrepeatable reads, phantom reads, and serialization anomalies. Its Read Committed level can return different committed data to two successive queries in one transaction, while Serializable can abort a conflicting transaction so the application can retry it.

MySQL InnoDB has its own defaults and locking details. Treat each database manual as the authority, then write a concurrency test for the business rule that matters, such as inventory never becoming negative or a reset token being consumed once.

Durability Protects a Successful Commit

Durability begins after the database reports a successful commit. The completed change should survive the crashes and restart scenarios covered by the engine, storage, and configuration.

BEGIN;

INSERT INTO transfers (id, from_account_id, to_account_id, amount_cents)
VALUES (9002, 1, 2, 5000);

COMMIT;

-- After reconnecting:
SELECT amount_cents FROM transfers WHERE id = 9002;

The final query should still return 5000. The transfer slip has moved from pending work to the permanent ledger.

Reliable recovery still requires backups alongside database durability. MySQL's InnoDB ACID documentation ties durability to redo logging, flush settings, the operating system, and storage hardware. Replication and backups protect different failure cases, so a successful COMMIT should never become the entire recovery plan.

How to Use BEGIN, COMMIT, and ROLLBACK

BEGIN starts the explicit transaction, COMMIT makes its changes permanent, and ROLLBACK cancels its uncommitted changes. A successful transfer has this basic shape:

BEGIN;

UPDATE accounts
SET balance_cents = balance_cents - 5000
WHERE id = 1;

UPDATE accounts
SET balance_cents = balance_cents + 5000
WHERE id = 2;

COMMIT;

The application should verify that both updates changed the expected rows before committing. A statement that matches zero rows may be logically wrong even when the database reports no SQL error.

Database client libraries expose affected-row counts in different ways, so check the API used by the application. The rule stays stable: confirm the transfer changed one sender row and one recipient row before the commit makes either change permanent.

When validation after the updates finds a problem, finish with ROLLBACK instead:

BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 42;

-- The order check fails in the application.
ROLLBACK;

SELECT quantity FROM inventory WHERE id = 42;

The final query returns the quantity from before the transaction. The rollback has canceled the uncommitted update.

Common Pitfalls & Debugging

Why did one statement commit before the next one failed?

Autocommit is the usual cause of this failure. MySQL enables autocommit for new connections by default, so each successful statement becomes its own transaction unless the application starts an explicit one. PostgreSQL also wraps an individual statement in an implicit transaction when there is no explicit BEGIN.

Log the transaction boundary and check the connection used by every statement. A transaction belongs to a connection, so starting it on one connection and running the next query on another leaves the statements in separate units of work.

Why is another request waiting on a lock?

Long transactions hold database resources while application code continues doing unrelated work. A remote API call, report calculation, or user prompt inside the transaction can leave other requests waiting on rows they need.

Validate ordinary input before BEGIN, perform only the database work needed to protect the rule, and commit promptly. If transactions update the same rows in different orders, they can deadlock. Update shared resources in a consistent order and retry the whole transaction when the database reports a retryable deadlock or serialization failure.

Why did the same query return different rows?

The isolation level may allow a nonrepeatable read or phantom read. Under PostgreSQL Read Committed, each statement sees a snapshot taken when that statement begins, so a later query can see changes committed by another transaction.

Identify the required invariant before increasing isolation. Repeatable Read offers a more stable snapshot, while Serializable can reject a transaction whose result cannot match any one-at-a-time execution. Stronger levels require deliberate retry handling and can change blocking or throughput.

Best Practices for Transaction Boundaries

• Group statements by one business action, such as placing an order or moving inventory.
• Use constraints for rules that must survive every code path.
• Check affected-row counts when a missing row is a logical failure.
• Keep network calls, file processing, and email delivery outside the transaction.
• Handle rollback in every error path, then rethrow or report the original error.
• Make deadlock and serialization retries repeat the complete transaction.
• Test concurrency with separate database connections instead of one serial test script.

A short transaction with a named invariant is easier to review than a large request handler wrapped in BEGIN and COMMIT. The boundary should show exactly which database facts must change together.

Frequently Asked Questions

Does every SQL statement need an explicit transaction?

No. Use an explicit BEGIN when several statements must commit or roll back as one unit, or when several reads must share one transaction snapshot or locking rule. If one statement committed before the next failed, see the autocommit debugging section above.

Does ROLLBACK undo everything a program has done?

No. ROLLBACK reverses uncommitted transactional DML on a transaction-safe engine such as InnoDB. Writes to nontransactional tables remain, and DDL statements that trigger implicit commits cannot be rolled back. It also cannot retract external side effects or work committed by another transaction.

Which isolation level should an application use?

Start with the database default, then test the exact concurrency rule the application must protect. PostgreSQL defaults to Read Committed, while MySQL InnoDB defaults to Repeatable Read. Stronger isolation can prevent more anomalies, but applications must also handle retries and possible blocking.

Can a transaction span MySQL and an external API?

A normal SQL transaction controls work inside one database connection. It does not make an external payment, email, or webhook transactional. Record the database state clearly, commit it, and coordinate later side effects with an outbox, queue, or idempotent retry design.

What is a deadlock and how should an application handle one?

Two transactions each hold a lock the other needs, so neither can continue. The database detects this and kills one with an error. The application should catch that specific error and retry the whole transaction, not just the failed statement.

Can transactions be nested?

Not truly, but savepoints give you the useful part. A savepoint marks a point inside a transaction you can roll back to without discarding everything before it, which is how libraries implement what looks like a nested transaction.

What happens to an open transaction if the connection drops?

The database rolls it back. Uncommitted work is discarded when the session ends, which is the behaviour you want: a half-finished transfer disappears rather than half-applying. Anything already committed stays committed.

Self-Check

1. Which ACID property prevents a transfer from committing only the debit: atomicity, consistency, isolation, or durability?
2. What happens when an InnoDB transaction updates two rows, the second update fails, and the application issues ROLLBACK?
3. If an inventory row starts at quantity 8, a transaction subtracts 1, rolls back, and then selects the row, what quantity is returned?
4. Which operation belongs outside a short database transaction: updating an account row, inserting a ledger entry, calling a remote payment API, or checking an affected-row count?
5. With MySQL autocommit enabled, what happens when two updates run without BEGIN and the second update fails?

Answers

1. Atomicity. It keeps the debit and credit in one all-or-nothing unit.
2. Both uncommitted row changes are reversed. InnoDB supports transactional DML, so the failed unit returns to its prior state.
3. 8. The rollback cancels the uncommitted subtraction before the later selection runs.
4. Calling a remote payment API. A database rollback cannot retract an external request, and the network wait keeps locks open longer.
5. The first update remains committed. Autocommit makes each statement its own transaction unless the application starts an explicit transaction.

Next Steps

Use schema design basics to model the constraints that consistency depends on, then read SQL indexes and query optimization to understand the cost of the reads and writes inside a transaction. For application code, PHP and MySQL with PDO shows transaction methods and prepared statements, while SQL joins covers the relationships those transactions update.