What Is a Transaction in SQL?

Introduction

A transaction groups one or more database operations into a single logical unit of work. The database either commits the unit so its changes become part of the durable state or rolls it back so the attempted work does not remain. This boundary is essential whenever a business action spans several related rows or tables.

Transaction syntax is simple, but reliable transaction design is not. Correctness also depends on constraints, isolation, lock behavior, error handling, retries, idempotency, and the scope of external systems. This tutorial develops those ideas from a bank transfer through production application patterns.

What Is a Transaction?

A SQL transaction begins from a consistent database state, performs reads and writes, and ends with either commit or rollback. Other sessions may see some, all, or none of its intermediate work depending on the database engine and isolation level, but uncommitted changes never become durable committed state.

The transaction should correspond to one business invariant. Transferring money requires a debit and credit to succeed together. Placing an order may require an order row, line items, and inventory reservation. Grouping unrelated work into one transaction increases contention without improving correctness.

Why Transactions Are Important

Without transactions, a failure between related statements can leave partial state. A debit might succeed while the credit fails, or an order might exist without its items. Manual cleanup after every network, process, constraint, or hardware failure is not reliable.

Transactions provide controlled failure handling and concurrency boundaries. They allow the database to enforce constraints against a coherent set of changes and give applications a clear point at which success becomes final.

Basic Transaction Flow

  1. Start an explicit transaction or enter one implicitly.
  2. Read the rows needed to validate business conditions.
  3. Execute the related inserts, updates, and deletes.
  4. Verify affected rows and any application-level rules.
  5. Commit when every required operation succeeds.
  6. Roll back after any failure or rejected condition.

The application must always finish the transaction. Returning a pooled connection with an open transaction can leak locks and make later requests inherit unexpected state.

Transaction Control Commands

CommandPurpose
BEGIN or START TRANSACTIONStarts an explicit transaction
COMMITAccepts the transaction's changes
ROLLBACKCancels uncommitted changes
SAVEPOINTCreates an internal rollback marker
ROLLBACK TOUndoes work after a savepoint
SET TRANSACTIONConfigures supported transaction properties

Exact syntax and behavior vary across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. Always consult the selected engine for isolation names, implicit commits, error behavior, and DDL support.

BEGIN, COMMIT, and ROLLBACK

BEGIN marks the explicit boundary. COMMIT requests that the database make changes durable and visible according to its guarantees. After a successful commit, ordinary rollback cannot reverse the transaction; compensation requires a new transaction or recovery procedure.

ROLLBACK abandons changes made since the transaction began. It does not undo remote API calls, files, emails, or messages already sent. Keep irreversible external side effects outside the database transaction or coordinate them with durable workflow patterns.

Bank Transfer Example

BEGIN;

UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 101
  AND balance >= 500.00;

UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 102;

COMMIT;

This demonstrates syntax but is not production-complete. The application must verify that both accounts exist, the debit changed exactly one row, currency matches, the transfer is not duplicated, and concurrent operations cannot violate rules. If either statement affects an unexpected number of rows, roll back.

A Safer Transfer Structure

A transfer table should record a unique idempotency key, source, destination, amount, currency, status, and timestamps. Account balance changes and the transfer record belong in one transaction. A ledger of immutable entries provides auditability, while a balance can be maintained for efficient reads.

Lock accounts in a consistent identifier order to reduce deadlocks. Reject non-positive amounts and self-transfers unless explicitly supported. Database constraints protect basic invariants, while application or stored procedure logic validates the complete business operation.

Successful and Failed Transactions

A successful transaction reaches commit after every statement and validation passes. A failed transaction may encounter a constraint violation, deadlock, timeout, unavailable row, or business rejection. The correct result is rollback, not a partial commit of statements that happened to succeed first.

Some engines mark the transaction unusable after certain errors until rollback; others allow continued statements. Portable application code should treat unexpected database errors as transaction-fatal unless engine behavior and recovery are deliberately handled.

ACID Properties

PropertyMeaningPractical effect
AtomicityWork succeeds or fails as one unitNo partial transfer or order
ConsistencyCommitted state obeys declared invariantsKeys and checks remain valid
IsolationConcurrent work has controlled interactionAnomalies depend on isolation level
DurabilityCommitted work survives supported failuresRecovery restores committed state

