How to Prevent Concurrent Updates from Overwriting Each Other

Two users read the same record:

Who wins?

Without concurrency protection, the second write might silently overwrite the first.

One common solution is optimistic concurrency.

For example, keep a version number with the record:

Order
Id: 42
Status: Pending
Version: 7

Update it only if the version is still 7:

UPDATE Orders
SET Status = 'Completed',
    Version = 8
WHERE Id = 42
  AND Version = 7;

If zero rows are updated, somebody changed the record after you read it.

Your application can then reject, retry, merge, or ask the user to resolve the conflict.

If multiple actors can update the same data, checking whether it changed before overwriting it is often useful.