Transaction and Concurrency in Databases

Database transactions allow multiple related operations to behave as one logical unit, while concurrency control determines what happens when many transactions access the same data at the same time.

These concepts are closely connected. ACID properties define important transaction guarantees, the transaction lifecycle determines whether work commits or rolls back, and optimistic and pessimistic locking provide different strategies for handling concurrent modifications.

Table of Contents

• 1. ACID Properties
  • Atomicity
  • Consistency
  • Isolation
  • Durability
• 2. Database Transaction
  • Transaction Lifecycle
  • COMMIT and ROLLBACK
  • Transaction Boundaries
• 3. Optimistic Locking
  • Version-Based Optimistic Locking
  • Handling Optimistic Conflicts
• 4. Pessimistic Locking
  • Row-Level Locking
  • Lock Contention
• Optimistic vs Pessimistic Locking
• Transaction Isolation Levels
  • Read Committed
  • Repeatable Read
  • Serializable
• MVCC and Concurrency
• Deadlocks
• Production Design Example
• Common Transaction and Concurrency Mistakes
• Production Checklist
• Frequently Asked Questions
  • Does a Transaction Lock the Entire Table?
  • Does MVCC Eliminate Locks?
  • When Should Optimistic Locking Be Used?
  • When Should Pessimistic Locking Be Used?
  • Should a Deadlocked Transaction Be Retried?
• Conclusion
  • More Articles to Read

1. ACID Properties

ACID describes four important properties associated with database transactions: Atomicity, Consistency, Isolation, and Durability.

Consider a transfer of $20 between two accounts:

BEGIN;

UPDATE accounts
SET balance = balance - 20
WHERE id = 1;

UPDATE accounts
SET balance = balance + 20
WHERE id = 2;

COMMIT;

The two updates represent one logical operation. A correct transactional system should not leave the database with money removed from the first account but never added to the second because the process failed halfway through.

Atomicity

Atomicity means the transaction's changes are treated as one unit: the transaction succeeds as a whole or its incomplete changes are not committed as the final result.

Initial State
     ↓
Transaction starts
     ↓
Intermediate State
     ↓
 ┌───┴────┐
 │        │
Success  Failure
 │        │
 ↓        ↓
COMMIT   ROLLBACK
 │        │
 ↓        ↓
Final    Original
State    State

Suppose the first update succeeds:

Account A: 40 → 20

but the second update fails because of a constraint, application error, deadlock, or database failure.

Atomicity prevents the transaction from committing only the first half of the transfer.

Consistency

Consistency means a transaction should move the database from one valid state to another while respecting the rules enforced by the database and the transaction's application logic.

Database-level consistency can include:

• primary keys;
• foreign keys;
• unique constraints;
• CHECK constraints;
• NOT NULL constraints;
• data types.

For example:

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    balance_cents BIGINT NOT NULL,
    version BIGINT NOT NULL,
    CHECK (balance_cents >= 0)
);

The database can reject a transaction that would violate the non-negative balance constraint.

ACID consistency does not mean that the database automatically understands every business invariant. If total debits must equal total credits, the schema and application transaction must enforce that rule correctly.

Isolation

Isolation controls how concurrent transactions can observe and interfere with each other's work.

Suppose two transactions read the same account:

Initial balance = 40

Transaction A             Transaction B
     │                          │
READ 40                     READ 40
     │                          │
subtract 20                 subtract 20
     │                          │
WRITE 20                    WRITE 20

If concurrency is handled incorrectly, both operations can appear successful even though one update effectively overwrote the effect of the other.

This is a lost update style of concurrency problem.

Isolation mechanisms include MVCC, row locks, predicate locks, serialization checks, and database-specific concurrency-control algorithms.

Durability

Durability means that after a transaction is successfully committed according to the database's durability configuration, its committed state should survive failures covered by that durability guarantee.

Transaction
    ↓
COMMIT
    ↓
Durable state
    ↓
Database process crashes
    ↓
Restart + recovery
    ↓
