PostgreSQL Locks & Spring JPA Locks

 · 

I recently spent some focused time reading PostgreSQL’s concurrency and locking documentation, and then mapping that understanding to how locks actually work in Spring JPA / Hibernate.

This is not a reference manual.
It’s a mental model — the kind that helps avoid race conditions, deadlocks, and subtle production bugs. (with the help with AI)

Why Locks Exist (Even with MVCC)

MVCC (Multi-Version Concurrency Control) allows concurrent reads and writes by maintaining multiple versions of data, giving each transaction a consistent snapshot of the database. Instead of blocking readers, writers create new row versions while readers continue to see older committed versions. This reduces read–write contention and improves concurrency, at the cost of additional storage and background cleanup (e.g., PostgreSQL’s VACUUM).

How it works

PostgreSQL uses MVCC, which already gives:

Locks exist to protect:

Locks answer a different question:

Who is allowed to touch this object right now, and in what way?

Table-Level Locks (Structure & Lifetime)

ACCESS SHARE (normal SELECT)

This is why normal SELECT#1: “ACCESS SHARE is a lock… just not a blocking one”

A normal SELECT still takes a table lock:

It usually feels like “no lock” because it conflicts with almost nothing. But it does protect you from destructive operations (e.g., DROP TABLE, TRUNCATE, some ALTER TABLE) while the query is running.

ACCESS SHARE is a coordination lock (“don’t destroy the building while I’m inside”), not a contention lock (“nobody else may enter”). s feel “lock-free” — they are coordination locks, not contention locks.

ROW EXCLUSIVE (INSERT / UPDATE / DELETE)

This is the default write lock in PostgreSQL.

SHARE (CREATE INDEX)

CREATE INDEX (non-concurrent) is a production foot-gun

Safer alternative

Use:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

This uses SHARE UPDATE EXCLUSIVE instead of SHARE — it is designed to allow ongoing reads/writes while preventing incompatible schema changes.

Rule of thumb:

Big hot table in production? Default to CREATE INDEX CONCURRENTLY.

ACCESS EXCLUSIVE (DROP / TRUNCATE / VACUUM FULL)

Only ACCESS EXCLUSIVE blocks normal SELECT.

Row-Level Locks (Business Ownership)

Row locks:

FOR UPDATE (strongest)

SELECT * FROM orders WHERE id = 123 FOR UPDATE;

Use this for:

SELECT ... FOR UPDATE does not show stale rows — it waits

I had this wrong at first:

“If one session locks a row, can another session still read the old unpaid version?”

Answer:

This is exactly why FOR UPDATE is a good primitive for “check-then-update” workflows.

Example: The safe “unpaid → paid” pattern

For a payment/order state transition, the safe pattern is:

BEGIN;

SELECT status
FROM orders
WHERE id = 123
FOR UPDATE;

-- decision point is now safe
UPDATE orders SET status = 'paid'
WHERE id = 123 AND status = 'unpaid';

COMMIT;

What this buys you:

FOR NO KEY UPDATE (subtle, weaker)

SELECT * FROM orders WHERE id = 123 FOR NO KEY UPDATE;

Meaning:

I will update this row, but I promise not to change its identity (PK / FK).

Key insight:

A surprising case

While one transaction holds FOR NO KEY UPDATE on an order row:

Rule:

Use FOR UPDATE unless weaker semantics are fully understood.

Page-Level Locks (Engine Internals)

These exist for engine safety, not concurrency control.
Ignore them for application design.

Spring JPA / Hibernate Lock Mapping

JPA expresses intent.
PostgreSQL enforces reality.

JPA Lock PostgreSQL
NONE no row lock
PESSIMISTIC_READ SELECT … FOR SHARE
PESSIMISTIC_WRITE SELECT … FOR UPDATE

There is no JPA equivalent for FOR NO KEY UPDATE.

Important Spring gotcha

This does not lock anything:

@Transactional
Order order = repo.findById(id);

Unless a pessimistic lock is explicitly requested.

Correct order-payment pattern in Spring

@Transactional
@Lock(LockModeType.PESSIMISTIC_WRITE)
public void payOrder(Long orderId) {
    Order order = orderRepository.findById(orderId).orElseThrow();
    if (order.isUnpaid()) {
        order.markPaid();
    }
}

Maps to:

SELECT ... FOR UPDATE;

Second transaction blocks.
No double pay.
No race condition.

Note #1: Table locks are about table stability, row locks are about business ownership

I kept mixing these until it clicked:

And a statement can take both:

Note #2: VACUUM FULL is a full stop

Another operational reality:

So:

Never run VACUUM FULL on a hot table unless you have a maintenance window.

Prefer:

Final cheat sheet (what I want to remember)

Clarity beats cleverness.

Takeaway