Dirty reads 髒讀
Transaction 1 讀到了 Transaction 2 的未提交的值
READ COMMITED 可以避免這個問題
READ UNCOMMITTED 會有這個問題
A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.
Non-repeatable reads 不可重複讀
在同一個 Transaction 裡 不同的時間點 讀取了同一個 row 卻得到了不同的值（但是可能使用了不同的 SELECT 語句去讀到同一個 row）
REPEATABLE READ 可以避免這個問題
READ COMMITED 會有這個問題
A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.
When Transaction 2 commits successfully, which means that its changes to the row with id 1 should become visible. However, Transaction 1 has already seen a different value for age in that row. At the SERIALIZABLE and REPEATABLE READ isolation levels, the DBMS must return the old value for the second SELECT. At READ COMMITTED and READ UNCOMMITTED, the DBMS may return the updated value; this is a non-repeatable read.
Phantom reads 幻讀
有點類似 Non-repeatable reads
在同一個 Transaction 裡 不同的時間點 兩個相同但是先後執行的 SQL 卻得到了不同的結果
差別在於 Phantom reads 專門指沒有用 range locks 時
先後兩個同樣的 SELECT 卻返回了不同數量的結果
SERIALIZABLE 可以避免以上的三種問題
也可以說 non-repeatable reads 的原因是 UPDATE
而 phantom reads 的原因則是 INSERT 和 DELETE
A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.
READ UNCOMMITTED
允許 dirty reads
可能會讀到其他 transaction 尚未提交的數據
READ COMMITED
不會有 dirty reads
只會讀到其他 transaction 已經提交的數據
但是允許 non-repeatable reads
大部份資料庫默認的 isolation level 都是 READ COMMITED
REPEATABLE READ
不會有 dirty reads 和 non-repeatable reads
但是允許 phantom reads
MySQL 默認的 isolation level 是 REPEATABLE READ
但是 InnoDB 透過 next-key lock 也防止了 phantom reads
SERIALIZABLE
不會有 dirty reads、non-repeatable reads 和 phantom reads
讀寫互斥
所以讀和寫都只能一個一個依序執行