MySQL dirty reads vs dirty reads defined in SQL standard

Question

From MySQL manual:

READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

The bolded part is confusing me. "Earlier version of a row might be used"? Used where? And how this can be called a "dirty read"?

SQL standard says dirty read contains data that has never existed:

P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

Is MySQL manual wrong here or what?

You changed the words! _may be considered to have never existed_ != _has never existed_ — Lightness Races in Orbit, Jan 22 '19 at 17:09

score 1 · Answer 1 · answered Jan 22 '19 at 17:06

The MySQL definition is one case of dirty read: The case when the row already exists (but is being updated by T1) and T2 reads it. Then if T1 rolls back, T2 may have performed wrong calculations with it. Ugly.

The SQL definition cover more cases. For example when the rows is being created for the first time by T1 (but not yet committed), and then is read by T2. In this case, if the transaction T1 is rolled back, it's like the row never existed.... but T2 is using it already for who knows what purposes. Ugly.

In sum, both cases are ugly and can lead to inconsistencies. The SQL Definition is more comprehensive, than MySQL's.

Isn't the latter case you provided possible in MySQL too? – Croco Jan 23 '19 at 20:23 — Croco, Jan 23 '19 at 20:23

MySQL dirty reads vs dirty reads defined in SQL standard

1 Answers1