What transaction and isolation choices matter when two services update the same laboratory result?

Why

Picture an instrument service that writes a corrected value while a review service records an approval on the same row. Each reads the row, changes one field in memory and writes the whole row back. Whoever commits second erases the other's change, and nothing errors.

The PostgreSQL documentation explains that Read Committed, the default, gives each statement a fresh snapshot, so it does not stop this read-then-write pattern by itself. There are four standard remedies. Do the change in one statement, such as UPDATE results SET status = 'approved' WHERE id = 42 AND status = 'pending', so the condition and the write are atomic.

Lock the row first with SELECT ... FOR UPDATE, which the documentation says blocks other updates and locks on those rows until the transaction ends. Use a version column and update only WHERE version = the value you read, treating zero affected rows as a conflict.

Or run at Repeatable Read or Serializable, where the database rolls back a conflicting transaction with a serialization error, and the application must retry the whole transaction. For regulated data, also keep the audit trail in the same transaction as the change, so a rollback cannot leave a history row for an update that never happened. Keep transactions short, because held locks block other writers.