🔒 Concurrency Control in PostgreSQL 16 with Spring Boot JdbcTemplate — A Practical Tutorial

Concurrency control is a crucial concern for enterprise apps, especially when using PostgreSQL’s FOR UPDATE locking with Spring Boot JdbcTemplate for safe, transactional updates.

PostgreSQL offers powerful row-level locking mechanisms like SELECT … FOR UPDATE to help prevent dirty writes, lost updates, and race conditions. When combined with Spring Boot’s transaction management and **JdbcTemplate**, this becomes an elegant solution for safely handling concurrent updates.

🧠 1. Understanding FOR UPDATE

The FOR UPDATE clause in PostgreSQL locks selected rows so that:

• Other transactions cannot update or delete these rows until the current transaction completes.
• Other transactions attempting to SELECT … FOR UPDATE on the same rows will wait (or fail if NOWAIT is used).

Syntax

SELECT * FROM account WHERE account_id = 1001 FOR UPDATE;

This locks the row for the current transaction until it’s committed or rolled back.

🚦2. Common Use Cases

1. Banking / Financial Transactions — Prevent two transactions from updating the same account balance simultaneously.
2. Inventory Management — Ensure stock levels aren’t oversold.
3. Crew / Flight Assignment Systems — Prevent assigning the same crew or aircraft twice in parallel updates.

In Spring, the @Transactional annotation marks a method (or class) so that all database calls within it run in one transaction. What this gives you:

✔ Atomicity — either all the operations inside succeed, ✔ Rollback on error — if an exception occurs anywhere inside, all changes are undone so the database stays consistent.

This works with JdbcTemplate too — behind the scenes Spring wraps the method in JDBC transaction logic: it disables auto-commit, then commits on success or rolls back on failure.

When you lock rows withFOR UPDATE, validate and update them, you want that sequence to be atomic — that’s exactly what @Transactional gives you.

👉 FOR UPDATE — Lock rows for safe updates

*SELECT ... FOR UPDATE tells PostgreSQL to lock the rows you select so no other transaction can modify them until you finish your transaction. That means:*

• If Transaction A runs FOR UPDATE on a row, Transaction B must wait before it can update that row.
• This prevents situations where two threads read stale data and then write conflicting updates.
• You typically use this before doing an update so that no one else can sneak in changes mid-transaction.

📌 Use case: Lock a flight record before assigning crew or updating aircraft details so that no two processes can step on each other.

SKIP LOCKED and NOWAIT give you different ways to handle contention depending on whether you want to wait, skip, or fail fast.

👉 SKIP LOCKED — Ideal for multi-threaded/distributed queues

SKIP LOCKED is an extension you add to FOR UPDATE like:

SELECT * FROM jobs WHERE status='PENDING' FOR UPDATE SKIP LOCKED;

What it does:

• If another transaction has already locked a row, this query skips that row instead of waiting.
• That lets multiple workers pull distinct tasks from a queue without blocking on the same locked rows.

📌 Use case: In a pool of workers that assign flights/crew, you can have each worker pick the next unlocked job instead of waiting on locked ones.

👉 5) NOWAIT — Fail fast if lock can’t be acquired

With NOWAIT, you tell PostgreSQL: “If the row is locked, don’t wait — just fail now.” For example:

SELECT * FROM flight WHERE id=? FOR UPDATE NOWAIT;

• If another transaction has the lock, you get an immediate error instead of waiting.
• This is useful in UI real-time environments where waiting is unacceptable and you want to report lock contention immediately.

📌 Use case: A dispatcher tries to assign crew to a flight; if someone else is updating it, you immediately return a conflict message to the user.

⚙️ 4. Spring Boot + PostgreSQL Setup

Let’s simulate a bank account transfer scenario using **FOR UPDATE** for concurrency-safe debit/credit operations.

🧾 Example Table

CREATE TABLE account (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    balance NUMERIC(15, 2)
);

Sample Data:

INSERT INTO account (name, balance) VALUES 
('Alice', 1000.00),
('Bob', 500.00);

🧠 5. Spring Boot Application Setup

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>
</dependencies>

Application Properties