ACID is not magic application correctness. A transaction can commit logically wrong data if the schema and business validation permit it. The properties support rules that designers must first define.

Atomicity

Atomicity means the database does not expose a committed half-transaction. Internal logs and recovery protocols ensure that after a crash the system treats the unit as committed or uncommitted according to the engine's rules.

Atomicity is scoped to resources participating in the transaction. A normal local transaction cannot atomically include a payment provider, email service, and another independent database.

Consistency

Consistency means a transaction moves the database between states allowed by its constraints and transaction logic. Primary keys, foreign keys, unique constraints, checks, triggers, and application invariants all contribute.

If a schema allows a negative account balance and the transaction does not validate funds, committing that value does not violate database mechanics. It violates an unimplemented business rule. Consistency depends on correct modeling.

Isolation

Isolation controls how concurrent transactions observe and affect one another. Full serial behavior is easiest to reason about but can increase blocking or aborts. Lower or snapshot-based isolation may offer more concurrency while permitting specific anomalies.

The isolation name alone is not enough because engines implement levels differently. Understand whether reads use locks or versions, which anomalies are prevented, and when serialization failures require retry.

Durability

Durability means a successful commit survives failures covered by the database's configured guarantees. Write-ahead logging, redo logs, storage flushes, replication, and recovery protocols support this property. Configuration can trade durability for latency, so defaults and settings matter.

Durability is not the same as backup. A committed accidental deletion is durable too. Backups and point-in-time recovery protect against operator error, corruption, and disaster beyond transaction semantics.

Transaction Lifecycle

A transaction may be active, waiting for a lock, failed, committed, or rolled back. Long waits consume connections and can hold versions or locks. Applications need statement and transaction timeouts, cancellation, and logs that identify the business operation and transaction duration.

Commit itself can fail or its response can be lost. If the client times out after sending commit, the outcome may be unknown. Idempotency keys and status lookup allow the application to resolve uncertainty without blindly repeating the business action.

Autocommit Mode

Most clients use autocommit by default, making each standalone statement its own transaction. This is convenient for independent operations but cannot group several statements into one business unit. Starting an explicit transaction temporarily changes that boundary.

Frameworks may hide autocommit and transaction scopes behind annotations or callbacks. Verify actual connection behavior, especially with pooled connections, asynchronous code, and nested service calls.

Implicit vs Explicit Transactions

An explicit transaction has a visible beginning and end. An implicit transaction may start automatically after a statement and require later commit or rollback. Oracle, SQL Server modes, and client libraries differ in how they begin and finish work.

Explicit boundaries are easier to review for multi-statement business operations. Avoid relying on interactive tool behavior as evidence of production client behavior.

Savepoints

A savepoint marks a position inside a transaction. Rolling back to it undoes later work while preserving earlier work in the same transaction. This can support optional sub-operations or batch processing where one recoverable step should not cancel everything.

BEGIN;

INSERT INTO orders (order_id, customer_id, order_status)
VALUES (5001, 101, 'PENDING');

SAVEPOINT before_optional_note;

INSERT INTO order_notes (order_id, note_text)
VALUES (5001, 'Gift wrap requested');

ROLLBACK TO SAVEPOINT before_optional_note;
COMMIT;

Savepoints do not release every lock or external side effect, and behavior varies by engine. They are not a substitute for choosing a sensible transaction boundary.

Isolation Levels

LevelGeneral intentImportant note
Read UncommittedMaximum read concurrencyMay allow dirty reads where supported
Read CommittedRead only committed dataRepeated reads may differ
Repeatable ReadStabilize rows already readPhantom behavior varies by engine
SerializableEquivalent to some serial orderMay block or abort transactions

Snapshot isolation and engine-specific variants may provide consistent versioned views without matching serializable guarantees. Select the level from business invariants and tested behavior, not merely from performance expectations.

Dirty Reads

A dirty read occurs when one transaction reads another transaction's uncommitted change. If the writer rolls back, the reader acted on a value that never became committed. Many production engines prevent dirty reads at their common default levels.

Dirty data is especially dangerous when it drives external actions or cached decisions. Use an isolation level that prevents it rather than trying to compensate after the read.

Non-Repeatable Reads

A non-repeatable read occurs when a transaction reads one row twice and observes different committed values because another transaction updated it between reads. This may be acceptable for a simple query but unsafe when validation assumes the first value remains current.

