CODEWITHSUNDEEP
javadatabasejdbctransactionsnon-repeatable-read
Rollerball
Rollerball

Reputation: 13118

Dirty read vs Non-repeatable read

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.

Upvotes: 33

Views: 22620

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

  • Dirty read is that a transction reads the uncommitted data which other transactions insert, update or delete.

  • 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).

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

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

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

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

person table:

id name
1 John
2 David

First for dirty read, I did these steps below with SQL 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 SELECT * FROM person WHERE id = 2;

2 Tom		 T1 reads Tom instead of David before T2 commits.

*Dirty read occurs!!
Step 6 COMMIT; T2 commits.
Step 7 COMMIT; T1 commits.

Second 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.

Upvotes: 2

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154190

A picture is worth 1000 words.

Non-Repeatable Read

In the diagram above, the flow of statements goes like this:

  1. Alice and Bob start two database transactions.
  2. Alice modifies the title of a given post record.
  3. Bob reads the uncommitted post record.
  4. If Alice commits her transaction, everything is fine. But if Alice rolls back, then Bob will see a record version that no longer exists in the database transaction log.

This anomaly is only permitted by the Read Uncommitted isolation level, and, because of the impact on data integrity, most database systems offer a higher default isolation level.

Upvotes: 6

touchstone
touchstone

Reputation: 1085

i had the same confuse as you do before.

after i read the answers in your post, i decide to find out from mysql doc.

after read the doc from mysql, i think what confuse us is the understading angle. we think that "tran A change one record with no commit, and tran B reads two different data before and after, which indeed a 'dirty data' and 'non-repeatable read'", what we confuse is because we learn it from the result of the two transactions' behavior.

BUT, the correct angle is: "dirty read"' is a TWO-trasanction thing,whereas "non-repeatable read" is totally a ONE-transaction thing.

What that means? for exsample, if you are a transaction, and i am a transaction after you. you read a X, and i update it to Y, then you read again.

TO US, you have read a dirty data, because i didn't commit, maybe i want to rollback. i make you read the dity data.

TO YOU youself, in your own transaction, you read two different data, it's a non repeatable data.

a little bit verbose. may it helps.

refs: 1. https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read
2. https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_non_repeatable_read

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

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.

Upvotes: 18

JB Nizet
JB Nizet

Reputation: 692231

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.

Upvotes: 35

Related Questions