Faizo

Writing · Rust · SQLite · Discord API

Two moderators click Close at once: a compare-and-swap state machine in SQLite

How a Discord ticket bot stays correct under double clicks, stale buttons and restarts: every state change is one guarded UPDATE, and only the winner acts.

A support-ticket system for Discord sounds like the simplest kind of bot. A member presses a button, the bot creates a private channel, staff help, someone presses Close. The first version of mine was written that way: read the ticket, check its status, do the work.

Real servers don't use it that politely. Two moderators press Close in the same second, and the channel can get two closing messages and two transcripts. A member double-clicks Open and can receive two tickets. Someone presses a Reopen button on a week-old message for a ticket that has since been deleted. A deploy restarts the service halfway through closing a ticket and can leave it with no buttons at all.

Every one of those is the same bug. The code reads a status and then acts on it, and something changes in between. This article describes the rewrite that removed the gap.

Why "check, then act" can't work here

Discord delivers each button click as a separate HTTP request. Two clicks arrive as two concurrent requests, handled by two tasks. Each one reads the ticket, sees open, and proceeds to close it. No amount of care inside one handler helps, because each handler is correct on its own.

A lock around the handler doesn't fix it either. A close takes several calls to Discord (post a message, change permissions, rename the channel, upload a transcript), which is seconds of work. Holding a lock across that makes every other click wait, and Discord requires an answer to an interaction within three seconds. A lock also disappears on restart, which is exactly when you need to know what was in progress.

And buttons live forever. A message posted last month still has a working Close button on it. The state the button was drawn for and the state the ticket is in now can be completely different.

One guarded write per transition

The fix is to make the database the only judge of whether a transition may happen, in a single statement:

UPDATE tickets
   SET status = 'closing'
 WHERE id = ?1
   AND status = 'open';

This is a compare-and-swap. The write only happens if the ticket is still in the state the caller believes it is in. SQLite runs one writer at a time, so of two simultaneous clicks exactly one statement changes a row. The handler then asks how many rows were changed:

/// Move a ticket from one state to another. Returns false if it wasn't in from.
fn transition(&self, id: i64, from: Status, to: Status) -> rusqlite::Result<bool> {
    let changed = self.conn.execute(
        "UPDATE tickets SET status = ?3 WHERE id = ?1 AND status = ?2",
        (id, from.as_str(), to.as_str()),
    )?;
    Ok(changed == 1)
}

Only the caller that gets true touches Discord. The loser answers privately with "this ticket is already being closed" and does nothing else. There is no code path in the service that reads a status and then decides what to do to the ticket. That rule is the whole design, and it handles all three problems at once: the second moderator loses the swap, and so does the stale button, because a deleted ticket is not in the state its old button expects.

In-between states are real states

Because the work takes seconds, the state machine has states for "work in progress":

pending  → open → closing → closed
                  closing → locked → deleting → closed
                            locked → reopening → open

closing, reopening and deleting are claims. Winning the swap into one of them means "this task now owns the ticket until it moves it on". Other clicks that arrive meanwhile fail their own swap, since no transition starts from a claimed state, and get the polite refusal.

If the work fails partway (Discord returns an error, a permission is missing), the task swaps the ticket back to where it came from and tells the user why. The message distinguishes "an admin needs to fix a permission" from "Discord was busy, try again", because the first never fixes itself and the second usually does.

Opening a ticket: reserve first

Opening is harder than closing, because there is no row to guard yet. The rules for opening are also richer: a member may have at most N open tickets, there may be a cooldown, and each ticket gets the next number in sequence.

All of that happens in one BEGIN IMMEDIATE transaction, which takes SQLite's write lock up front: check the member's open tickets against the limit, take the next ticket number, and insert a placeholder row in state pending. Only after that commits does the bot start creating the channel. A double click runs the transaction twice, and the second run sees the first one's placeholder and is refused. That holds even on a server with no limit or cooldown configured, since the placeholder itself is the evidence.

Two details keep reservations from causing their own problems. A failed open deletes its placeholder instead of leaving a closed ticket behind, so a member isn't put on cooldown for a ticket they never got. And a placeholder older than five minutes is treated as abandoned.

Post the new buttons before removing the old ones

A review of the first rewrite caught an ordering mistake that the state machine alone doesn't prevent. To lock a ticket, the bot removes the "Close" controls and posts a banner with "Reopen" and "Delete". My first version retired the old controls and then posted the new ones. If the second step failed, the ticket had no working buttons and nobody could do anything with it.

The order is now always: post the new controls, then record the new state, then retire the old controls. If posting fails, the transition is rolled back and the old buttons are still there and still valid. At every moment the channel contains a set of buttons that matches the ticket's actual state.

Surviving a restart

Deploys restart the service. A task that is halfway through closing a ticket when the process stops leaves the row in closing forever, and since nothing transitions out of a claimed state, the ticket would be stuck.

Two mechanisms handle this:

  • Drain on shutdown. Long-running flows are spawned through a small task tracker. On a stop signal the service stops accepting requests and waits up to eight seconds for tracked tasks to finish, inside the ten seconds Docker allows before it kills the container. The web framework's own graceful shutdown doesn't wait for tasks you spawned yourself, so this has to be explicit.
  • Recover on startup. Whatever was still in flight is put back to a resting state when the service boots: pending reservations are dropped, closing returns to open, and reopening and deleting return to locked. The rule for choosing the resting state follows from the previous section: go back to the state whose buttons are still on screen.

Testing it

Unit tests on the store prove the swaps: two transitions from the same state, exactly one succeeds. That is necessary and nowhere near sufficient, because the bugs were in the flows around the swaps.

So the main tests drive the real HTTP router with correctly signed interaction payloads, against a fake Discord API running in the same process. The fake is strict in the ways that matter: it refuses edits to an archived thread, and it refuses to lock a thread when the bot lacks the permission Discord requires for that. A flow that does its steps in the wrong order fails against the fake the same way it would fail against Discord, in a test instead of in someone's server. Any new flow gets its tests there first.

Summary

  • Never read a status and then act on it. Make the state change a single guarded UPDATE … WHERE status = ? and check the affected-row count.
  • Only the winner of the swap performs side effects. Everyone else gets a refusal.
  • Give slow work its own claimed states, and roll back to the previous state on failure.
  • To create something, reserve a placeholder row inside one write transaction first.
  • Add the new controls before removing the old ones.
  • On startup, return every claimed state to the resting state the user can still see.