When Two People Save at Once, One Edit Disappears: Concurrency Control and Locking Design for Business Systems

The Bug You Never See on the Dev Server

During development and testing, one person usually works on one screen at a time. Problems that appear only when two people save the same data at nearly the same moment tend to show up after go-live. The usual symptoms:

Negative stock: With 10 units on hand, two staff members ship 7 units each at the same time. Both checks see "10 available," both pass, and stock ends at -4

Duplicate order numbers: If the system reads the last number and adds 1, two simultaneous orders get the same number

Overwritten approval comments: While A is typing a comment, B edits the amount and saves. When A saves later, A's stale screen wipes out B's change

Double charges: A slow response makes the user click again, and the same payment is processed twice

These failures happen only when requests overlap within tens or hundreds of milliseconds, so they are hard to reproduce. The logs show two perfectly normal transactions, and finding the root cause often takes weeks.

Types of Concurrency Problems

Lost update: Two transactions read the same value, each modifies it, and the earlier change disappears

Duplicate processing: The same request runs twice because of a resend or retry

Check-then-act races: Another request slips in between "check the remaining quantity" and "deduct it"

It helps to separate two cases. Screen editing conflicts involve data a person holds open for minutes. Backend processing races involve requests that overlap within milliseconds. They call for different solutions.

Comparing the Approaches

Optimistic Locking

Add a version column and save with a condition: update only if the version still matches what you read, and increment it. If zero rows are updated, someone else saved first, and the user is told. No locks are held in the normal case, so the overhead is minimal. In HTTP APIs, ETag and If-Match headers with a 412 response follow the same principle.

Pessimistic Locking

SELECT ... FOR UPDATE locks the row and makes other transactions wait until you finish. It is reliable, but waits can grow, and if two transactions each wait for the other's lock, you get a deadlock. Always acquire locks in a fixed order (for example, ascending product ID), and cap waiting with lock_timeout or NOWAIT.

Atomic Updates and Constraints

Instead of read, compute, write, do it in one statement: "UPDATE stock SET qty = qty - 7 WHERE product = ? AND qty >= 7." The check and the deduction happen together, so stock cannot go negative. Issue order numbers from a database sequence and add a unique key, so the database blocks duplicates even when application code has a bug.

Idempotency Keys

The client sends a unique key with each request, and the server returns the original result if it has already processed that key. For payments and orders, where retries are common, this is close to mandatory.

Choosing by Business Scenario

Stock deduction and seat booking: Contention is frequent. Make atomic updates the default, and when several rows must change together, take pessimistic locks in a fixed order

Document and approval editing: Conflicts are rare, but a person needs to decide which version is right. Optimistic locking fits

Batch jobs overlapping with online work: A nightly settlement batch that holds thousands of rows locked for minutes stalls online users. Commit in chunks of 1,000 rows, and update target rows with a version condition so you never overwrite online edits

Designing the User Experience Too

If you only detect the conflict and show "Save failed," users lose what they typed. Show "Another user saved changes first" with their input and the latest values side by side, and let them choose field by field. Displaying a soft lock such as "Jane Kim is editing" when editing starts reduces conflicts in the first place. Give that marker an expiry of around 15 to 30 minutes so it clears automatically when someone simply closes the browser.

Testing and Operations

Functional tests will not catch races. Build a load test that fires 50 to 100 simultaneous requests at the same product, then verify stock totals, order-number uniqueness, and payment counts. In production, record version conflicts, lock-wait timeouts, and reused idempotency keys in an audit log. You can then see which screens conflict most often and fix them based on the numbers.

At POLYGLOTSOFT, we design concurrency control and conflict-handling screens into business systems such as ERP, MES, and WMS from the start. With our subscription development service, these safeguards are built into new systems from day one. For systems already in production, our SM service tracks down the causes of stock mismatches and duplicate processing and fixes them. If you are seeing similar symptoms, feel free to get in touch.