There's a difference between a transaction that locks for writes and one that locks for reads. By default the reading is not locked in this (common) case.

That's a terrible default! Ignoring read-write races is a surefire way to... well, create bugs like the one in the post.

The common case is going to be many readers with a few or even a single writer. In that scenario, a read locked database would destroy any chance of operating at scale.

Many DBMS have a SELECT FOR UPDATE statement just for those cases you want to lock everybody out while just reading.

For a lot of RDBMS, transaction semantics can be later with different transaction isolation levels. There levels like repeatable read which work in this way, but the default level is usually weaker.