Committed state preserved

Databases commonly use transaction logs to provide this property.

For example, PostgreSQL uses write-ahead logging so recovery information can become durable before modified table pages must be written to their final locations. See What Is Write-Ahead Logging? for the complete durability path.

2. Database Transaction

A database transaction groups one or more database operations into a logical unit of work.

A transaction usually starts explicitly or implicitly, performs reads and writes, and eventually commits or aborts.

BEGIN
  ↓
ACTIVE
  ↓
Read / Write
  ↓
Commit requested
  ↓
COMMITTED
  ↓
TERMINATED

If execution fails:

ACTIVE
  ↓
Error / Abort
  ↓
FAILED
  ↓
ROLLBACK
  ↓
TERMINATED

Transaction Lifecycle

A simplified transaction lifecycle contains several conceptual states.

Real database engines implement these details differently, but the important application-level distinction is simple: work is either successfully committed or the transaction must be treated as unsuccessful.

COMMIT and ROLLBACK

COMMIT tells the database to complete the transaction:

BEGIN;

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

INSERT INTO orders (
    customer_id,
    product_id,
    quantity
)
VALUES (100, 42, 1);

COMMIT;

ROLLBACK abandons the transaction's uncommitted work:

BEGIN;

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

-- something fails

ROLLBACK;

Applications should also handle exceptions so a failed operation does not accidentally leave a transaction open.

try:
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(
                """
                UPDATE accounts
                SET balance_cents = balance_cents - %s
                WHERE id = %s
                """,
                (2000, account_id),
            )
except Exception:
    raise

Structured transaction management is preferable to scattered manual commit and rollback calls because cleanup is easier to reason about.

Transaction Boundaries

Choosing what belongs inside a transaction is a major concurrency decision.

A problematic workflow might look like:

BEGIN
  ↓
Read account
  ↓
Lock row
  ↓
Call external payment API
  ↓
Wait 2 seconds
  ↓
Update account
  ↓
COMMIT

The database transaction remains open while waiting on a remote system.

This can hold locks, connections, snapshots, and other resources unnecessarily.

A better design often separates slow external work from the shortest database-critical section that actually requires atomicity.

Transactions should therefore be as short as correctness allows, not simply as large as an application request.

3. Optimistic Locking

Optimistic locking assumes concurrent conflicts are uncommon enough that transactions can proceed without holding an exclusive database lock for the entire business operation.

Before writing, the operation verifies that the data has not changed since it was read.

Consider an account:

account_id = 1
balance    = 40
version    = 1

Sarah and John both read Version 1:

Sarah                    John
  │                        │
  └──── READ version 1 ─────┘

John updates first:

balance: 40 → 20
version:  1 → 2

Sarah later attempts an update that still expects Version 1.

The database rejects the stale condition because Version 1 is no longer current.

Version-Based Optimistic Locking

A common implementation adds a version column:

SELECT id, balance_cents, version
FROM accounts
WHERE id = 1;

The application receives:

id            = 1
balance_cents = 4000
version       = 7

The update includes the version that was originally read:

UPDATE accounts
SET balance_cents = 2000,
    version = version + 1
WHERE id = 1
  AND version = 7;

If nobody changed the row, one row is updated.

Rows affected = 1
→ success

If another transaction already changed it:

Current version = 8

UPDATE ... WHERE version = 7

Rows affected = 0
→ concurrency conflict

No long-lived exclusive lock is required between the original read and the attempted update.

Handling Optimistic Conflicts

A zero-row update is not necessarily a database failure. It is a concurrency outcome that the application must handle.

Possible strategies include:

• reload the latest state and ask the operation to retry;
• automatically retry when the business operation is safely repeatable;
• merge non-conflicting changes;
• return a conflict response to the caller;
• abandon the operation.

Automatic retries require care.

Suppose a transaction also charges an external payment provider. Blindly rerunning the entire workflow can create duplicate side effects unless the external operation is idempotent.

