The lost update SQL will not stop for you

1. Make the database do the arithmetic (best, when you can)

UPDATE accounts SET balance = balance - 100 WHERE id = 'alice' AND balance >= 100;

Read and write are now one statement, and a row-level write lock is held for its duration — the second session blocks until the first commits, then re-reads and computes from the current value. Check the affected row count: zero means the balance >= 100 guard rejected it, which is your insufficient-funds path, expressed without a round trip.

This is the right answer whenever the new value is a function of the old one that SQL can express. It is not always available, which is what the other three are for.

2. SELECT ... FOR UPDATE (when the decision must happen in your code)

BEGIN;
SELECT balance FROM accounts WHERE id = 'alice' FOR UPDATE;  -- B blocks here
-- ... arbitrary application logic ...
UPDATE accounts SET balance = 900 WHERE id = 'alice';
COMMIT;

FOR UPDATE takes the write lock at read time, so the read-modify-write becomes genuinely serial. This is pessimistic locking and it is the only one of the four that works when the modification is not expressible in SQL — parsing a JSONB document, calling a pricing service, applying a rule engine.

Costs: contention (writers queue), and it reintroduces exactly the deadlock from concurrency exercise 02 the moment one transaction locks two rows — so the same total-ordering discipline applies, and ORDER BY id on a multi-row FOR UPDATE is not a style preference.

3. A version column, and a retry (optimistic)

ALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 0;
 
-- read version 7, compute, then:
UPDATE accounts SET balance = 900, version = 8
 WHERE id = 'alice' AND version = 7;
-- 0 rows affected  =>  somebody else won. re-read and redo.

Nothing is locked; the write simply refuses to apply if the row moved underneath it. Correct at every isolation level including READ UNCOMMITTED, because it does not depend on isolation at all — the guarantee is in the predicate.

This is what every ORM's @Version does (Hibernate, JPA, ActiveRecord's lock_version), what HTTP If-Match and ETags are, and what conditional writes are in DynamoDB and Cosmos. Cost: you must actually write the retry loop, with a bound. Under heavy contention on one row it degrades badly, because every loser redoes its work. Optimistic concurrency is the right default for low-contention rows and the wrong one for a hot counter.

4. Raise the isolation level (correct, and the least useful)

BEGIN ISOLATION LEVEL REPEATABLE READ;

Postgres's REPEATABLE READ detects that B is writing a row whose version changed since B's snapshot began, and aborts it:

ERROR:  could not serialize access due to concurrent update

Correct. But note what you signed up for: the error arrives at UPDATE time, after your work is done, and you must retry — which means you have taken on fix 3's obligation without fix 3's explicitness, and a SQLSTATE 40001 handler is now load-bearing infrastructure. MySQL's REPEATABLE READ does not do this; it silently allows the lost update, so this fix is not portable.

Ranking

Prefer 1 when SQL can express the change. Use 2 when it cannot. Use 3 when the row is low-contention or the write crosses a network boundary. Reach for 4 only when you are raising the level for other reasons anyway — and never without a retry handler.

None of them is "use a transaction". BEGIN/COMMIT gives you atomicity and a consistent read snapshot. It does not give you mutual exclusion, and the lost update is a mutual-exclusion bug wearing a transaction costume.