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

by

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.

More Related Contents:

  1. What is a database transaction?
  2. Can we update primary key values of a table?
  3. Oracle (ORA-02270) : no matching unique or primary key for this column-list error
  4. How to display table data more clearly in oracle sqlplus
  5. How do ACID and database transactions work?
  6. Liquibase lock – reasons?
  7. How SID is different from Service name in Oracle tnsnames.ora
  8. Are foreign keys really necessary in a database design?
  9. Is it possible to generate django models from the database?
  10. Do stored procedures run in database transaction in Postgres?
  11. What is a View in Oracle?
  12. Business Logic: Database or Application Layer
  13. Convert String ISO-8601 date to oracle’s timestamp datatype
  14. PostgreSQL Database Log Transaction
  15. What is the default Precision and Scale for a Number in Oracle?
  16. Oracle: How do I convert hex to decimal in Oracle SQL?
  17. Knex Transaction with Promises
  18. Display dynamic image from database or remote source with p:graphicImage and StreamedContent
  19. Primary keys with Apache Spark
  20. What are database normal forms and can you give examples? [closed]
  21. Best way to test a MS Access application?
  22. Transaction isolation levels relation with locks on table
  23. How to backup sqlite database?
  24. How to cancel a long-running Database operation?
  25. Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?
  26. Ideas on database design for capturing audit trails [closed]
  27. What can cause an Oracle ROWID to change?
  28. I need a client side browser database. What are my options [closed]
  29. Should I commit or rollback a read transaction?
  30. “Cannot drop database because it is currently in use”. How to fix?

Leave a Comment