Comprehensive Understanding of Transaction Isolation Levels

Section 1: Transaction Isolation Levels

  • 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.
  • Non-repeatable read: SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
  • Phantom: SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same , it obtains a different collection of rows.
A1 Dirty read: w1[x] ... r2[x] ... (a1 and c2 in any order)
A2 Fuzzy read: r1[x] ... w2[x] ... c2 ... r1[x] ... c1
A3 Phantom read: r1[P] ... w2[y in P] ... c2 ... r1[P] ... c1
P1 Dirty Read: w1[x]...r2[x]...(c1 or a1)
P2 Fuzzy Read: r1[x]...w2[x]...(c1 or a1)
P3 Phantom: r1[P]...w2[y in P]...(c1 or a1)
P4 Lost update: r1[x]...w2[x]...w1[x]...c1
A5A Read skew: r1[x]… w2[x]... w2[y]… c2… r1[y]… (c1 or a1)
A5B Write skew: r1[x]… r2[y]… w1[y]… w2[x]… (c1 and c2 occur)
A5B2 Write skew 2: r1[P]... r2[P]… w1[y in P]... w2[x in P]...(c1 and c2 occur)
r1[x=50] r2[x=50] w2[x=60] c2 w1[x=70] c1
(x+y=100) r1[x=50] w2[x=10] w2[y=90] c2 r1[y=90] c1
(x+y>=60) r1[x=50] r2[y=50] w1[y=10] c1 w2[x=10] c2
(count(P)<=4):r1[count(P)=3],r2[count(P)=3],insert1[x in P],insert2[y in P],c1,c2,

Section 2: Isolation Level Implementation

2.1 Lock-based Isolation Level Implementation

  • Item lock: Lock the accessed row to prevent dirty or fuzzy reads.
  • Predicate lock (gap lock): Lock the search scope. If a full table is scanned, lock the whole table to prevent phantom reads.
  • Short duration: Release a lock after a statement ends.
  • Long duration: Release a lock after a transaction is committed or rolled back.

2.2 Oracle Isolation Level Implementation

2.3 MySQL (InnoDB) Isolation Level Implementation

Section 3: MySQL (X-Engine) Isolation Level Implementation

Section 4: Summary

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com