Optimistic locking therefore detects a conflict; it does not automatically define the correct business response to that conflict.

4. Pessimistic Locking

Pessimistic locking assumes a conflict is important or likely enough that access should be coordinated before the protected operation proceeds.

A transaction can lock a row before changing it:

BEGIN;

SELECT id, balance_cents
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance_cents = balance_cents - 2000
WHERE id = 1;

COMMIT;

While the row lock is held, another transaction attempting a conflicting lock or update normally has to wait.

Sarah                     John
  │                         │
LOCK account 1              │
  │                         │
  │                    request lock
  │                         │
  │                       WAIT
  │                         │
UPDATE                      │
COMMIT                      │
  │                         │
release lock ───────────────→
                            │
                         continue

This prevents both operations from independently treating the same row as exclusively available.

Row-Level Locking

Fine-grained row locks are generally preferable to unnecessarily broad locks because unrelated transactions can continue operating on different rows.

Transaction A → locks account 1
Transaction B → locks account 2
Transaction C → locks account 3

All can proceed concurrently

But many requests targeting the same row create a hotspot:

Transaction A ─┐
Transaction B ─┤
Transaction C ─┼→ account 1
Transaction D ─┤
Transaction E ─┘

Even a powerful database cannot execute mutually exclusive modifications to one hot resource with unlimited parallelism.

Lock Contention

Pessimistic locking trades conflict detection for waiting.

If a transaction holds a lock for 5 ms, contention may be insignificant. If it holds the lock for five seconds, many requests can accumulate behind it.

Lock holder
    ↓
5-second transaction
    ↓
waiter
waiter
waiter
waiter
waiter

Important lock-contention metrics include:

• lock wait duration;
• number of waiting transactions;
• transaction duration;
• deadlock count;
• lock timeout count;
• hot rows and tables.

A broader introduction to lock types and transactional behavior is available in Database Locks and Transactions.

Optimistic vs Pessimistic Locking

Neither strategy is universally better. They optimize for different conflict patterns.

Optimistic locking works well for many CRUD applications where users frequently read data but rarely modify the exact same record simultaneously.

Pessimistic locking is useful when concurrent operations on the same resource are common and proceeding from stale state would cause expensive conflicts.

The decision should be based on:

• conflict frequency;
• transaction duration;
• cost of retrying;
• business invariants;
• hotspot behavior;
• latency requirements.

Transaction Isolation Levels

Locking strategy is only one part of concurrency control. The transaction's isolation level determines which concurrent changes can become visible and which anomalies the database prevents.

SQL databases commonly expose levels such as Read Committed, Repeatable Read, and Serializable, although exact behavior differs between database engines.

Read Committed

Under PostgreSQL Read Committed, each statement sees data committed before that statement begins.

Transaction A                Transaction B

SELECT balance
→ 40

                             UPDATE → 20
                             COMMIT

SELECT balance
→ 20

Two reads inside the same transaction can therefore observe different committed states.

Repeatable Read

PostgreSQL Repeatable Read provides a stable transaction snapshot for ordinary reads.

Transaction A                Transaction B

SELECT balance
→ 40

                             UPDATE → 20
                             COMMIT

SELECT balance
→ 40

The second read continues to see the version appropriate for Transaction A's snapshot.

Serializable

Serializable isolation provides the strongest isolation semantics of these common levels by requiring concurrent execution to be equivalent to some valid serial execution.

This does not mean every transaction simply waits.

A database can detect that concurrent operations cannot safely be serialized and abort one transaction:

Transaction A ─┐
               ├→ unsafe serialization
Transaction B ─┘
                    ↓
             one transaction aborts
                    ↓
                  retry

Applications using Serializable isolation should therefore have a deliberate retry strategy for serialization failures.

MVCC and Concurrency

Many relational databases use Multi-Version Concurrency Control (MVCC) to reduce unnecessary blocking between readers and writers.

Instead of immediately destroying an old row state when a row changes, the database can maintain multiple row versions:

Version 1
balance = 40

