Why the 101st Waitlist User Never Gets Skipped: Atomic Promotion with MySQL Row Locks

Problem: The Real Challenge Is Cancellation, Not Overselling

When building a limited-capacity event signup (e.g., 100 seats), preventing overselling is straightforward: decrement a counter inside a transaction with a conditional update. The subtle bug appears when a confirmed attendee cancels and a waitlisted user must be promoted. If the system releases the seat first, then asynchronously promotes the next waitlisted user, a race window opens: a new signup can grab the seat before the promotion runs, causing the first waitlisted user to be skipped.

Solution: Atomic Release-and-Promote in a Single Transaction

The author places both the cancellation update and the waitlist promotion inside the same database transaction, serialized by locking the activity row with SELECT ... FOR UPDATE. This makes the release and promotion appear as one atomic state change to all concurrent requests.

Database Schema

Two tables are used:

activity : stores capacity, confirmed_count, and next_seq (monotonically increasing queue number for each new signup).

activity_signup : one row per user per activity with status (CONFIRMED, WAITING, CANCELLED) and queue_seq. Unique indexes on (activity_id, user_id) and (activity_id, queue_seq), plus a composite index idx_waiting (activity_id, status, queue_seq) for fast waitlist lookup.

CREATE TABLE activity (
  id BIGINT PRIMARY KEY,
  capacity INT NOT NULL,
  confirmed_count INT NOT NULL DEFAULT 0,
  next_seq BIGINT NOT NULL DEFAULT 1,
  CHECK (capacity >= 0),
  CHECK (confirmed_count >= 0 AND confirmed_count <= capacity)
) ENGINE=InnoDB;

CREATE TABLE activity_signup (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  activity_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  queue_seq BIGINT NOT NULL,
  UNIQUE KEY uk_activity_user (activity_id, user_id),
  UNIQUE KEY uk_activity_seq (activity_id, queue_seq),
  KEY idx_waiting (activity_id, status, queue_seq)
) ENGINE=InnoDB;

Core Service Implementation (Spring Boot 3.x, Java 17+, MySQL 8.x)

All write paths for a given activity first lock the activity row ( lockActivity()), then modify signup records. This serializes writes per activity while allowing different activities to proceed in parallel.

@Service
public class SignupService {
  private final JdbcTemplate jdbc;

  public SignupService(JdbcTemplate jdbc) { this.jdbc = jdbc; }

  private record Activity(int capacity, int confirmedCount, long nextSeq) {}
  private record Signup(long id, String status) {}
  private record Waiting(long id, long userId) {}

  @Transactional
  public String join(long activityId, long userId) {
    Activity activity = lockActivity(activityId);
    Signup previous = findSignup(activityId, userId);

    // Idempotent retry: if already signed up and not cancelled, return current status
    if (previous != null && !"CANCELLED".equals(previous.status())) {
      return previous.status();
    }

    long seq = activity.nextSeq();
    boolean hasSeat = activity.confirmedCount() < activity.capacity();
    String newStatus = hasSeat ? "CONFIRMED" : "WAITING";

    if (previous == null) {
      jdbc.update("""
        INSERT INTO activity_signup (activity_id, user_id, status, queue_seq)
        VALUES (?, ?, ?, ?)
        """, activityId, userId, newStatus, seq);
    } else {
      jdbc.update("""
        UPDATE activity_signup
        SET status = ?, queue_seq = ?
        WHERE id = ? AND status = 'CANCELLED'
        """, newStatus, seq, previous.id());
    }

    jdbc.update("""
      UPDATE activity
      SET next_seq = next_seq + 1,
          confirmed_count = confirmed_count + ?
      WHERE id = ?
      """, hasSeat ? 1 : 0, activityId);

    return newStatus;
  }

