Transactions & ACID Properties

A transaction is a group of SQL statements treated as a single, indivisible unit of work — either all of them succeed and are saved, or none of them are. The classic example is a money transfer: debit one account, credit another. If the debit succeeds but the credit fails, the money vanishes. A transaction prevents that: both happen, or neither does.

Transactions are governed by four guarantees together known as ACID: Atomicity, Consistency, Isolation, Durability. These properties are what make a database trustworthy.

The ACID properties

• Atomicity — all-or-nothing. Every statement in the transaction commits together, or the whole thing rolls back. There is no partial result.
• Consistency — a transaction moves the database from one valid state to another, never leaving it broken. All constraints hold before and after.
• Isolation — concurrent transactions do not step on each other. Each runs as if it were alone, even when many run at once.
• Durability — once a transaction commits, its changes survive crashes, power loss, and restarts. They are written to permanent storage.

COMMIT and ROLLBACK

You start a transaction, run statements, then either COMMIT (make it permanent) or ROLLBACK (undo everything since the start):

-- PostgreSQL / MySQL compatible (standard transaction control)

BEGIN;   -- start the transaction (PostgreSQL also accepts START TRANSACTION)

-- A two-step transfer: both must succeed together
UPDATE account SET balance = balance - 1000 WHERE account_id = 1;  -- debit
UPDATE account SET balance = balance + 1000 WHERE account_id = 2;  -- credit

-- If everything is correct:
COMMIT;        -- both updates become permanent

-- If something is wrong (checked before COMMIT):
-- ROLLBACK;   -- both updates are undone; balances unchanged

If the connection dies after the debit but before the COMMIT, atomicity ensures the database automatically rolls back — the debit never happened. The money is safe.

Savepoints: partial rollback

A savepoint marks a point inside a transaction you can roll back to without abandoning the whole transaction:

BEGIN;

INSERT INTO student (student_id, full_name) VALUES (10, 'Asha');

SAVEPOINT after_student;   -- mark a checkpoint

INSERT INTO enrolment (student_id, course) VALUES (10, 'DBMS');

-- Oops, that enrolment was wrong. Undo only it:
ROLLBACK TO SAVEPOINT after_student;

-- The student insert is still pending. Commit just that:
COMMIT;

Savepoints (SAVEPOINT, ROLLBACK TO SAVEPOINT) are standard SQL and supported by both PostgreSQL and MySQL/InnoDB.

Auto-commit: the default to watch out for

By default, many database clients run in auto-commit mode: each statement is its own transaction, committed immediately. That means a lone UPDATE is permanent the instant it runs — there is nothing to ROLLBACK. To get all-or-nothing behaviour, you must explicitly start a transaction with BEGIN (or turn auto-commit off). This is the source of many "I can't undo my mistake" moments.

Isolation levels

Isolation is not binary — the SQL standard defines four levels, trading consistency against concurrency. Higher isolation prevents more anomalies but allows less parallelism:

The anomalies these prevent:

• Dirty read — reading another transaction's uncommitted (possibly-to-be-rolled-back) changes.
• Non-repeatable read — reading the same row twice in one transaction and getting different values because another transaction committed in between.
• Phantom read — re-running a query and finding new rows that another transaction inserted.

-- Set the isolation level for the next transaction (standard SQL)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... statements ...
COMMIT;

Vendor defaults differ — an important point: PostgreSQL defaults to READ COMMITTED; MySQL/InnoDB defaults to REPEATABLE READ. So the same code can behave differently across the two unless you set the level explicitly. Knowing your database's default prevents subtle concurrency surprises.

Why isolation matters: a quick scenario

Two staff members process the last seat in a batch at the same time. Without proper isolation, both read "1 seat left," both write "0 seats left," and the batch is double-booked. Adequate isolation (or row locking) makes one transaction wait for the other, so only one booking succeeds. This is exactly the kind of correctness ACID guarantees.

Common mistakes

• Forgetting auto-commit is on. Running a destructive statement outside an explicit transaction means there is nothing to roll back. Wrap risky work in BEGIN ... COMMIT.
• Holding transactions open too long. A transaction left uncommitted holds locks and blocks others. Keep transactions short; commit or roll back promptly.
• Assuming the same isolation level everywhere. PostgreSQL and MySQL differ by default. Set the level explicitly when correctness depends on it.
• Catching an error but not rolling back. If a multi-step transaction hits an error, you must ROLLBACK — otherwise you may leave it in a half-done, locked state.
• Choosing SERIALIZABLE by reflex. It is the safest but slowest level and can cause more transaction retries. Use the lowest level that is still correct for your case.

FAQ

What happens if the server crashes mid-transaction? Atomicity and durability handle it: uncommitted work is rolled back on restart, and committed work is recovered intact. You never get a half-applied transaction.

Do I need transactions for a single statement? A single statement is already atomic on its own. You need explicit transactions when multiple statements must succeed or fail together.

Are transactions slow? The overhead is small and the safety is essential for any multi-step change involving money, inventory, or linked records. Do not skip them to chase marginal speed.

Keep learning

Transactions protect the bulk changes you make with INSERT, UPDATE & DELETE, uphold the rules set by constraints in SQL, and wrap the multi-step logic in stored procedures.

Want to write data changes that are always safe and recoverable? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.