What is the difference between Non-Repeatable Read and Phantom Read?

Question

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

I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom read?

####Transaction A

SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1

####OUTPUT:

1----MIKE------29019892---------5000

####Transaction B

UPDATE USERS SET amount=amount+5000 where ID=1 AND accountno=29019892;
COMMIT;

####Transaction A

SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1

Another doubt is, in the above example, which isolation level should be used? And why?

Answer 1

From Wikipedia (which has great and detailed examples for this):

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

and

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Simple examples:

• User A runs the same query twice.
• In between, User B runs a transaction and commits.
• Non-repeatable read: The A row that user A has queried has a different value the second time.
• Phantom read: All the rows in the query have the same value before and after, but different rows are being selected (because B has deleted or inserted some). Example: select sum(x) from table; will return a different result even if none of the affected rows themselves have been updated, if rows have been added or deleted.

In the above example,which isolation level to be used?

What isolation level you need depends on your application. There is a high cost to a "better" isolation level (such as reduced concurrency).

In your example, you won't have a phantom read, because you select only from a single row (identified by primary key). You can have non-repeatable reads, so if that is a problem, you may want to have an isolation level that prevents that. In Oracle, transaction A could also issue a SELECT FOR UPDATE, then transaction B cannot change the row until A is done.

Answer 2

A simple way I like to think about it is:

Both non-repeatable and phantom reads have to do with data modification operations from a different transaction, which were committed after your transaction began, and then read by your transaction.

Non-repeatable reads are when your transaction reads committed UPDATES from another transaction. The same row now has different values than it did when your transaction began.

Phantom reads are similar but when reading from committed INSERTS and/or DELETES from another transaction. There are new rows or rows that have disappeared since you began the transaction.

Dirty reads are similar to non-repeatable and phantom reads, but relate to reading UNCOMMITTED data, and occur when an UPDATE, INSERT, or DELETE from another transaction is read, and the other transaction has NOT yet committed the data. It is reading "in progress" data, which may not be complete, and may never actually be committed.

Answer 3

The Non-Repeatable Read anomaly looks as follows:

1. Alice and Bob start two database transactions.
2. Bob’s reads the post record and title column value is Transactions.
3. Alice modifies the title of a given post record to the value of ACID.
4. Alice commits her database transaction.
5. If Bob’s re-reads the post record, he will observe a different version of this table row.

The Phantom Read anomaly can happen as follows:

1. Alice and Bob start two database transactions.
2. Bob’s reads all the post_comment records associated with the post row with the identifier value of 1.
3. Alice adds a new post_comment record which is associated with the post row having the identifier value of 1.
4. Alice commits her database transaction.
5. If Bob’s re-reads the post_comment records having the post_id column value equal to 1, he will observe a different version of this result set.

So, while the Non-Repeatable Read applies to a single row, the Phantom Read is about a range of records which satisfy a given query filtering criteria.

Answer 4

Read phenomena

• Dirty reads: read UNCOMMITED data from another transaction
• Non-repeatable reads: read COMMITTED data from an UPDATE query from another transaction
• Phantom reads: read COMMITTED data from an INSERT or DELETE query from another transaction

Note : DELETE statements from another transaction, also have a very low probability of causing Non-repeatable reads in certain cases. It happens when the DELETE statement unfortunately, removes the very same row which your current transaction was querying. But this is a rare case, and far more unlikely to occur in a database which have millions of rows in each table. Tables containing transaction data usually have high data volume in any production environment.

Also we may observe that UPDATES may be a more frequent job in most use cases rather than actual INSERT or DELETES (in such cases, danger of non-repeatable reads remain only - phantom reads are not possible in those cases). This is why UPDATES are treated differently from INSERT-DELETE and the resulting anomaly is also named differently.

There is also an additional processing cost associated with handling for INSERT-DELETEs, rather than just handling the UPDATES.

Benefits of different isolation levels

• READ_UNCOMMITTED prevents nothing. It's the zero isolation level
• READ_COMMITTED prevents just one, i.e. Dirty reads
• REPEATABLE_READ prevents two anomalies: Dirty reads and Non-repeatable reads
• SERIALIZABLE prevents all three anomalies: Dirty reads, Non-repeatable reads and Phantom reads

Then why not just set the transaction SERIALIZABLE at all times? Well, the answer to the above question is: SERIALIZABLE setting makes transactions very slow, which we again don't want.

In fact transaction time consumption is in the following rate:

SERIALIZABLE > REPEATABLE_READ > READ_COMMITTED > READ_UNCOMMITTED

So READ_UNCOMMITTED setting is the fastest.

Summary

Actually we need to analyze the use case and decide an isolation level so that we optimize the transaction time and also prevent most anomalies.

Note that databases by default may have REPEATABLE_READ setting. Admins and architects may have an affinity towards choosing this setting as default, to exhibit better performance of the platform.

Answer 5

There is a difference in the implementation between these two kinds isolation levels.
For "non-repeatable read", row-locking is needed.
For "phantom read"，scoped-locking is needed, even a table-locking.
We can implement these two levels by using two-phase-locking protocol.

