A transaction is a single unit of work that either happens completely or not at all. It is the mechanism that lets a database move money, update inventory, or book a seat without ever leaving the data in a half finished state.
Every serious application needs to change more than one row at a time and stay correct when many users hit the same data at once. Transactions are how relational databases make that safe. In this guide we build up from the classic bank transfer, work through the four ACID properties one by one, compare the isolation levels defined by the SQL standard, and finish with the concurrency problems and best practices you will meet in production.
What this guide covers
1. What a transaction actually is
A transaction groups one or more SQL statements so the database treats them as an indivisible whole. Either all of the statements take effect together, or none of them do. There is no middle ground where half the work is saved and half is lost.
The idea matters because real operations rarely map to a single statement. Transferring money is a debit plus a credit. Placing an order writes an order header, several order lines, and a stock adjustment. If the server crashes, the network drops, or a constraint fails partway through, you do not want a customer charged for goods that were never reserved. A transaction gives you a clean boundary: commit to make every change permanent, or roll back to undo the whole batch as if it never ran. The theory behind this guarantee is described in detail on the ACID Wikipedia article.
It helps to think of a transaction as having a clear beginning, a body of work, and one of two possible endings. You open it, you run your statements, and then you decide: commit and keep the results, or roll back and discard them. Until you decide, every change you have made is provisional and private to your own session. Other users continue to see the old values as if you had never touched them. That private working area is the heart of why transactions are safe to build on. It lets you attempt risky, multi step work and back out cleanly the moment anything looks wrong, without ever exposing a broken state to the rest of the system.
2. The bank transfer example
The textbook case is moving 100 from account A to account B. It is two updates that must succeed or fail as a pair. If the debit runs but the credit fails, money simply vanishes.
-- Move 100 from account 1 to account 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100
WHERE id = 1;
UPDATE accounts SET balance = balance + 100
WHERE id = 2;
COMMIT;Between START TRANSACTION and COMMIT, no other session sees the intermediate state where account 1 has been debited but account 2 has not yet been credited. If anything goes wrong before the commit, you issue a rollback instead and both updates disappear.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- The recipient account was closed: abandon everything
ROLLBACK;
-- accounts.balance for id 1 is back to its original valueSyntax note: BEGIN, BEGIN WORK and START TRANSACTION all open a transaction and are effectively interchangeable across MySQL, PostgreSQL and SQL Server. Pick one and stay consistent. Many drivers also run in autocommit mode by default, where each statement is its own transaction until you explicitly open one.
3. BEGIN, COMMIT, ROLLBACK and SAVEPOINT
Four commands control the lifecycle of a transaction. Learn these and you can drive transactions in any SQL database.
| Command | What it does |
|---|---|
START TRANSACTION / BEGIN | Opens a new transaction; changes are now provisional. |
COMMIT | Makes every change since the start permanent and visible to others. |
ROLLBACK | Discards every change since the start, restoring the prior state. |
SAVEPOINT name | Marks a point you can roll back to without abandoning the whole transaction. |
A SAVEPOINT is a checkpoint inside a transaction. You can undo part of the work and keep the rest, which is handy when one optional step fails but the main operation should still commit.
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (42, 250);
SAVEPOINT after_order;
-- Try to apply a promo code that turns out to be invalid
UPDATE orders SET total = total - 50 WHERE id = LAST_INSERT_ID();
-- Undo just the discount, keep the order itself
ROLLBACK TO SAVEPOINT after_order;
COMMIT;The order survives at full price while the failed discount is rolled back. The official PostgreSQL transactions tutorial walks through savepoints with a similar worked example if you want another perspective.
4. The four ACID properties
ACID is an acronym for the four guarantees a transactional database promises: Atomicity, Consistency, Isolation, and Durability. Together they are what separate a database from a plain file you write to.
Atomicity
All or nothing. Every statement in the transaction succeeds, or the database rolls the whole thing back. There is no partial commit. This is exactly what protects the bank transfer above.
Consistency
A transaction moves the database from one valid state to another. Constraints, foreign keys, and triggers are all enforced at commit, so rules like "balance can never be negative" or "every order line points at a real product" are never violated by a committed transaction.
Isolation
Concurrent transactions do not step on each other. Each one behaves as though it were running alone, even when hundreds run at the same time. How strictly this holds is tunable through isolation levels, covered in the next section.
Durability
Once a transaction commits, its changes survive crashes, power loss, and restarts. The database writes the change to durable storage, typically through a write ahead log, before it reports success. This is why a commit can feel slightly slower than an uncommitted write: the engine is making sure the record is safely on disk so that even if the power fails a millisecond later, your committed data is still there when the server comes back up. Durability is the property that lets you tell a customer "your payment went through" and actually mean it.
| Property | Guarantees | What breaks without it |
|---|---|---|
| Atomicity | All statements commit together or none do. | A crash mid transfer debits one account but never credits the other. |
| Consistency | Only valid states are committed; all constraints hold. | Orphan order lines and negative balances slip past the rules. |
| Isolation | Concurrent transactions do not see each other's uncommitted work. | One session reads half finished data from another and acts on garbage. |
| Durability | Committed data survives crashes and restarts. | A confirmed payment disappears after the server reboots. |
5. Isolation levels and read anomalies
Isolation is the ACID property with the most dials to turn, so it deserves a section of its own. Perfect isolation is expensive because it forces transactions to wait for each other. If every transaction had to run completely alone, a busy system would grind to a crawl while requests queued up behind one another. To let you find the right balance, the SQL standard defines four isolation levels so you can trade a little consistency for a lot more concurrency. Each level allows or forbids three classic read anomalies, which are the specific ways that concurrent transactions can produce surprising results:
- Dirty read: you read a row another transaction has changed but not yet committed, and it may still roll back.
- Non repeatable read: you read the same row twice in one transaction and get different values because another transaction committed a change in between.
- Phantom read: you run the same range query twice and the second run returns extra rows another transaction inserted.
| Isolation level | Dirty read | Non repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible |
| SERIALIZABLE | Prevented | Prevented | Prevented |
You set the level per session or per transaction. Defaults differ by engine: PostgreSQL and Oracle default to READ COMMITTED, while MySQL InnoDB defaults to REPEATABLE READ. The exact behaviour, including how InnoDB blocks phantoms with gap locks, is documented in the MySQL InnoDB isolation levels reference.
-- Raise isolation for a sensitive report
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT SUM(balance) FROM accounts WHERE branch = 'NW';
-- No other session can insert or change NW rows until we commit
COMMIT;The bars show relative concurrency, or roughly how much parallel work the level allows. As you move down toward SERIALIZABLE you gain correctness but the database serialises more work, so throughput falls. The right choice is the weakest level that still keeps your data correct for the task at hand.
6. Concurrency problems: lost updates and deadlocks
Lost update
Two transactions read the same value, each modifies it, and each writes it back. The second write silently overwrites the first, so one update is lost. Imagine two clerks both reading a stock count of 10, each selling 3, and each writing back 7. The true answer is 4.
-- Read, then compute in the app, then write: race condition
SELECT stock FROM products WHERE id = 7; -- app sees 10
UPDATE products SET stock = 7 WHERE id = 7;-- Let the database compute atomically, or lock the row first
UPDATE products SET stock = stock - 3 WHERE id = 7;
-- Or read with an explicit lock inside a transaction
SELECT stock FROM products WHERE id = 7 FOR UPDATE;Doing the arithmetic in a single statement, or reading with SELECT ... FOR UPDATE so the row is locked, removes the race entirely.
Deadlocks
A deadlock happens when two transactions each hold a lock the other needs. Transaction A locks row 1 and waits for row 2, while transaction B locks row 2 and waits for row 1. Neither can proceed. Databases detect this automatically and kill one transaction with a deadlock error so the other can finish. Your application must catch that error and retry. Deadlocks are a normal fact of life on any busy database, so treat them as something to handle gracefully rather than something you can eliminate entirely, and design your retry logic to be safe to run more than once.
Reduce deadlocks: always acquire locks in the same order across your code paths (for example, always update the lower account id first), keep transactions short, and be ready to retry a transaction that fails with a deadlock error rather than surfacing it to the user.
For a deeper treatment of locking behaviour, see our lessons on transactions and concurrency and on transactions, locks and deadlocks.
7. Best practices for writing transactions
Keep transactions short
Open the transaction as late as possible and commit as soon as the work is done. Long transactions hold locks longer, block other sessions, and raise the odds of a deadlock.
Never wait on the user mid transaction
Do not hold a transaction open while showing a dialog or calling a slow external API. Gather everything first, then run a tight transaction.
Always handle errors and roll back
Wrap the transaction in your language's error handling. On any failure, issue a ROLLBACK so you never leave a half applied change or a dangling lock.
Pick the lowest safe isolation level
Use READ COMMITTED unless a specific operation needs stronger guarantees. Raise the level only where correctness demands it.
Retry on deadlock
Treat deadlock errors as expected under load. Catch them and retry the whole transaction a few times with a small backoff.
-- A safe, short transaction pattern
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- On any application error, run ROLLBACK instead of COMMIT
COMMIT;Master these habits and transactions become the quiet reliability layer under your application. To keep building, work through our managing transactions and concurrency lesson, keep the SQL cheat sheet handy, review the wider SQL best practices, and put it all together in the SQL Intermediate Course.