Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between non-repeatable read vs dirty read

Tags:

From this oracle java tutorial:

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

What's the difference between a dirty read and the non-repeatable read? Is it not the same thing? reading the wrong result due to others' updating?

Thanks in advance.

like image 921
Rollerball Avatar asked Aug 18 '13 09:08

Rollerball


People also ask

What are dirty reads non-repeatable reads and phantom reads?

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.

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

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.

What is non-repeatable read in database?

Nonrepeatable Reads A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete.

What is the difference between repeatable read and read committed?

The REPEATABLE READ transaction will still see the same data, while the READ COMMITTED transaction will see the changed row count. REPEATABLE READ is really important for reporting because it is the only way to get a consistent view of the data set even while it is being modified.


2 Answers

The exact same page explains what a dirty read is:

Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.

So, non-repeatable read consists in reading two different committed values, whereas dirty read consists in reading a value that hasn't been committed yet. Quite different.

like image 137
JB Nizet Avatar answered Oct 22 '22 00:10

JB Nizet


From here:-

Dirty Reads occur when one transaction reads data written by another, uncommitted, transaction. The danger with dirty reads is that the other transaction might never commit, leaving the original transaction with "dirty" data.

Non Repeatable Reads occur when one transaction attempts to access the same data twice and a second transaction modifies the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable.

like image 38
Rahul Tripathi Avatar answered Oct 22 '22 00:10

Rahul Tripathi