Answer 6

In a system with non-repeatable reads, the result of Transaction A's second query will reflect the update in Transaction B - it will see the new amount.

In a system that allows phantom reads, if Transaction B were to insert a new row with ID = 1, Transaction A will see the new row when the second query is executed; i.e. phantom reads are a special case of non-repeatable read.

Answer 7

• Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently).

• Phantom read is that a transaction reads the same table at least twice but the number of the same table's rows is different between the 1st and 2nd reads because other transactions insert or delete rows and commit at the same time(concurrently).

I experimented non-repeatable read and phantom read with MySQL and 2 command prompts.

For the experiments of non-repeatable read and phantom read, I set READ COMMITTED isolation level to occur non-repeatable read and phantom read:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

And, I created person table with id and name as shown below.

person table:

id	name
1	John
2	David

First for non-repeatable read, I did these steps below with MySQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person WHERE id = 2; 2 David		T1 reads David.
Step 4		UPDATE person SET name = 'Tom' WHERE id = 2;	T2 updates David to Tom.
Step 5		COMMIT;	T2 commits.
Step 6	SELECT * FROM person WHERE id = 2; 2 Tom		T1 reads Tom instead of David after T2 commits. *Non-repeatable read occurs!!
Step 7	COMMIT;		T1 commits.

Second for phantom read, I did these steps below with MySQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person; 1 John 2 David		T1 reads 2 rows.
Step 4		INSERT INTO person VALUES (3, 'Tom');	T2 inserts the row with 3 and Tom to person table.
Step 5		COMMIT;	T2 commits.
Step 6	SELECT * FROM person; 1 John 2 David 3 Tom		T1 reads 3 rows instead of 2 rows after T2 commits. *Phantom read occurs!!
Step 7	COMMIT;		T1 commits.

Answer 8

The accepted answer indicates most of all that the so-called distinction between the two is actually not significant at all.

If "a row is retrieved twice and the values within the row differ between reads", then they are not the same row (not the same tuple in correct RDB speak) and it is then indeed by definition also the case that "the collection of rows returned by the second query is different from the first".

As to the question "which isolation level should be used", the more your data is of vital importance to someone, somewhere, the more it will be the case that Serializable is your only reasonable option.

Answer 9

Lets say you have a table named Employee. With columns: ID, Name You have the following data in the table

ID   | Name
1    | Mike 
2    | Peter

1. You start a transaction.
2. You select all the records of Table: Employee
3. You lookup the name of the record with ID=1, the value is Mike
4. You lookup the number of records in the employee table. the value is 2.
5. In the same transction, now you again select all the records of the employee table and lookup the name attribute of the record with id=1, and lookup the number of records in the employee table. In Non-repeatable read scenario you cannot gurantee that the name value will be Mike. In Phantom read scenario you cannot gurantee number of employees will be 2 it might be more that 2.

Why? Because between step 4 and 5 some other database session might have modified the name value or added a new record to the table.

Now lets see how Mysql solves these problem with different database isolation level. In this post lets only consider the isolation levels: REPEATABLE_READ and SERIALIZABLE levels.

When database has REPEATABLE_READ isolation level. in step 2, mysql add a read lock to the selected rows. Note that this is not a table level lock so other sessions can still add new records to the table and commit but other sessions cannot delete or update the selected rows since these we have the read lock for the selected items. Even after the select query finish the read lock will remain active until we commit. because of this once you read certain rows in a transaction you will see same data in the already selected rows unless you modify it in the same transaction.

When database has SERIALIZABLE isolation level. in the step number 2 on top of read lock we also get a range lock. this range lock also will only be released when we commit the transaction. Range locks are used to prevent other transactions from inserting new records into the locked range.

Answer 10

I think there are some difference between Non-repeateable-read & phantom-read.

The Non-repeateable means there are tow transaction A & B. if B can notice the modification of A, so maybe happen dirty-read, so we let B notices the modification of A after A committing.

There is new issue: we let B notice the modification of A after A committing, it means A modify a value of row which the B is holding, sometime B will read the row again, so B will get new value different with first time we get, we call it Non-repeateable, to deal with the issue, we let the B remember something(cause i don't know what will be remembered yet) when B start.

Let's think about the new solution, we can notice there is new issue as well, cause we let B remember something, so whatever happened in A, the B can't be affected, but if B want to insert some data into table and B check the table to make sure there is no record, but this data has been inserted by A, so maybe occur some error. We call it Phantom-read.

Answer 11

non-repeatable read is an isolation level and phantom read (reading committed value by other transactions) is a concept (type of read e.g. dirty read or snapshot read). Non-repeatable read isolation level allows phantom read but not dirty reads or snapshot reads.

Answer 12

Both non-repeatable reads and phantom reads result from one transaction T1 seeing changes from another transaction T2 that commits before T1 is complete. The difference is that a non-repeatable read returns different values for the same logical row. (For example, if the primary key is employee_id, then a certain employee may have different salaries in the two results.) A phantom read returns two different sets of rows, but for every row that appears in both sets, the column values are the same.