Why do new rows show up when I run the same query twice inside one transaction?

Nothing in the table is an error report. Both executions succeed, and both return rows that were committed when they were read. The transaction has simply been given two different answers to one question, because the question is a predicate rather than a row identity. That is why the anomaly can pass every individual assertion in the code and still break a report or a check-then-act decision.

It is tempting to say that locking the rows the first query returned will stop this. It will not. Locks on 120, 240 and 410 protect those row identities, but the row that causes the trouble is 310, which did not exist when the locks were taken and therefore could not be locked. T2's insert into 310 does not conflict with any lock on 120, 240 or 410 because it does not touch those rows. The lock has to cover the condition $amount > 100$, not just the rows that happen to satisfy it now. That is a predicate lock or a range lock, or the coarser mechanism a serialisable isolation level uses; it is more expensive because it constrains inserts that are not yet in the table.

This is different from reading the same row twice and getting two values. There the row identity is fixed and a row lock is enough. Here the row identity 310 is new, so a row lock on anything already read cannot help. The enabling condition is precise: the transaction must read a range or a non-unique condition twice, and another transaction must commit an insert that satisfies that condition between the two reads. It does not need a read of uncommitted data, and it does not need the transaction to write anything back. The anomaly is about the range, not about the value of any individual row.

Under read committed, each statement gets a fresh view, so the second run can see the committed insert; that is the usual way this appears. Under repeatable read the answer depends on the implementation: some databases give every plain SELECT in the transaction one snapshot and the extra row will not appear, while others still permit phantoms or only protect rows already read. Under serialisable this anomaly is eliminated, at the cost of making the condition itself stable and potentially blocking or aborting the inserting transaction. If the insert is not committed, or if it is rolled back, it is not visible at read committed and above, so it cannot cause the second run to grow.

In a check-then-act pattern, count the bookings for a room and insert one if the count is below a limit. The count can be 3 on the first run and 4 on the second, and a decision based on the first count is already stale. The remedy is to run that pattern at a level that fixes the predicate, or to take an explicit lock on the condition, not merely on the rows the count returned.