1. Problem and Goals

Our system applies users’ change requests by opening Merge Requests (MRs) against a Git repository. If every request opened its own MR, many MRs would modify the same repository at the same time, force each other to rebase, and queue up on the pipelines. To avoid this we introduced batch aggregation: once a request is approved it enters a waiting set, and each round merges the waiting requests into a single MR that is processed as a unit.

This design requires the following invariants to hold at all times:

  • I1 Single active batch: at any moment there is at most one active batch (running or blocked).
  • I2 No double claiming: a request is claimed by at most one batch.
  • I3 Non-blocking admission: while a batch is running, new requests can still enter the waiting set.
  • I4 Idempotent admission: if the same request is submitted repeatedly, it is admitted only once.
  • I5 One row per relationship: each “subject–target” relationship has exactly one row of current state, and re-applying reuses that row.
  • I6 Stale writes fail: state transitions are based on the expected current state; if the state has been changed elsewhere, the write must fail.

The goal is to have the database enforce these invariants directly, rather than relying on “application code that happens to be correct”.

2. Constraints and Considerations

  • The database is MySQL InnoDB. MySQL does not support partial unique indexes like PostgreSQL does (CREATE UNIQUE INDEX ... WHERE status = 'RUNNING').
  • The service runs as multiple instances, and no new components are introduced. There is no Redis, ZooKeeper or similar distributed lock available, and we do not plan to introduce one for this.
  • Triggers overlap. The scheduling platform guarantees that a scheduled job runs on a single instance, but operators can manually trigger the same round, and a manual trigger may coincide with a scheduled one.
  • Batches are long-lived. A batch takes several minutes from creating the MR to merging it, and we cannot hold database locks for that long.
  • The process may crash at any step. The mutual-exclusion mechanism must not leak permanently because its holder crashed.

3. Design

3.1 A generated column plus a unique index: the single active slot (I1)

CREATE TABLE merge_batch (
  id          BIGINT      NOT NULL AUTO_INCREMENT,
  status      TINYINT     NOT NULL DEFAULT 1 COMMENT '1=RUNNING 2=SUCCEEDED 3=FAILED 4=BLOCKED',
  active_slot TINYINT GENERATED ALWAYS AS
                (CASE WHEN status IN (1, 4) THEN 1 ELSE NULL END) STORED,
  mr_id       VARCHAR(64) NULL,
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_active_slot (active_slot),
  KEY idx_status_created (status, created_at, id)
) ENGINE = InnoDB;

This design relies on one semantic of MySQL unique indexes: a unique index allows multiple NULLs.

  • Active batches (RUNNING, BLOCKED) have active_slot = 1, and the unique index guarantees that at most one row in the whole table has the value 1.
  • Terminal batches (SUCCEEDED, FAILED) have active_slot = NULL, so any number of them can coexist.
  • The UPDATE status that moves a batch into a terminal state also turns active_slot into NULL, releasing the slot with it; no separate release step is needed.

In effect this is equivalent to the partial unique index UNIQUE (status) WHERE status IN (RUNNING, BLOCKED).

We also compared other mutual-exclusion approaches:

Approach Main problem
A lock row plus SELECT ... FOR UPDATE The lock is either held for the whole batch (a transaction lasting minutes), or a separate lease field has to be maintained
Session-level GET_LOCK() The lock is bound to a database connection, which does not fit well with connection reuse in a pool
An external distributed lock such as Redis Requires a new component; the lease expiry is hard to align with the task’s actual duration
A generated column plus a unique index The lock and the state are the same column: there is no lease, and “state is terminal but the lock was not released” cannot happen

The last row is why we chose it: the mutual-exclusion state is the business state itself. If the holder crashes, the batch stays RUNNING and the slot remains occupied, but this is an explicit state that can be queried and handled, not an invisible leaked lock. A timeout sweep or the operations tool moves it to a terminal state, and the slot is released. The blocked state (BLOCKED) also occupies the slot, so subsequent batches stop until this batch has been dealt with.

3.2 The claim transaction: non-locking reads and guarded UPDATEs (I2, I3)

CREATE TABLE merge_batch_request (
  id           BIGINT      NOT NULL AUTO_INCREMENT,
  operation_id VARCHAR(36) NOT NULL,
  batch_id     BIGINT      NULL COMMENT 'NULL means still waiting',
  created_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_operation_id (operation_id),
  KEY idx_batch_created (batch_id, created_at, id)
) ENGINE = InnoDB;

Each round’s claim is done in a single transaction:

