What is the difference between non-repeatable read and phantom read?

Non-repeatable read and phantom read are two different types of anomalies that can occur during concurrent data access in databases.

• Non-repeatable read: occurs when, within a single transaction, the same query to the same record returns different data because another transaction has modified or deleted that record and committed the changes.

• Phantom read: occurs when, within a single transaction, a repeated query to a table with a condition returns a different number of rows because another transaction has added or deleted rows satisfying the condition.

Example:

• Non-repeatable read: a transaction reads a row with id=1, then another transaction updates this row and commits, then the first transaction reads the row with id=1 again and sees the changed data.

• Phantom read: a transaction reads all rows where status='active', then another transaction adds a new row with status='active' and commits, then the first transaction reads the rows with status='active' again and sees the new "phantom" row.

Thus, non-repeatable read is related to changes in existing rows, while phantom read involves the appearance or disappearance of new rows matching the condition.