PostgreSQL Row-Level Locking Explained Through a Real Enrollment System

At first glance, student enrollment looks like a simple CRUD operation.

That implementation works perfectly when one user makes one request at a time. Production systems don’t work that way.

Two users can click Enroll at almost exactly the same moment. If the system is not designed for concurrency, both requests can observe the same available seat and both can successfully enroll. That is a classic race condition.

For an EdTech platform with capacity-limited cohorts, I needed the database itself to protect the business invariant:

1. The Enrollment Problem

Imagine a cohort with:

Now two students submit enrollment requests simultaneously.

2. The Naive Implementation

A naive service might look like:

const cohort = await cohortRepository.findOne({
  where: { id: cohortId },
});

if (cohort.availableSeats <= 0) {
  throw new Error('Cohort is full');
}

await enrollmentRepository.save({ userId, cohortId });

cohort.availableSeats -= 1;
await cohortRepository.save(cohort);

The code looks reasonable. The problem is the gap between the READ and the WRITE. Another request can execute during that gap.

3. Understanding the Race Condition

Consider this timeline:

4. The Invariant

Before designing the transaction, define what must never become false.

5. Row-Level Locking

PostgreSQL provides row-level locking. The key operation is:

SELECT *
FROM cohorts
WHERE id = $1
FOR UPDATE;

FOR UPDATE tells PostgreSQL: lock the selected row so that other transactions attempting conflicting modifications must wait until this transaction completes.

6. The Correct Transaction

The entire critical operation is performed inside one database transaction.

7. What Happens With Two Concurrent Requests?

Available seats = 1

The invariant survives.

8. The Important Part: Lock Before Checking

9. TypeORM Implementation

In a NestJS application using TypeORM, the transaction is controlled through a QueryRunner:

const queryRunner = dataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  const cohort = await queryRunner.manager
    .createQueryBuilder(Cohort, 'cohort')
    .setLock('pessimistic_write')
    .where('cohort.id = :id', { id: cohortId })
    .getOne();

  if (!cohort) throw new Error('Cohort not found');
  if (cohort.availableSeats <= 0) throw new Error('Cohort is full');

  const enrollment = queryRunner.manager.create(Enrollment, {
    userId,
    cohortId,
  });
  await queryRunner.manager.save(enrollment);

  cohort.availableSeats -= 1;
  await queryRunner.manager.save(cohort);

  await queryRunner.commitTransaction();
  return enrollment;
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}

The important pattern is: Transaction + Pessimistic write lock + Capacity check + Enrollment + Seat update + Commit.

10. Why pessimistic_write?

TypeORM’s pessimistic_write maps to a database-level SELECT … FOR UPDATE. The application is telling PostgreSQL: “I am about to make a decision based on this row, and I need this row protected until my transaction finishes.”

11. Why Not Just Use Optimistic Locking?

Optimistic concurrency (version column + conditional update) is a valid strategy and works extremely well in many workloads. Enrollment, however, has a particularly clear contention point: one cohort row with a limited number of seats.

When multiple users compete for the same scarce resource, explicit locking can be a straightforward and predictable solution. The correct choice depends on the workload and contention characteristics.

12. Database Constraints Are Still Necessary

Row locking solves the capacity race. It does not automatically prevent the same student from enrolling twice. Enforce a unique constraint:

UNIQUE (user_id, cohort_id)

Application-level checks can also race. A database unique constraint closes that hole.

13. Defense in Depth

14. Keep the Transaction Small

15. Event-Driven Downstream Processing

Once enrollment succeeds, other parts of the system can react asynchronously:

16–17. Rollback & Crash Safety

If the enrollment insert fails, the transaction rolls back and the seat is not consumed. If the application crashes before COMMIT, PostgreSQL does not treat the incomplete transaction as successfully committed. Critical state changes should always be handled transactionally.

18. Lock Contention

If many users try to enroll in the same cohort simultaneously, they contend for the same row lock. That is expected. The goal is not to eliminate contention completely — it is to ensure that contention produces correct behavior.

19. Why Row-Level Instead of Table Locking?

A table-level lock would be unnecessarily aggressive. Enrolling in Cohort A should not block enrollment into Cohort B, C, or D. Row-level locking provides much finer-grained concurrency.

20. The Real Business Invariant

The most important part of this architecture isn’t FOR UPDATE. It is identifying the invariant first:

Once that is clear, the architecture follows:

21. Common Mistakes

22. The Complete Architecture

Client
  │
  ▼
Cohort Service
  │
  ▼
Begin Transaction
  │
  ▼
Lock Cohort Row (FOR UPDATE)
  │
  ▼
Check Capacity
  │
  ├── Full → ROLLBACK
  │
  ▼
Create Enrollment
  │
  ▼
Decrease Seats
  │
  ▼
COMMIT
  │
  ▼
Kafka Event
  │
  ├── Calendar
  ├── Notification
  └── Analytics

23. The Result

24. When Should You Use Row-Level Locking?

Particularly useful when multiple requests compete to modify the same scarce resource:

It should not be the default answer to every concurrency problem. Locking creates waiting; waiting creates contention. The correct approach depends on transaction duration, contention level, workload, consistency requirements, acceptable latency, and failure behavior.

25. The Broader Lesson

Concurrency bugs are difficult because they often disappear during local development. A system that works sequentially is not necessarily a system that works concurrently. Concurrency needs to be designed explicitly.

Final Takeaway