29

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.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Rollerball
  • 12,618
  • 23
  • 92
  • 161

5 Answers5

27

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.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • How can It be possible though? I mean after the transaction is complete the only way to prevent not-repeatable read is to keep the lock even after committed... Anyway sooner or later another connection (B) will be done and how is it going to prevent the update of that row previously retrieved by A – Rollerball Aug 18 '13 at 09:34
  • Or perhaps only 1 connection per time might write in the DBMS. So as long as there is an active connection to a DBMS no other connection can actually update the DB for those particular rows.(till the first connection get closed) Am i right? – Rollerball Aug 18 '13 at 09:40
  • 2
    A repeatable-read is guaranteed only for the duration of a transaction: you start a transaction T1, read A1, then another transaction T2 modifies A1 to A2 and commits, then the first transaction T1 re-reads the value, and still reads A1. That's a repeatable-read. Of course, another transaction T3 that reads the value for the first time after T2 has committed will read the committed value, A2. To know how it's implemented by your database, read its documentation. Most of them use [MVCC](http://en.wikipedia.org/wiki/Multiversion_concurrency_control), AFAIK. – JB Nizet Aug 18 '13 at 09:46
17

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • How can It be possible though? I mean after the transaction is complete the only way to prevent not-repeatable read is to keep the lock even after committed... Anyway sooner or later another connection (B) will be done and how is it going to prevent the update of that row previously retrieved by A – Rollerball Aug 18 '13 at 09:35
  • Or perhaps only 1 connection per time might write in the DBMS. So as long as there is an active connection to a DBMS no other connection can actually update the DB for those particular rows.(till the first connection get closed) Am i right? – Rollerball Aug 18 '13 at 09:40
  • A Non-repeatable read is where one transaction cannot read the second time unless another transaction alters the row.While performing select statement, non-repeatable reads my occur when read locks are not acquired. Non-repeatable reads may occur at the need of effected transaction by a commit conflict, must roll back is relaxed. – Rahul Tripathi Aug 18 '13 at 09:44
5

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
1

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

touchstone
  • 1,055
  • 1
  • 10
  • 14
  • This has easy explanation about these read anomaly: https://stackoverflow.com/questions/11043712/what-is-the-difference-between-non-repeatable-read-and-phantom-read – Gowtham Jan 16 '22 at 07:30
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.
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129