All packsOffline Sync EngineSolve
postgres advisory lock serialize transaction commit order race
Two transactions can take sequence numbers out of commit order
Transaction A takes sequence 100, transaction B takes 101 and commits first -- a client that pulled through 101 never sees seq 100, because nothing serialized commit order against sequence order.
What goes wrong
If two push transactions run concurrently, transaction A might grab sequence number 100 while transaction B grabs 101 -- and then B commits first. A client pulling changes in sequence order, having already paginated through 101, has no reason to go back and check for 100: from its perspective it's already past that point, but transaction A's row commits moments later at a sequence number the client has already scrolled past and will never revisit.
Why agents get it wrong
Handing out sequence numbers via a database sequence and letting transactions commit whenever they finish is the default, low-friction way to build a paginated feed, and it works correctly under light load where transactions rarely overlap -- the race is a genuine concurrency bug that only manifests under simultaneous writes, which single-threaded manual testing essentially never produces.
What Offline Sync Engine pre-decides
The pack pins a single fix rather than leaving this as an open risk: every push transaction opens with SELECT pg_advisory_xact_lock(4217), serializing all pushes through one lock so sequence order and commit order become the same thing by construction. The documented ceiling is real -- this caps push throughput at roughly hundreds per second -- and the pack states the upgrade path explicitly (fencing pulls at the oldest in-flight transaction) rather than suggesting the lock be removed to fix throughput.
Source: ARCHITECTURE.md 'Pagination (decided)'