Optimistic vs pessimistic locking

Read-modify-write code (read a row, decide something in your application, write it back) has a race. Two requests can read the same value and both write, and one of the updates is lost. There are two standard fixes.

#Pessimistic: lock first

Take a row lock as you read, so other writers wait until you commit:

BEGIN;
SELECT stock FROM items WHERE id = 42 FOR UPDATE;   -- others block here
-- application checks stock, computes the new value
UPDATE items SET stock = 3 WHERE id = 42;
COMMIT;

• Correct by construction. No retries in the common case.
• Holds locks while your code runs. Keep the transaction short, and don't call other services inside it.
• Can deadlock when two transactions lock rows in different orders. Lock in a consistent order (by ID, for example). NOWAIT or SKIP LOCKED fail fast or skip busy rows. SKIP LOCKED is how a database table works as a job queue.

#Optimistic: check on write

Keep a version column. Read without locking, and make the write conditional on the version you read:

SELECT stock, version FROM items WHERE id = 42;          -- stock=4, version=17

UPDATE items SET stock = 3, version = version + 1
WHERE id = 42 AND version = 17;                          -- 1 row: you won
                                                         -- 0 rows: someone else did; re-read and retry

• No locks held between the read and the write, so it works even across an HTTP round trip. That's the "someone else edited this record" check in an edit form, often sent as an ETag and If-Match header.
• Wasted work under contention. If many writers fight over one row, most of them retry.

#Choosing

SERIALIZABLE isolation is optimistic locking done for you across every row the transaction touched, and conflicting transactions abort and must be retried. See isolation levels. Across machines, the same version-check idea shows up as fencing tokens.