Use stronger isolation, row locks, or optimistic version checks when the operation depends on a stable value. The correct technique depends on contention and workflow length.

Phantom Reads

A phantom occurs when a repeated predicate query returns a different set of rows because another transaction inserted or deleted matching rows. Counting reservations and then inserting another can violate a capacity rule if concurrent transactions perform the same sequence.

Serializable isolation, predicate or range locks, exclusion constraints, and atomic conditional writes can protect such invariants. Engine implementations differ significantly.

Lost Updates

A lost update occurs when two transactions read the same value, calculate changes, and the later write overwrites the earlier result. Inventory and counters are common examples. Reading quantity 10 twice and writing 9 twice records one sale instead of two.

Use atomic arithmetic updates, SELECT FOR UPDATE, or optimistic versions. Avoid read-modify-write code without concurrency protection.

Write Skew

Write skew occurs when concurrent transactions read a shared condition but update different rows, jointly violating an invariant. Two doctors may each see another doctor on call and independently remove themselves, leaving nobody available.

Row locks on the updated records may not protect a cross-row predicate. Serializable isolation, an aggregate constraint represented by a lockable row, or explicit coordination may be required.

Locks

Databases use shared, exclusive, intent, row, page, table, range, or metadata locks depending on the engine. Locks protect correctness but can cause waits and deadlocks. Version-based reads may reduce read-write blocking but do not eliminate write conflicts.

Lock only what is needed, access resources in a consistent order, keep transactions short, and index predicates so updates do not scan and lock unnecessary rows.

SELECT FOR UPDATE

SELECT ... FOR UPDATE locks selected rows for a planned update in supporting databases. It can protect a read-modify-write sequence, but it must be used inside a transaction and with selective predicates.

BEGIN;

SELECT balance
FROM accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;

-- Validate and update both accounts here.

COMMIT;

The consistent order reduces deadlock probability. Lock syntax and options such as nowait or skip locked are database-specific.

Deadlocks

A deadlock forms when transactions wait on one another in a cycle. Databases detect the cycle and abort one participant. Deadlocks are not always bugs, but frequent deadlocks indicate inconsistent access order, broad updates, missing indexes, or transactions that are too long.

Capture deadlock diagnostics, reduce transaction scope, lock resources consistently, and retry the entire aborted transaction with bounded backoff. Retrying only the last statement can violate business assumptions established earlier.

Transaction Retry Strategy

Serialization failures, deadlocks, and some transient connection errors are expected under concurrency. Retry only errors classified as transient, rerun the complete transaction from a clean connection state, and use exponential backoff with jitter and a strict attempt limit.

The business operation must be idempotent. A unique request key can ensure that repeating an order or transfer request returns the existing result rather than creating another operation.

E-Commerce Order Placement

An order transaction may insert the order, add items, reserve inventory, and write an outbox event. Validate prices and stock inside the controlled boundary. Remote payment authorization should not hold database locks for an unpredictable duration.

A common workflow creates a pending order transactionally, commits it, performs payment through an idempotent external request, then records success or failure in another transaction. Compensation releases inventory when payment fails.

Other Real-World Scenarios

Student enrollment must prevent two concurrent registrations from exceeding course capacity. Library borrowing must mark one copy unavailable and create the loan together. Appointment booking must protect one provider time slot. Ticket sales must reserve a seat exactly once. Testing systems may create a run, its cases, and an outbox notification atomically.

Salary updates and batch imports require validation, audit, and controlled rollback. The details differ, but each scenario starts by naming the invariant and choosing the smallest transaction that protects it.

Database-Specific Behavior

PostgreSQL supports explicit BEGIN, MVCC, and transaction-abort behavior after statement errors. MySQL transaction semantics depend on the storage engine, and some DDL statements cause implicit commits. SQL Server uses BEGIN TRANSACTION and offers locking and row-versioning options. Oracle normally begins transactions implicitly and provides read consistency through MVCC.

SQLite serializes writes according to its journaling mode and transaction type. Never assume syntax implies identical semantics across systems. Test the selected version, driver, and configuration.

Transactions in Application Code

Acquire a connection, begin the transaction, execute every related statement on that same connection, commit only after validation, and roll back in the error path. Ensure cleanup runs even when exceptions or cancellations occur. Framework callbacks can make this structure safer than manual connection-state management.