@Transactional
public Claim claim(int maxRequests) {
    // 1. Take the slot
    long batchId;
    try {
        batchId = insertReturningKey("INSERT INTO merge_batch (status) VALUES (1)");
    } catch (DuplicateKeyException e) {
        return Claim.slotOccupied();   // This transaction has not written anything yet; returning normally is equivalent to rolling back
    }

    // 2. Read the waiting requests: a plain read, no FOR UPDATE
    List<Req> pending = query(
        "SELECT id, operation_id FROM merge_batch_request " +
        "WHERE batch_id IS NULL AND created_at < CURRENT_TIMESTAMP " +
        "ORDER BY created_at, id LIMIT ?", maxRequests);
    if (pending.isEmpty()) {
        throw new NothingToClaimException();   // Roll back, releasing the slot just taken
    }

    // 3. Guarded claim: only claim rows that are still unclaimed, and the affected row count must match the number of rows read
    int updated = update(
        "UPDATE merge_batch_request SET batch_id = ? " +
        "WHERE batch_id IS NULL AND id IN (" + placeholders(pending) + ")",
        batchId, ids(pending));
    if (updated != pending.size()) {
        throw new ClaimPreemptedException();   // Some row was changed elsewhere; roll back everything
    }

    // 4. Write the append-only membership history
    insertHistory(batchId, pending);
    return Claim.claimed(batchId, pending);
}

Why step 2 does not use FOR UPDATE. Under the REPEATABLE READ isolation level, a locking read with a range condition takes next-key locks on the scanned index range. Here the scanned range is the batch_id IS NULL range of idx_batch_created, which is exactly where newly admitted requests are inserted, so they would be blocked until the claim transaction ends, violating I3. Dropping the locking read does not affect correctness:

  • Other claimers are kept out by the slot in step 1;
  • Even if an unexpected concurrent modification occurs, the WHERE batch_id IS NULL and the affected-row-count check in step 3 detect it and roll back the whole transaction.

Why an empty read throws an exception. If the transaction committed normally, it would leave behind a RUNNING batch with no members that still occupies the slot. Throwing makes the slot roll back together with the transaction.

A lock-free pre-check outside the transaction. Before entering the claim transaction, we first run SELECT ... WHERE batch_id IS NULL ... LIMIT 1. When the waiting set is empty, the round ends immediately, avoiding the idle churn of “take the slot, then roll back”.

Use the database clock throughout. The condition is written as created_at < CURRENT_TIMESTAMP, and the timeout sweep as created_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? SECOND). The write time and the comparison time come from the same clock, so clock skew between application servers does not affect the decision.

One thing to verify in your implementation. Step 1 catches the unique-key conflict inside the transaction and returns normally. In MySQL, a failed statement rolls back only that statement, and the transaction itself remains usable. However, some data-access frameworks mark the transaction as rollback-only when they translate the exception, causing an error on commit. Before adopting this pattern, confirm how your framework behaves.

3.3 State as version: guarded state transitions (I6)

Every state write includes the expected current state and checks the affected row count:

-- Batch terminal transition: only a batch that is still RUNNING can transition
UPDATE merge_batch SET status = 2 WHERE id = ? AND status = 1;   -- Affected rows must be 1

-- Optimistic concurrency control for ordinary rows: the state just read serves as the version
UPDATE operation SET status = ?, ... WHERE operation_id = ? AND status = ?;

An affected row count of 0 means the state has been changed elsewhere, and the writer throws a “preempted” exception.

The position of such a guarded write inside a composite transaction also matters. The settlement transaction for a failed batch has several steps: mark the batch as FAILED, then delete or reset the records of each request in the batch. We put the batch’s guarded write first:

tx.execute(List.of(
    () -> batchRepo.markFailed(batchId, code, msg),  // WHERE status = RUNNING; affected rows must be 1
    () -> resetOperations(batchId)                   // Subsequent steps
));

If the batch has already been settled by another path (for example, the timeout sweep and the running task arrive at the same time), the first step fails, the whole transaction rolls back, and the reset does not run a second time.

3.4 Treat a duplicate key as an answer, not an error (I4)

A unique-constraint conflict has a clear business meaning in each scenario, and we handle each one accordingly:

// Admission: a duplicate means "already admitted"; return false, no error
public boolean admit(String opId) {
    try {
        insert("INSERT INTO merge_batch_request (operation_id) VALUES (?)", opId);
        return true;
    } catch (DuplicateKeyException e) {
        return false;
    }
}

// Audit record: a duplicate means "already recorded"; do not overwrite the existing row (it may already be terminal)
public void insertAudit(Audit a) {
    try { insert(a); } catch (DuplicateKeyException e) { /* Expected deduplication result */ }
}

// Request idempotency: look up first; insert if not found; an insert conflict means a concurrent request inserted first, so re-read and return the winner's result
public Result checkOrInsert(String requestId, String opId) {
    Record r = findById(requestId);
    if (r != null) return Result.duplicate(r.operationId());
    try {
        insert(new Record(requestId, opId));
        return Result.fresh();
    } catch (DuplicateKeyException e) {
        Record winner = findById(requestId);
        return winner != null ? Result.duplicate(winner.operationId())
                              : Result.retryLater();
    }
}

