A Save button can report success while discarding someone else's work. An employee edits an equipment record's department. A colleague corrects its location. If the first employee later submits the whole old form, the department changes but the corrected location is overwritten.
That is a lost update. Two people and a pause between reading and writing are enough. A concurrency strategy determines when to stop the competing change and what happens next.
Check the version when saving
With optimistic control, the record carries a version. An employee who opened version 12 can save only if that version remains current. If a colleague has already produced version 13, the application reports a conflict instead of overwriting it.
The condition and update must be atomic: checking first and then writing unconditionally leaves another race. Imports, scripts and background jobs must update the same marker or their changes can escape detection.
Optimistic control does not mean the database uses no locks at all. It avoids holding a long-lived lock between opening the form and submitting it. The actual write still follows the database's concurrency rules. That matters when someone spends minutes editing a record.
Other markers can work. A last-modified timestamp needs verified precision and generation rules: two different changes must not silently share the same marker.
A detected conflict still needs a resolution
“Unable to save” is inadequate after someone fills out a long form. Preserve the input, show competing changes and let the person reload, reapply selected edits or resolve the values.
Automatic merging can work for independent changes. Different column names do not establish independence, though. A department and a location may be linked by an equipment-allocation rule; a discount and a total are linked by a calculation. The merged record still needs validation as a whole.
A particularly bad retry silently fetches the latest version and writes the original form over it. The technical exception disappears because the application has bypassed the protection it was supposed to provide. A decision made by a person on old information often requires renewed consent, not a hidden retry.
When briefly reserving the resource is simpler
Pessimistic control can fit assigning the only available item of equipment. A short transaction locks the relevant record, checks its current state, assigns it and commits. Operations requiring an incompatible lock must wait or fail under an explicit policy.
In PostgreSQL, SELECT FOR UPDATE protects the selected row against conflicting modifications until the transaction ends; it does not block ordinary reads of that row. “Locked” therefore does not mean that nobody can look at the data. The lock mode determines which operations are affected.
A user's open editing form should not become a database transaction of that kind. If a product needs a visible “reserved for editing” feature, that is an application-level agreement: expiry, renewal, release when the user leaves and validation of a save submitted after expiry. It should not hold a database connection and lock indefinitely.
Frequent collisions can indicate a modelling problem
Even short transactions can acquire resources in different orders. One locks an equipment record and waits for an assignment request; another locks the request and waits for the equipment. That is a deadlock. The database may detect the cycle and abort one transaction. The application still needs to handle the failure and, when appropriate, retry the complete operation.
A consistent acquisition order, short transactions and bounded waits reduce risk. Extending a timeout does not remove the cycle.
With optimistic control, a heavily contested record creates a different failure pattern: many attempts fail the version check and start again. Those retries consume capacity and can sustain overload. It may be better to reduce the shared record's scope, separate independent operations or change scheduling rather than switch every update in the product to another strategy.
A costly mistake does not by itself require pessimistic locking. Conditional updates can protect critical work too. Compare collision frequency, operation duration and whether the decision can safely be repeated.
Check the actual user experience
For the equipment form, open two windows on the same version. After saving from one, the other must not quietly erase its changes. Keep their draft. For automatic assignment, test simultaneous requests and failure while a process holds a lock.
Measure conflicts, waits, deadlocks, retries and completion time. Frequent collisions on one record can expose a poorly designed workflow even when the protection works.
Finally, locking coordinates access to state, while idempotency prevents repeating the same logical instruction. Once a lock has been released, a duplicate delivery may run again unless operation identity is checked. An important workflow may need both forms of protection.