Avoid network calls, user input, and long computations while locks are held. Pass the transaction context through repository methods so a nested operation does not silently open a separate autocommit connection.

Stored Procedures and Transactions

Stored procedures can centralize transactional rules near the data and reduce network round trips. They are useful for operations shared by several clients, but they require versioning, testing, observability, and clear error contracts.

A stored procedure is not automatically atomic; its statements must execute within the intended transaction behavior. Nested transaction semantics and savepoint support differ by database.

Long-Running Transactions

Long transactions retain locks or old row versions, delay cleanup, enlarge logs, increase conflict probability, and consume pooled connections. Interactive approvals, file processing, and remote API calls should not remain inside one open SQL transaction.

Split long workflows into durable states and short transactions. Use background jobs, status machines, and compensation while preserving idempotency and auditability.

Transaction Performance

Measure transaction duration, commit latency, lock waits, deadlocks, log volume, retries, and rows touched. Batch related statements when it reduces overhead, but avoid enormous transactions that are difficult to roll back and replicate.

Indexes accelerate selective writes but add maintenance. Query plans affect which rows are scanned and locked. Performance tuning must preserve the invariant the transaction exists to protect.

Distributed Transactions

A local transaction cannot directly provide atomicity across independent databases and external services. Two-phase commit can coordinate supported resources but increases coupling, latency, and failure complexity and is not universally available.

Distributed systems commonly use idempotent local transactions, events, and compensation. The business process becomes eventually consistent, so intermediate states and recovery paths must be explicit.

Saga Pattern

A saga divides a long business workflow into local transactions. Each successful step has a compensating action, such as releasing inventory after payment failure. Orchestration uses a coordinator; choreography reacts to events.

Compensation is not time travel. A refund may have fees, and a sent message cannot be unsent. Model state transitions, retries, timeouts, and manual intervention for irreversible effects.

Transactional Outbox

The outbox pattern writes a business change and an event record in the same local transaction. A relay later publishes outbox rows to a message broker. This prevents the gap where the database commits but event publication fails.

Consumers still need idempotency because delivery may occur more than once. Monitor relay lag, failed messages, and unprocessed rows, and retain enough information for replay and reconciliation.

Observability and Unknown Outcomes

Log transaction duration, retry count, isolation, error code, business request identifier, and affected-row expectations without exposing secrets. Monitor open transactions, lock waits, deadlocks, rollback rate, replication lag, and connection pool saturation.

When commit outcome is unknown after a timeout, query by idempotency key rather than automatically repeating the action. Operational tooling should let support teams distinguish pending, committed, compensated, and failed business states.

Constraint Timing and Statement Order

Many constraints are checked at the end of each statement, while some databases support constraints deferred until transaction commit. Deferred checking can help with mutually dependent rows or complex key changes, but it also allows temporarily invalid intermediate state. Application code must not expose or rely on that state outside the transaction.

Statement order remains important even with atomicity. Insert parent rows before children when foreign keys are immediate, delete children before restricted parents, and update unique values in a sequence that avoids temporary collisions. Deferrable behavior is database-specific and should be used narrowly. Prefer a model that permits clear ordering over widespread constraint deferral that makes errors appear only at commit.

Nested Transaction Boundaries

Applications often call a service method marked transactional from another transactional method. Most relational databases do not create an independent nested transaction that can commit separately. Frameworks may join the existing transaction, create a savepoint, suspend it for a new connection, or reject the configuration. These choices affect rollback behavior.

Document which layer owns the boundary. A repository should normally participate in the caller's transaction rather than committing internally. Avoid swallowing an inner exception and then attempting to commit an outer transaction whose connection is already marked failed. Thread-local transaction contexts also become unsafe when work moves to another thread or asynchronous task without explicit propagation.

Read-Only Transactions and Consistent Reports

Transactions are useful for reads as well as writes. A report that issues several queries may need one consistent snapshot so totals, details, and counts describe the same committed point in time. Without that guarantee, later queries can include changes that earlier queries did not see, producing an internally inconsistent report.

Read-only modes can help the database optimize behavior or reject accidental writes, but their guarantees vary. Snapshot duration still matters because long readers can retain old row versions and delay cleanup. For large analytics, use replicas, warehouses, exported snapshots, or staged data rather than holding a transactional snapshot open across slow user interactions.