None of these three patterns takes an explicit lock; their correctness under concurrency comes from the unique constraint itself.

3.5 One row per relationship: in-place reset and refusing to overwrite (I5)

The binding table has a unique key on (subject_id, target_key), so each relationship has exactly one row. But every request generates a new operation_id, so the “one row per operation” write model has to be reconciled with the “one row per relationship” unique key:

Binding existing = findBySubjectAndTarget(subject, target);
if (existing == null) {
    insert(newRow);                                     // First application
} else if (REAPPLIABLE.contains(existing.status())) {
    int n = update(
        "UPDATE binding SET status = ?, operation_id = ?, expires_at = ? " +
        "WHERE subject_id = ? AND target_key = ? AND status = ?",   // Pin the exact old state that was read
        newRow.status(), newRow.operationId(), newRow.expiresAt(),
        subject, target, existing.status());
    if (n == 0) throw new PreemptedException();         // A concurrent re-application or state change
} else {
    throw new ConstraintViolationException();           // Refuse to overwrite an active row or one awaiting recovery
}
  • Only re-appliable terminal states such as “rejected” and “closed” may be reset in place. The reset replaces operation_id, which is exactly how stale callbacks are recognized in the previous post.
  • Every other state refuses to be overwritten. If a failed row still awaiting manual recovery were overwritten, its operation_id would be replaced, and the recovery flow attached to the old operation_id would no longer find its target.
  • The WHERE clause pins the exact old state that was read, rather than “any re-appliable state”, so that a change between the read and the write is still detected when several states are re-appliable.
  • The history of every application is stored in a separate history table; the main table holds only the current state.

3.6 Cross-table transitions: a fixed lock order, and a result only when the state actually flips

Transitions that modify both the operation and binding tables always lock operation first and binding second. All paths use the same lock order, which avoids deadlocks. The advancing method returns a non-null result only when this call actually changed the state, and callers use that to decide whether to produce side effects (emitting metrics, sending notifications). As a result, of two concurrent paths, only one produces side effects (see Section 3.7 of the previous post for details).

3.7 Settling a failed batch: close first, then re-read, and let “merged” win

When a batch fails, the MR may already exist, and it may happen to merge successfully just after the failure was decided. The settlement flow is: if the MR is still open, close it first, then re-read the MR’s final state.

  • If it reads merged: treat it as a success, align all members to “merged”, and mark the batch SUCCEEDED. A merged MR can no longer be closed, so when “merge” and “close” race, the merge result is never lost.
  • If it reads closed, or there is no MR: mark the batch FAILED and, in the same transaction, delete each member’s processing record; the append-only membership history and audit records are kept.

4. Results

  • All six invariants are enforced by database constraints or guarded writes. Concurrent claims, duplicate admissions, duplicate requests and stale state writes are all rejected or recognized at the database level, independent of call timing.
  • No new components were introduced. The single-active mutual exclusion has no lock table, lease or external lock service; the mutual-exclusion state and the business state are the same column, so they can never disagree.
  • Admission is not blocked by batches. The claim transaction takes no locks on the waiting range, so new requests are written as usual while a batch is running.
  • State is explicit after a crash. A crashed batch stays in a queryable RUNNING state and is settled by the timeout sweep or the operations tool; there are no invisible leaked locks.
  • Scheduled and manual triggers can safely overlap. Both run the same code; when they overlap, the later one gets “slot occupied”, with no extra coordination needed.

5. Known Limitations and Next Steps

  • State-as-version has an ABA problem. If a row leaves a state and returns to it between the read and the write, the guard condition still passes. The table reserves a version column that is not yet in use. For states that may round-trip, we plan to switch to a monotonically increasing version number.
  • There is only one global slot. The current design does not support parallelism by dimension. If per-partition parallelism is needed, the generated column can be changed to CASE WHEN status IN (1, 4) THEN partition_key END, and the unique index becomes “at most one active batch per partition”.
  • The batch path has row-by-row access. The hand-off phase looks up the relationship row once per request (N+1); membership history is inserted row by row; and IN lists can reach over a thousand placeholders without being chunked. We plan to switch to batch queries and batch inserts, and to chunk the IN lists.
  • Some queries lack suitable indexes. The reconciliation query filters by status plus a time range, but only a single-column time index exists; fetching the largest MR number uses ORDER BY CAST(mr_id AS UNSIGNED), and sorting on an expression cannot use an index. We plan to add a composite (status, updated_at) index and store the MR number in a numeric column.
  • Expiry of idempotency records is not enforced. The records have an expiry field, but there is no cleanup job and no corresponding index.
  • Time precision is one second. created_at has second precision and the claim condition is created_at < CURRENT_TIMESTAMP, so requests admitted within the current second wait until the next round. This is a deliberate trade-off, but it needs to be documented.