Order a trip's modifications under a FOR UPDATE lock on the trip row

Decision

Start every transaction that writes a modification with SELECT ... FOR UPDATE on the trip's row. Under that lock the write reads the head, takes the next position in the trip's log, inserts the modification with its parent, advances the head and the version, and commits. A write aimed at a version other than the active one also locks that version's row. Switching versions locks the version row before copying its tip into the trip. Postgres releases the locks on commit or rollback. Replay orders modifications by the position assigned under the lock, and never by timestamp.

Why

Each new modification must point at the current tip, and the tip must advance atomically (ADR-0046). Two writers that read the same tip would both append to it and create a fork nobody asked for. The next position is the trip's highest position plus one, and no unique index backs it, so the lock is also what stops two writes taking the same position.

Rejected alternatives

• Optimistic concurrency, with a version check and a retry on conflict. Every writer needs retry logic. With a lock none of them do.
• Ordering replay by timestamp. The server stamps a write when it builds the request, before the transaction takes the lock, so a later stamp can commit first. Clocks on separate server instances also disagree.

Consequences

Concurrent writers to one trip queue and run one at a time, with no retries. Trips are edited rarely enough that the wait is negligible. The lock only serializes writes. Whether a write is allowed is decided by the ancestry check that runs under it (ADR-0073). A write path that skipped the lock could assign a duplicate position without any error, so every path takes it first.