The problem
TTMI runs an internal retail ERP used across 50 stores, ~500 employees and 4 brands. One of its modules handles quality control: inspectors open a batch of items and record results for each one.
Several inspectors often work on the same batch at the same time, from different devices. The original update path was “last write wins”. If two people saved around the same moment, or one person saved from a screen they had loaded minutes earlier, a colleague’s work was overwritten and nobody was told.
The obvious fix, locking the whole batch for every edit, would have made inspectors queue behind each other and the screen would feel frozen.
Two related problems sat next to this one. Login tokens were shared and long-lived, so they could not be scoped to one application or one device, and two logins at the same moment could race and issue duplicate tokens. And the bulk update endpoint for this area ran far more SQL than the work needed.
What I did
Narrow row locks plus version checks
Inside one transaction, the API locks the parent batch row and then only the item rows the request actually touches. Inspectors working on different items do not block each other. Each item also carries a version number. The client sends the version it loaded, and if that version is stale the API answers with a conflict before writing anything.
Locks protect against two requests running at the same moment. Version checks protect against the person whose screen is five minutes old, which no lock can see. The trade-off is more moving parts than either technique alone, in exchange for short locks and stale screens that get caught explicitly.
A fixed lock order
Two requests that touch overlapping items can deadlock if they lock rows in different orders. I made the item locks always happen in one stable order. The cost is a small sort on every write, which is cheap next to a deadlock and a retry.
Locking only the main rows
PostgreSQL cannot lock the nullable side of an outer join, so I limited the lock to the primary rows. Related rows are protected by the parent lock and by validation, without a lock of their own. I accepted this because the parent lock already serializes writers on the same batch.
Tokens scoped per app and device, rotated atomically
I added tokens tied to a user, an application and a device. Issuing a new token happens under a row lock inside a transaction, and only the token for that application and device is replaced. Two simultaneous logins can no longer mint duplicates, and signing in on one device does not log the user out elsewhere. The price is one extra locked read at login.
Fewer queries on the bulk update path
The bulk endpoint loaded reference data once per row and prefetched nested data per row. I changed it to read reference data once per request and dropped the per-row nested prefetching. I deliberately kept per-row validation and locking identical to the single-item path, reusing the same logic. A faster special path was possible, but two copies of the rules would drift apart over time.
Result
Stale edits now come back as a clear conflict that tells the inspector to reload, and nobody’s work disappears silently. Inspectors editing different items in the same batch proceed in parallel.
Concurrent logins cannot create duplicate tokens, and rotating a token for one device leaves the user’s other sessions valid.
The bulk QC batch API ran ~55% fewer SQL queries and was ~60% faster in a local benchmark of ~320 rows. The changes are deployed to production.
What I’d do differently
I measured the speed-up only in a local benchmark. Next time I would add query-count assertions to the test suite and capture timings from production, so the gain is both protected and confirmed under real load. I would also write the locking rules down as a short design note before coding, because the reasons behind the lock order and the outer-join limit are easy to lose.