Race conditions are the defects that pass every test and appear in production, because tests run one operation at a time and production does not. Two users edit the same record and one change silently disappears. Two requests both check that stock is available and both proceed. A button clicked twice creates two orders. All of these are correct-looking code, and all of them are wrong the moment two things happen at once.
The lost update is the most common. Two people open a record, both see the current values, both save. The second write overwrites the first, and neither user is told anything - the first person's change simply ceases to exist, usually discovered much later. The read-modify-write cycle is the culprit, and it is everywhere in ordinary application code.
Optimistic locking fixes it cheaply and is under-used. Every row carries a version number, sent to the client with the data and back with the update. The update statement includes a condition that the version still matches, and increments it. If someone else saved in between, the condition matches no rows, the update affects nothing, and you can tell the user their data changed underneath them and show them what happened. That is a considerably better outcome than silent loss, and it costs one column.
Pessimistic locking - taking a lock for the duration - is the alternative and is right where conflicts are frequent or a wrong outcome is unacceptable, such as decrementing inventory or moving money. It is more expensive because it holds a lock across the transaction, and it introduces deadlock as a possibility when two transactions take the same locks in different orders. Always acquire locks in a consistent order, and keep the transaction short - never wait for a user or an external API while holding one.
The check-then-act pattern deserves specific attention because it looks so reasonable. Checking whether a username is free and then inserting it is a race with a window between the two statements. The only reliable fix is a database constraint - a unique index - and handling the violation when the insert fails. The constraint is atomic in a way the check never is, which is the general principle: where correctness depends on a rule, enforce the rule in the database rather than in a sequence of application statements.
For testing, the useful technique is deliberate rather than accidental: fire concurrent requests at the endpoint and assert the invariant afterwards - total stock never negative, exactly one order created, the final balance correct. Most concurrency bugs surface within a few dozen parallel attempts, and a test that runs them is repeatable in a way that hoping to notice the problem in production is not.
