Can you explain what phantom read phenomenon means in the context of transactions?

The phantom read phenomenon occurs in the context of database transactions when a repeated query within the same transaction returns different results because another transaction has added or deleted rows that match the selection condition.

For example, if in the first part of a transaction we select all records with a certain condition, and then in the second part we repeat the same query, new rows (phantoms) may appear due to parallel changes in the database that were not visible during the first read.

This is related to the transaction isolation level: phantom reads are possible at the READ COMMITTED level but are prevented at the SERIALIZABLE level.

Example:

-- Transaction 1
BEGIN;
SELECT * FROM orders WHERE status = 'pending'; -- returned 5 records
-- Transaction 2 concurrently adds a new record with status = 'pending'
SELECT * FROM orders WHERE status = 'pending'; -- now returns 6 records
COMMIT;