CodeRang
SQL topics

SQL Transactions: BEGIN, COMMIT, and ROLLBACK

SQL transactions group related database operations into one logical unit of work. This guide explains BEGIN, COMMIT, ROLLBACK, isolation, and the concurrency problems transactions are designed to control.

Updated · SQL Arena team

SQL for backend developers trackAll problems

What you will practice

  • Group related changes between BEGIN and COMMIT
  • ROLLBACK when an error makes the operation invalid
  • Reason about isolation levels and concurrency anomalies
  • Protect rows with SELECT FOR UPDATE
  • Verify the final state after an atomic operation

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Verify balances after a transfer

Alice had 1200 USD and Bruno had 700 USD before the transfer. One transaction moved 250 USD from Alice to Bruno and recorded the movement in ledger_entries. Return owner_name, balance, and currency for accounts 1, 2, and 3, ordered by id, to verify the final state.

Query 1PostgreSQLv16
SELECT owner_name, balance, currency
FROM accounts
WHERE id IN (1, 2, 3)
ORDER BY id;
Submit
Resultaccountsledger_entries
Answer accepted

How SQL Transactions Work

The SELECT is the final verification step. Alice now has 950 USD and Bruno has 950 USD, so their combined amount is unchanged. Clara is a control row and remains at 430 USD. ORDER BY id makes the result deterministic.

The debit and credit belong in one transaction. BEGIN starts the unit of work, both updates happen before finalization, and COMMIT accepts the completed operation. If the credit or a validation step fails, ROLLBACK must cancel the debit as well. Otherwise the database can keep a half-finished transfer.

Atomicity does not solve concurrency by itself. If two requests debit the same account at nearly the same time, both can read the old balance before either writes. That can cause a lost update or let an overdraft check use stale state. Isolation levels and locking control what concurrent transactions may observe and change.

In PostgreSQL, SELECT ... FOR UPDATE is common when you need to read, validate, and then update a row safely. The lock lasts until the transaction ends. For two-account transfers, locking rows in a consistent order, such as ascending id, reduces deadlock risk.

SQLPostgreSQL v16
SELECT owner_name, balance, currency
FROM accounts
WHERE id IN (1, 2, 3)
ORDER BY id;
Query result
owner_namebalancecurrency
Alice950USD
Bruno950USD
Clara430USD

Common SQL Transaction and Isolation Mistakes

01

Committing a partial operation

If the debit succeeds but the credit fails, committing the first change leaves the system inconsistent. Related changes must share one transaction, and an unrecoverable error before completion should trigger ROLLBACK.

02

Checking a balance without concurrency protection

A condition such as balance >= amount is not enough if another transaction can change the same row between the read and the write. Use an atomic update, suitable isolation, or an explicit row lock.

03

Locking multiple rows in inconsistent order

Two transfers can each lock one account and then wait for the other. Taking locks in a stable order helps reduce avoidable deadlocks.

Practice problems on this topic

SQL for backend developers track

SQL Arena has transaction scenarios where correctness depends on failures and concurrency. Exercises cover stock deduction, transfers without overdraft, concurrent debits, lost counter increments, pending-job queues, and one-time seat booking. These exercises are Premium. For a broader backend path, continue with the “SQL for Backend Developers” track. SQL Arena contains 1,192 auto-checked tasks, with separate browser sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. Answers are tested on hidden data. Interview Mode uses a server-side timer, disables hints, and produces a report; one run per day is free. The AI mentor has 10 free uses.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What are SQL transactions?

SQL transactions group related database operations into a single unit of work. The operations can be committed together or rolled back when the transaction should not be completed.

How do SQL transactions work?

A transaction begins, performs one or more database operations, and then ends with either COMMIT or ROLLBACK. COMMIT makes the completed changes permanent, while ROLLBACK discards uncommitted changes.

What do BEGIN COMMIT and ROLLBACK mean in SQL?

BEGIN starts an explicit transaction. COMMIT accepts its changes, while ROLLBACK cancels changes made in the current transaction before they are committed.

What are SQL transaction isolation levels?

Isolation levels control how concurrent transactions can observe and affect each other's work. The SQL standard defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, although database implementations differ.

Why are transactions important in SQL?

Transactions help keep related changes consistent when several statements belong to one operation. They also provide mechanisms for handling failures and controlling interactions between concurrent database work.

See also

Practice SQL Transactions

Practice cases where failures or competing requests change the correct solution: transfers, debits, counters, queues, and reservations. The goal is a safe operation, not a single successful run.

Practice SQL transactions

More practice from CodeRang