Testing Transactional Behavior

Unit tests should verify decisions, but integration tests must exercise the real database engine because locks, isolation, constraint timing, and error states are not faithfully reproduced by simple mocks. Test rollback after every failing statement, commit visibility from another connection, affected-row checks, duplicate idempotency requests, and connection cleanup.

Concurrency tests need controlled interleaving across separate connections. Pause one transaction after its read or lock, run the competing operation, and assert the final invariant rather than only individual responses. Include deadlock victim retries, lock timeouts, process termination, and unknown commit outcomes. Production-like engine versions and configuration are essential because an in-memory substitute may have different semantics.

Common Transaction Mistakes

  • Assuming several autocommit statements form one transaction.
  • Committing before validating affected-row counts.
  • Forgetting rollback and cleanup on exceptions.
  • Holding transactions open during remote calls or user input.
  • Using read-modify-write logic without concurrency control.
  • Retrying non-idempotent operations blindly.
  • Assuming all engines implement isolation levels identically.
  • Using savepoints as a substitute for proper boundaries.
  • Believing a local transaction includes messages and APIs.
  • Ignoring deadlock diagnostics, timeouts, and unknown commit outcomes.

Best Practices

Define the invariant and keep the transaction as small as possible while still protecting it. Use constraints, atomic SQL, appropriate isolation, selective indexes, and consistent lock order. Verify row counts and make business requests idempotent.

Handle errors with rollback, classify transient failures, and retry complete transactions with limits. Keep external calls outside lock-holding windows, use outbox or saga patterns when resources span systems, and monitor transaction health in production.

Interview Questions and Answers

What is a SQL transaction?

It is one logical unit of database work that ends in commit or rollback.

What does ACID stand for?

Atomicity, Consistency, Isolation, and Durability.

What is autocommit?

It is a mode where each standalone statement commits as its own transaction.

What is a savepoint?

It is a marker that permits partial rollback within an open transaction.

What causes a lost update?

Concurrent read-modify-write operations overwrite one another without locking, atomic updates, or version checks.

How should deadlocks be handled?

Reduce their causes and retry the complete aborted transaction using bounded backoff.

Can a SQL transaction include an external API?

Not as an ordinary local atomic transaction; distributed coordination or compensation is required.

Practical Exercises

  1. Implement a transfer that verifies both affected rows.
  2. Add an idempotency key and unique constraint to transfers.
  3. Demonstrate a lost update and fix it with an atomic statement.
  4. Use a savepoint for an optional order note.
  5. Compare repeated reads under two isolation levels.
  6. Create and diagnose a deadlock in a test environment.
  7. Design a retry policy for serialization failures.
  8. Separate remote payment from an order database transaction.
  9. Implement an outbox row in the order transaction.
  10. Define metrics and alerts for long-running transactions.

Quick Reference

ConceptMeaning
TransactionOne logical unit of database work
CommitAccepts the transaction's changes
RollbackCancels uncommitted changes
SavepointInternal rollback marker
IsolationControls concurrent interaction
DeadlockCyclic lock wait resolved by aborting a participant
IdempotencyRepeated request does not repeat the business effect
OutboxBusiness update and event record committed together

Complete Transaction Workflow

  1. Define the business invariant and transaction boundary.
  2. Select isolation and concurrency controls from actual anomalies.
  3. Begin the transaction on one managed connection.
  4. Read or lock only the rows required for validation.
  5. Execute related writes using constraints and atomic SQL.
  6. Verify affected-row counts and final business conditions.
  7. Write any required outbox event before commit.
  8. Commit when all required work succeeds.
  9. Roll back and clean up after any failure.
  10. Retry only classified transient errors with idempotency.
  11. Resolve unknown outcomes through status lookup.
  12. Monitor duration, locks, deadlocks, retries, and drift.

Key Takeaway

A SQL transaction protects a logical database operation by giving it a defined commit or rollback boundary. ACID properties, constraints, isolation, locks, and recovery mechanisms make that boundary reliable, but they do not replace correct business rules or application error handling.

Production transaction design keeps work short, verifies every assumption, handles concurrency deliberately, and treats retries and unknown outcomes as normal possibilities. When a workflow spans databases or external services, local transactions must be combined with idempotency, durable events, and compensation. The result is not merely valid SQL; it is dependable business behavior under failure and concurrency.