SQL Transactions and Isolation Levels
Learn how SQL BEGIN, COMMIT, ROLLBACK, savepoints, autocommit, and isolation levels protect related database updates.
A database transaction groups related SQL statements so they succeed or fail together. Understanding BEGIN, COMMIT, ROLLBACK, savepoints, and isolation levels helps prevent partial updates and reason about concurrent work. This guide uses PostgreSQL syntax and calls out where behavior depends on the database.
Why group statements in a transaction?
Suppose an order checkout inserts an order row and then reduces inventory. If only one change is saved, the application can leave inconsistent data. A transaction groups the changes into one unit: commit the unit when all required steps succeed, or roll it back when a step fails.
The familiar ACID properties are a useful design checklist:
- Atomicity: the transaction's changes are committed together or rolled back together.
- Consistency: constraints and application rules should hold before and after a successful transaction.
- Isolation: concurrent transactions are controlled so they do not observe or create unacceptable interference.
- Durability: after a successful commit, the database is responsible for preserving the committed result according to its durability configuration.
ACID does not mean every application rule is automatically enforced. Add database constraints for invariants that the database can express, and handle business-level failures in application code.
BEGIN, COMMIT, and ROLLBACK
In PostgreSQL, BEGIN starts a transaction block. The session's statements then belong to that transaction until it ends with COMMIT or ROLLBACK. Without an explicit block, PostgreSQL normally runs each successful statement in its own transaction (autocommit behavior).
BEGIN;
UPDATE accounts
SET balance = balance - 250
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 250
WHERE account_id = 202;
COMMIT;These two updates represent one transfer. In real code, verify that the source account exists and has sufficient funds before committing. If a required check or statement fails, issue ROLLBACK instead. A transaction should be short: do input validation and network calls before opening it where possible, because long-running transactions can hold resources and increase contention.
Transaction control syntax and defaults differ among database engines and client libraries. For example, drivers often expose a transaction API that starts the transaction implicitly. Check the documentation for the specific database, storage engine, and driver your application uses.
Recover part of a transaction with a savepoint
A savepoint marks a point inside an active transaction. If a later optional operation fails, you can roll back to that marker while keeping earlier work in the same transaction:
BEGIN;
INSERT INTO orders (customer_id, status)
VALUES (42, 'pending');
SAVEPOINT optional_item;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), 7, 1);
-- If this optional item cannot be added:
ROLLBACK TO SAVEPOINT optional_item;
UPDATE orders SET status = 'needs_review'
WHERE customer_id = 42 AND status = 'pending';
COMMIT;This example is illustrative: production code should identify the inserted order with a returned key, such as PostgreSQL's INSERT ... RETURNING id, instead of assuming a sequence call is appropriate. Savepoints do not commit data on their own. Their exact behavior and resource effects vary by engine; MySQL's InnoDB documentation, for example, notes that a rollback to savepoint does not release row locks acquired after it.
Isolation: what concurrent transactions can see
Isolation levels describe guarantees around concurrent reads and writes. Lower levels may allow more concurrency but expose more effects from concurrent work; stronger levels can require retries or additional coordination. Names in the SQL standard do not guarantee identical implementation details across engines.
PostgreSQL documents these levels:
- Read Committed: each command sees data committed before that command began. Two reads in one transaction can therefore see different committed results.
- Repeatable Read: in PostgreSQL, statements use a stable snapshot established by the first non-transaction-control statement. This is stronger than the minimum the SQL standard requires for that level, but serialization anomalies can still be possible.
- Serializable: the database aims to make concurrent committed transactions equivalent to some serial ordering. A transaction can be rejected with a serialization failure, so the application must be prepared to retry the complete transaction.
- Read Uncommitted: PostgreSQL treats this request as Read Committed; another engine may implement it differently.
Isolation is not a performance slider to set by guesswork. Define the invariant first. If two users may book the last seat at the same time, consider a constraint, an atomic update, or an appropriate locking/serializable strategy; merely checking availability in a separate read can race with another transaction.
Handle failures and retries safely
A failed SQL statement inside a PostgreSQL transaction leaves the transaction in an error state until it is rolled back (or recovered to a savepoint). Do not continue as if later statements will commit normally. Application code should use the driver's transaction API or a clear begin/try/commit/catch/rollback pattern, and should always release connections back to the pool.
Retry only errors that are safe and expected to be retried, such as a serialization failure when the whole unit of work can be repeated. Make the transaction function idempotent where practical, and avoid sending email, charging a card, or making an external HTTP request inside a database transaction. A database rollback cannot undo an external side effect.
Practical checklist
- List the writes that must succeed or fail together.
- Use database constraints for uniqueness, references, and other expressible invariants.
- Keep the transaction focused and short; do not wait on user input or remote services inside it.
- Choose isolation or locking based on a specific concurrency risk and your database's documented behavior.
- On errors, roll back the whole unit or to a deliberate savepoint; do not silently ignore a partial failure.
- Plan bounded retries for documented transient conflicts, and ensure repeating the work will not duplicate side effects.
- Test concurrent cases against the same database engine and version used in production.
Transactions are most effective when their boundaries match a real consistency requirement. Start with the smallest group of related database changes, then add stronger concurrency controls only when the invariant and engine behavior call for them.
Further reading
- PostgreSQL tutorial: transactions
- PostgreSQL: transaction isolation
- PostgreSQL: SAVEPOINT
- MySQL 8.4: savepoints and rollback to savepoint
Want a structured Java learning path?
Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.