Difference between non-repeatable read vs dirty read

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

A picture is worth 1000 words.

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.

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.

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.