Version 2
balance = 20

An older transaction can continue reading Version 1 while a newer transaction works with Version 2 according to the database's visibility rules.

Reader A ─────→ Version 1

Writer B ─────→ Version 2

This improves concurrency because ordinary readers do not always need to block writers.

MVCC does not eliminate write conflicts. Two writers trying to modify the same logical row still require coordination.

Snapshots, row versions, PostgreSQL tuple visibility, VACUUM, and long-running transaction behavior are covered in What Is MVCC?.

Deadlocks

A deadlock occurs when transactions wait on each other in a cycle.

For example:

Transaction A
locks Account 1
      ↓
needs Account 2
      ↓
     WAIT
      ↑
needs Account 1
      ↑
Transaction B
locks Account 2

Neither transaction can continue unless one is aborted.

Database engines normally detect deadlocks and choose a transaction to terminate so the other can proceed.

A common prevention strategy is to lock resources in a consistent order.

Instead of:

Transaction A: Account 1 → Account 2
Transaction B: Account 2 → Account 1

use:

Transaction A: Account 1 → Account 2
Transaction B: Account 1 → Account 2

Consistent ordering does not eliminate every possible deadlock, but it removes many common cyclic lock patterns.

Applications should also treat deadlock errors as expected concurrency outcomes rather than impossible database failures. When the operation is safe to repeat, retrying the complete transaction is often appropriate.

Production Design Example

Consider a ticketing platform selling seats for a popular event.

The inventory table contains:

seat_id       = 4201
event_id      = 100
status        = available
version       = 18

Thousands of customers can attempt to reserve seats concurrently.

For ordinary profile editing, optimistic locking works well because two users rarely modify the same profile at exactly the same time:

UPDATE customer_profiles
SET display_name = $1,
    version = version + 1
WHERE customer_id = $2
  AND version = $3;

For a scarce seat, conflicts are much more likely.

The reservation transaction uses a row lock:

BEGIN;

SELECT status
FROM seats
WHERE seat_id = $1
FOR UPDATE;

UPDATE seats
SET status = 'reserved',
    reservation_id = $2
WHERE seat_id = $1
  AND status = 'available';

INSERT INTO reservations (
    id,
    seat_id,
    customer_id,
    status
)
VALUES ($2, $1, $3, 'pending');

COMMIT;

The transaction intentionally remains small.

Payment processing is not performed while the seat row is locked:

Bad:

BEGIN
  ↓
Lock seat
  ↓
Call payment provider
  ↓
Wait 3 seconds
  ↓
COMMIT


Better:

Short reservation transaction
  ↓
COMMIT
  ↓
Payment workflow
  ↓
Confirm or release reservation
using explicit business rules

The second design requires a reservation-expiration workflow, but it avoids holding a hot database lock across an unpredictable network call.

Production monitoring shows:

Normal:
transaction p95       = 12 ms
lock wait p95         = 3 ms
deadlocks             = near zero

Popular event launch:
transaction p95       = 480 ms
lock wait p95         = 440 ms
hot seat contention   = high

The database CPU may still be moderate. The bottleneck is serialization around scarce rows rather than raw compute capacity.

Adding more application instances would not solve this constraint and could increase the number of transactions waiting for the same rows.

The production design therefore monitors:

• transaction latency;
• transaction duration;
• lock wait time;
• deadlocks;
• serialization failures;
• optimistic conflict rate;
• retry rate;
• connection utilization;
• hot rows and resources;
• aborted transactions.

The important lesson is that database concurrency capacity is not only a CPU or connection problem. A workload can become serialized around a small number of contested resources.

Common Transaction and Concurrency Mistakes

