Drag-and-Drop Ordering That Survives Concurrency

At a glance

The one-sentence version

I designed a way to let users drag and drop to reorder their goals that stays fast and correct no matter how long the list is or how many people are reordering it at once.

The problem

Drag and drop to reorder a list looks trivial until that list is shared across a multi-tenant database under concurrent load. The obvious approach, an integer position per row, means moving one item rewrites every row after it. On a long list that's hundreds of updates for a single drag, and a lock convoy on a table everyone touches at once.

flowchart LR
    subgraph NAIVE["Integer position"]
        N1["Move one item"] --> N2["Every row after it<br/>must shift"] --> N3["Writes grow with<br/>list length"]
    end
    subgraph GAP["Gap-based float rank"]
        G1["Move one item"] --> G2["new_rank = (rank_before +<br/>rank_after) / 2"] --> G3["One row written,<br/>regardless of list length"]
    end

How it's built

Each item gets a rank instead of a position, a float that sits between its two neighbours. Moving an item into a new spot means computing one new rank and writing one row:

new_rank = (rank_before + rank_after) / 2

No cascade, no rewriting everything after it. Paginating through the list uses a cursor keyed on that same rank rather than an offset, since offset pagination breaks the moment anything moves out from under it. The ranks slowly lose precision as the same region gets split again and again, so a scheduled job periodically re-spreads them back to even gaps rather than checking on every write. Backfilling ranks for existing data runs as a background job too, not a migration, so it never holds a platform-wide transaction open.

What production taught me

Months after shipping, two people reordering the same list at the same time exposed a race the design hadn't accounted for.

sequenceDiagram
    participant A as User A
    participant B as User B
    participant DB as Database

    A->>DB: read neighbouring ranks
    B->>DB: read neighbouring ranks
    Note over A,B: both compute the same midpoint
    A->>DB: write new rank
    B->>DB: write new rank
    Note over DB: two rows now share an identical rank

The fix was row locking around the read and the write, so a second concurrent reorder waits instead of racing. Reordering is infrequent and user-initiated, so a brief wait costs far less than a silently corrupted order.

Key takeaways

• A gap-based ranking scheme turns "insert into an ordered list" into a single write, regardless of list length. The bounded precision drift it trades for that is a fair deal, handled as scheduled maintenance instead of on the hot path.
• Pagination breaks the moment your sort key can change under the reader. Keying pagination on that same value, not a row offset, keeps it correct.
• A read-modify-write pattern is safe right up until two callers do it at the same time. If concurrent writes are possible, design the locking up front rather than waiting for production to find the race.
• Deleting logic that's causing bugs and earning nothing is a valid fix on its own, not just a fallback for when a better idea doesn't come along.

Backend engineer on Happy5 Performance, 2021 to present.