Database Isolation

為什麼DB會需要Isolation ?

主要是為了避免在並發底下可能造成的dirty reads or lost updates

以下表個可以清楚看到不同的層級(Isolation levels)解決了什麼問題(read phenomena)

Isolation levels vs read phenomena

'+' — possible
'-' — not possible
Read phenomena Isolation level Dirty reads Lost updates Non-repeatable reads Phantoms
Read Uncommitted + + + +
Read Committed - + + +
Repeatable Read - - - +
Serializable - - - -

Isolation levels

Read uncommitted

最低等級,在這情況下dirty reads是被允許的。

e.g. 可以在A transaction 可以看到 B transaction 還沒commit的更改。

Read committed

  • 這邊保證只會讀去到commit的資料,但不保證transaction當中相同query讀去到一樣資料(Non-repeatable reads)。
  • lock
  • write

Repeatable reads

  • 這可以保證兩次一樣query拿到相同資料,但並沒有 range-locks 因此可能會有 phantom reads 問題
  • locak
    • read
    • write


  • 最高等級
  • lock
    • read
    • write
    • range-locks


Author image

About Johnny.Xie

You've successfully subscribed to Johnnnnnny Blog
Great! Next, complete checkout for full access to Johnnnnnny Blog
Welcome back! You've successfully signed in.
Unable to sign you in. Please try again.
Success! Your account is fully activated, you now have access to all content.
Error! Stripe checkout failed.
Success! Your billing info is updated.
Error! Billing info update failed.