• Reading a value and later updating it without concurrency protection. Another transaction may change the row between the two operations.
• Assuming transactions eliminate all race conditions. Correctness depends on isolation, predicates, locks, constraints, and application logic.
• Keeping transactions open during external API calls. Locks, snapshots, and connections can remain occupied while waiting on the network.
• Using pessimistic locking for every update. Unnecessary locks reduce concurrency.
• Using optimistic locking where conflicts are constant. The system can spend excessive resources retrying work.
• Ignoring zero-row optimistic updates. They can represent real concurrency conflicts.
• Retrying non-idempotent side effects blindly. A database retry can duplicate external actions.
• Updating resources in inconsistent order. This increases deadlock risk.
• Assuming MVCC means no locks. Writer conflicts and many other database operations still require synchronization.
• Increasing the connection pool to solve lock waits. More waiting transactions can make contention worse.
• Using the strongest isolation level without understanding retries. Stronger guarantees can introduce additional coordination or transaction aborts.
• Using a transaction for an entire application request by default. Transaction scope should match the atomic database operation.

Production Checklist

• Define the business invariant each transaction protects.
• Keep transactions as short as correctness permits.
• Avoid remote network calls while database locks are held.
• Use database constraints to enforce invariants where possible.
• Choose an isolation level intentionally.
• Use optimistic locking when conflicts are uncommon and detectable.
• Use pessimistic locking when contested resources require serialization.
• Check affected row counts for optimistic updates.
• Define bounded retry behavior for retryable concurrency failures.
• Make retried workflows safe from duplicate external side effects.
• Acquire multiple locks in a consistent order where possible.
• Set transaction and lock timeouts appropriate for the workload.
• Monitor long-running and idle transactions.
• Monitor lock waits and deadlocks.
• Monitor serialization and optimistic concurrency failures.
• Identify hot rows instead of assuming the database needs more hardware.
• Load-test realistic concurrent access to the same resources.

Frequently Asked Questions

Transactions and concurrency controls work together, but they solve different parts of database correctness. Transaction boundaries define the unit of work, while isolation, MVCC, locks, constraints, and conflict detection determine how that work interacts with concurrent operations.

Does a Transaction Lock the Entire Table?

Not necessarily. Modern relational databases use multiple lock types and granularities. An update can acquire row-level locks while allowing unrelated rows to be modified concurrently.

Some operations can acquire stronger table-level or schema-related locks, so actual lock behavior depends on the statement and database engine.

Does MVCC Eliminate Locks?

No. MVCC mainly reduces unnecessary blocking between readers and writers by allowing transactions to see appropriate row versions.

Concurrent writers, explicit locking operations, schema changes, and other database activities can still require locks.

When Should Optimistic Locking Be Used?

Optimistic locking works well when conflicts are possible but relatively uncommon and when detecting a conflict after work has started is acceptable.

It is particularly useful when a record can remain visible to a user for a long time before an update is submitted, because holding a database lock throughout that period would be impractical.

When Should Pessimistic Locking Be Used?

Pessimistic locking is useful when concurrent access to the same resource is likely and the operation needs to establish exclusive or otherwise coordinated access before proceeding.

The protected transaction should remain short because every additional millisecond of lock ownership can increase waiting under contention.

Should a Deadlocked Transaction Be Retried?

Often, yes, when the entire transaction is safe to repeat. A deadlock victim is aborted so another transaction can proceed, and retrying later may succeed because the conflicting execution pattern has changed.

Retries should be bounded and should not blindly repeat external non-idempotent side effects.

Conclusion

Transactions provide a boundary around related database operations, while concurrency control protects that work when many transactions operate simultaneously. ACID describes the essential transaction properties, and commit or rollback determines whether the unit of work becomes part of the database state.

Optimistic locking detects concurrent changes and works well when conflicts are relatively rare. Pessimistic locking coordinates access before modification and is useful when conflicts are expected. Isolation levels, MVCC, constraints, deadlock handling, and short transaction boundaries complete the concurrency model.

The core principle is: define the invariant that must remain correct, keep the transaction boundary small, and choose the least restrictive concurrency mechanism that reliably protects that invariant.

More Articles to Read

• Database Transactions
• Database Best Practices for Scalable Applications
• Advanced PostgreSQL Tricks for Production Systems