  @Transactional
  public Long cancel(long activityId, long userId) {
    lockActivity(activityId);
    Signup mine = findSignup(activityId, userId);
    if (mine == null || "CANCELLED".equals(mine.status())) {
      return null; // no signup or duplicate cancel
    }

    jdbc.update("""
      UPDATE activity_signup SET status = 'CANCELLED' WHERE id = ?
      """, mine.id());

    // If the cancelling user was only waiting, just remove them
    if ("WAITING".equals(mine.status())) {
      return null;
    }

    // Find the first waitlisted user
    List<Waiting> first = jdbc.query("""
      SELECT id, user_id FROM activity_signup
      WHERE activity_id = ? AND status = 'WAITING'
      ORDER BY queue_seq LIMIT 1 FOR UPDATE
      """, (rs, row) -> new Waiting(rs.getLong("id"), rs.getLong("user_id")), activityId);

    if (first.isEmpty()) {
      // No waitlist: simply decrement confirmed_count
      jdbc.update("""
        UPDATE activity SET confirmed_count = confirmed_count - 1 WHERE id = ?
        """, activityId);
      return null;
    }

    Waiting promoted = first.get(0);
    jdbc.update("""
      UPDATE activity_signup SET status = 'CONFIRMED'
      WHERE id = ? AND status = 'WAITING'
      """, promoted.id());
    // One out, one in: confirmed_count unchanged
    return promoted.userId();
  }

  private Activity lockActivity(long activityId) {
    List<Activity> rows = jdbc.query("""
      SELECT capacity, confirmed_count, next_seq FROM activity WHERE id = ? FOR UPDATE
      """, (rs, row) -> new Activity(
        rs.getInt("capacity"),
        rs.getInt("confirmed_count"),
        rs.getLong("next_seq")), activityId);
    if (rows.isEmpty()) throw new IllegalArgumentException("activity not found");
    return rows.get(0);
  }

  private Signup findSignup(long activityId, long userId) {
    List<Signup> rows = jdbc.query("""
      SELECT id, status FROM activity_signup WHERE activity_id = ? AND user_id = ?
      """, (rs, row) -> new Signup(rs.getLong("id"), rs.getString("status")), activityId, userId);
    return rows.isEmpty() ? null : rows.get(0);
  }
}

HTTP Endpoints

A thin controller exposes POST /activities/{activityId}/signups/{userId} for join and DELETE ... for cancel. The cancel endpoint returns the promoted user ID so the caller can send notifications after the transaction commits. The author warns against calling external APIs (SMS, WeChat) while holding the database row lock.

@RestController
@RequestMapping("/activities/{activityId}/signups")
public class SignupController {
  private final SignupService service;

  public SignupController(SignupService service) { this.service = service; }

  @PostMapping("/{userId}")
  public Map<String, String> join(@PathVariable long activityId, @PathVariable long userId) {
    return Map.of("status", service.join(activityId, userId));
  }

  @DeleteMapping("/{userId}")
  public Map<String, Object> cancel(@PathVariable long activityId, @PathVariable long userId) {
    Long promoted = service.cancel(activityId, userId);
    return promoted == null
      ? Map.of("promoted", false)
      : Map.of("promoted", true, "userId", promoted);
  }
}

Why the 101st User Cannot Be Pushed Out

Scenario: capacity 100, first 100 confirmed, user 101 is WAITING. When user 20 cancels, the transaction locks the activity row, marks user 20 CANCELLED, finds user 101 via ORDER BY queue_seq LIMIT 1 FOR UPDATE, and promotes them to CONFIRMED — all in one commit. Any concurrent join request (e.g., user 102) must wait for the same activity row lock; when it acquires the lock, it sees the post-promotion state (confirmed_count still 100) and can only join as WAITING.

Conversely, if no waitlist exists, the transaction decrements confirmed_count. The next join request then sees an available seat and becomes CONFIRMED. Re-signing users receive a new queue_seq, so they cannot reclaim their old position.

The queue_seq is assigned while holding the activity lock, so its order matches the serialization order of the transactions — not the arrival time at the gateway.

Verification & Limitations

A minimal test with capacity 1 validates the flow: A gets CONFIRMED, B and C get WAITING; cancel A → B promoted; cancel B → C promoted; A re-joins → goes to tail. Concurrency tests with multiple threads and separate database connections should verify invariants after each round: confirmed_count == actual CONFIRMED rows, confirmed_count <= capacity, and no WAITING rows exist when a seat is free. The author stresses testing against real InnoDB, not H2 or single-connection mocks.

This design works while per-activity write throughput fits within single-row locking. For flash-sale traffic, the activity row becomes a bottleneck; the solution is upstream traffic shaping and a redesigned allocation scheme, not merely increasing the connection pool. The article references MySQL 8.4 documentation on InnoDB locking reads and lock types, and Spring's @Transactional proxy behavior.

References

MySQL 8.4: InnoDB Locking Reads

MySQL 8.4: Locks Set by Different SQL Statements

Spring Framework: @Transactional Proxy Mode