Could you explain what is meant by Phantom read in the context of transactions and databases?

A phantom read is a situation in transactions where, upon re-executing the same query within a transaction, new rows appear that were not visible during the first read.

Example:

1. Transaction T1 executes the query SELECT * FROM orders WHERE status = 'new' and retrieves 5 records.
2. While T1 is not finished, another transaction T2 adds a new record with status = 'new' and commits the change.
3. If T1 repeats the same query, it will see 6 records — the new row that appeared as a "phantom".

Phantom reads occur because the range of rows matching the query condition changed between the two reads.

To prevent phantom reads, the SERIALIZABLE isolation level is used, which locks data ranges, preventing new rows from appearing between reads. At the READ COMMITTED or REPEATABLE READ levels, phantom reads can occur.