Isolation Levels

SQLite mostly avoids this by letting one writer at a time. Postgres lets many, which is the point, and that means you have to think about what two simultaneous transactions can see of each other.

Read committed, the Postgres default, means each statement sees data committed before that statement began. Two statements in the same transaction can therefore see different data.

Repeatable read means the whole transaction sees one consistent snapshot.

Serializable behaves as if transactions ran one after another, and may abort one with a serialization failure, which your code must be ready to retry.

The classic bug this causes. Read the current count, decide there is space, insert a signup. Two people do it at the same moment, both read the same count, both decide there is space, and the workshop is now over capacity.

Nothing about that code looks wrong, and it works perfectly in every test you run by yourself.