SQL Transactions Explained

Imagine transferring money from one account to another. You subtract from one balance and add to the other. If the database crashes after the subtraction but before the addition, money vanishes. A transaction is the mechanism that makes those two steps happen together or not at all — the single most important idea for keeping data correct.

We will use the familiar employees and orders tables from the SQL learning hub, plus a small accounts table for the classic transfer example. The commands here work in MySQL, PostgreSQL, and SQL Server with minor keyword differences.

What a transaction is

A transaction is a group of one or more SQL statements that the database executes as a single, indivisible unit. Either every statement succeeds and the changes become permanent, or something fails and the database undoes all of them, as if none had run.

You wrap statements between a start and an end:

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 2;

COMMIT;

START TRANSACTION (or BEGIN) opens the transaction. The two UPDATE statements run but are not yet permanent. COMMIT makes them permanent together. If anything had gone wrong before COMMIT, you would run ROLLBACK and both updates would disappear, leaving the balances untouched.

Pro tip: By default, most databases run in autocommit mode — each statement commits instantly on its own. The moment you need two or more statements to succeed or fail together, you must open an explicit transaction. Forgetting this is how half-finished updates end up in production data.

The four ACID guarantees

Transactions are trustworthy because they satisfy four properties, remembered by the acronym ACID.

Atomicity means all-or-nothing. The transfer above either moves the full 5000 or moves nothing. There is no state where the money left one account but never arrived in the other.

Consistency means the database moves from one valid state to another. Constraints like foreign keys, unique keys, and check constraints are enforced; a transaction that would break a rule is rejected. This is where the design work from database normalization pays off, because well-defined constraints define what "valid" even means.

Isolation means concurrent transactions do not step on each other. If a hundred orders are inserted at once, each transaction behaves as though it were running alone, according to the isolation level you choose.

Durability means that once a transaction commits, its changes survive a crash, power loss, or restart. The database writes the change to a durable log before reporting success.

COMMIT and ROLLBACK in practice

Here is a realistic order-processing flow. When an employee finalizes a sale, you insert the order and update inventory together. If the inventory update fails, you must not keep the order.

START TRANSACTION;

INSERT INTO orders (order_id, emp_id, order_date, amount, status)
VALUES (5001, 42, '2026-07-15', 1299.00, 'CONFIRMED');

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 88;

-- If quantity is now negative, the sale is invalid:
-- application checks the result, then decides.
COMMIT;   -- or ROLLBACK on failure

If the application detects that the product was out of stock, it issues ROLLBACK instead of COMMIT, and the order insert is erased along with the inventory change. The customer sees a clean "out of stock" message and the data never records a phantom sale.

Many databases also support savepoints, which let you roll back part of a transaction:

START TRANSACTION;
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales';
SAVEPOINT sales_raise;
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Support';
ROLLBACK TO sales_raise;   -- undo only the Support raise
COMMIT;                    -- keep the Sales raise

Concurrency problems and isolation levels

The hard part of transactions is not one user — it is thousands at once. When transactions overlap, three classic problems appear.

A dirty read is reading another transaction's uncommitted change, which may later be rolled back. A non-repeatable read is reading the same row twice in one transaction and getting different values because another transaction committed a change in between. A phantom read is running the same query twice and getting different rows because another transaction inserted or deleted matching rows.

SQL defines four isolation levels that trade correctness against concurrency:

-- Set the level for the next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT SUM(amount) FROM orders WHERE emp_id = 42;
-- ... more work ...
COMMIT;

Read Uncommitted allows dirty reads and is rarely used. Read Committed prevents dirty reads and is the sensible default in PostgreSQL and SQL Server. Repeatable Read also prevents non-repeatable reads and is MySQL's default. Serializable prevents all three problems by making transactions behave as if they ran one after another, at the cost of the most locking.

Interview note: A frequent question is "What is MySQL's default isolation level, and what is PostgreSQL's?" The answer — Repeatable Read for MySQL (InnoDB), Read Committed for PostgreSQL — signals you have actually run these databases, not just read about them.

Deadlocks: when transactions wait forever

When two transactions each hold a lock the other needs, neither can proceed. This is a deadlock. Say transaction A locks employee row 1 and wants row 2, while transaction B locks row 2 and wants row 1. Modern databases detect this, pick a victim, and roll it back automatically, returning an error you must handle.

-- Transaction A
START TRANSACTION;
UPDATE employees SET salary = salary + 100 WHERE emp_id = 1;
UPDATE employees SET salary = salary + 100 WHERE emp_id = 2;  -- waits on B
COMMIT;

The practical fix is to have every transaction acquire locks in the same order — for example, always update the lower emp_id first. Consistent lock ordering makes the classic deadlock impossible.

Common mistake: Leaving a transaction open while the application does slow work — waiting on user input, calling an external API, or sleeping. Locks are held for the whole transaction, so long transactions block everyone else. Keep transactions short: open, do the writes, commit.

Common mistakes to avoid

Beginners tend to forget to commit, leaving changes invisible to everyone else and locks held indefinitely. Others wrap far too much in one transaction, hurting concurrency, or catch an error and continue without rolling back, producing exactly the half-finished state transactions are meant to prevent. The rule is simple: keep transactions small, always commit or roll back on every path, and never do slow non-database work inside one.

Where you meet transactions as a developer

You rarely type START TRANSACTION by hand in a real application. Frameworks manage it for you. In Spring, the @Transactional annotation you will study in Spring Data JPA wraps a method so that all its database work commits together or rolls back on any exception. Understanding what the framework is doing underneath — and why it sometimes deadlocks or reads stale data — is what turns a junior developer into someone who can debug production incidents.

Transactions also lean on the indexes you tune: the database locks the rows and index entries a transaction touches, so a poorly indexed query can lock far more than you intended. These topics connect, which is why they come up together in SQL interviews.

Putting it together

A transaction is the database's promise that a group of changes is atomic, consistent, isolated, and durable. You open it, run your statements, and end with COMMIT or ROLLBACK. You pick an isolation level to balance correctness against concurrency, keep transactions short to avoid blocking, and order your locks consistently to dodge deadlocks. Get comfortable reasoning about these guarantees and you will write data-handling code that stays correct under real production load — the skill that backend teams value most.