spring.datasource.url=jdbc:postgresql://localhost:5432/demo
spring.datasource.username=postgres
spring.datasource.password=admin
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.open-in-view=false

💡 6. Transactional Service using FOR UPDATE

Here’s where the magic happens.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.sql.ResultSet;
@Service
public class AccountService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Transactional
    public void transferMoney(long fromId, long toId, BigDecimal amount) {
        // Lock both rows
        String sql = "SELECT * FROM account WHERE id IN (?, ?) FOR UPDATE";
        var accounts = jdbcTemplate.query(sql, new Object[]{fromId, toId},
                (ResultSet rs, int rowNum) -> new Account(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getBigDecimal("balance")
                )
        );
        Account from = accounts.stream().filter(a -> a.getId() == fromId).findFirst().orElseThrow();
        Account to = accounts.stream().filter(a -> a.getId() == toId).findFirst().orElseThrow();
        if (from.getBalance().compareTo(amount) < 0) {
            throw new IllegalStateException("Insufficient balance");
        }
        BigDecimal newFromBalance = from.getBalance().subtract(amount);
        BigDecimal newToBalance = to.getBalance().add(amount);
        jdbcTemplate.update("UPDATE account SET balance=? WHERE id=?", newFromBalance, fromId);
        jdbcTemplate.update("UPDATE account SET balance=? WHERE id=?", newToBalance, toId);
        System.out.println("Transferred " + amount + " from " + from.getName() + " to " + to.getName());
    }
}

POJO

public class Account {
    private long id;
    private String name;
    private BigDecimal balance;
    public Account(long id, String name, BigDecimal balance) {
        this.id = id;
        this.name = name;
        this.balance = balance;
    }
    // getters and setters
}

🔍 7. What Happens Internally

• When SELECT ... FOR UPDATE runs, PostgreSQL locks the rows for both accounts.
• Another concurrent transfer involving either account will wait until this transaction commits or rolls back.
• Because the method is annotated with@Transactional, the entire operation (read → business logic → update) is atomic.

This prevents lost updates even under high concurrency.

🧱 8. Using NOWAIT or SKIP LOCKED for Job Queues

Let’s say we have a queue table, and multiple workers are polling for unprocessed jobs.

SELECT * FROM job_queue WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED LIMIT 1;

This allows one worker to pick an unlocked job, while others skip over locked ones — a perfect pattern for distributed workers.

🧰 9. Handling Lock Timeouts

PostgreSQL allows setting lock timeouts at runtime:

SET lock_timeout = '5s';

In Spring:

jdbcTemplate.execute("SET LOCAL lock_timeout = '5s'");

If a lock cannot be acquired within the timeout, PostgreSQL throws an error — you can catch this to retry or log it.

⚡ 10. Testing Concurrency Behavior

You can test concurrency by running two threads:

Runnable task1 = () -> accountService.transferMoney(1L, 2L, new BigDecimal("100"));
Runnable task2 = () -> accountService.transferMoney(1L, 3L, new BigDecimal("100"));
new Thread(task1).start();
new Thread(task2).start();

Only one transaction can lock the account 1 at a time — the second will wait until the first completes.

🧩 11. Advanced Pattern: Partial Locking with FOR UPDATE OF

If you join multiple tables and want to lock only one of them:

SELECT a.*, c.*
FROM account a
JOIN customer c ON a.customer_id = c.id
FOR UPDATE OF a;

This locks rows only in account, not customer.

✅ 12. Key Takeaways

ConceptUseFOR UPDATELock rows for safe updates@TransactionalEnsure atomicity and rollback on errorJdbcTemplateGives fine-grained control over SQL and lockingSKIP LOCKEDIdeal for multi-threaded or distributed worker queuesNOWAITFail fast if lock can’t be acquired

Conclusion

SELECT … FOR UPDATE is a powerful mechanism for pessimistic locking in PostgreSQL. When used with Spring Boot’s transaction management and JdbcTemplate, it ensures:

• Data consistency under concurrent transactions
• Deadlock-free, safe updates
• Predictable transactional behavior

It’s especially valuable for financial, inventory, or real-time crew/aircraft assignment systems — where correctness is far more important than raw throughput.