CSE 414: Section 5
Isolation Levels
February 6th, 2020
2PL v.s. Strict 2PL
2PL:
Strict 2PL:
Isolation Level: Read Uncommitted
Write Locks? Strict 2PL
Read Locks? None (Immediate Read)
Problem: Dirty-Read
Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads.
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Isolation Level: Read Uncommitted
Example Transaction:
T2 is reading value of A updated by T1’s write on A, but T1 has not committed yet.
The value of A read by T2 might not even be in the result.
Then T2’s action can be influenced by such uncommitted data.
T1 | T2 |
W(A) | |
| R(A) |
| W(B) |
| Commit |
R(B) | |
Commit | |
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Isolation Level: Read Committed
Write Locks? Strict 2PL
Read Locks? Obtain before read, release after (No more dirty read)
Problem: Unrepeatable Read
The values of 2 reads on the same tuple can be different in the same transaction
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Isolation Level: Read Committed
Example Transaction:
T1’s first R(A) and T1’s second R(A) might have different results.
Updated by T2’s W(A).
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Isolation Level: Repeatable Read
Write Locks? Strict 2PL
Read Locks? Strict 2PL (No more unrepeatable read)
“The transaction waits until rows write-locked by other transactions are unlocked; this prevents it from reading any "dirty" data.”
Problem: Phantom Read
In the same transaction, some tuples appear sometimes and disappear other times
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
STRICT 2PL READS MEAN MY TXNS ARE SERIALIZABLE
I CAN INSERT A NEW ITEM WHENEVER
Isolation Level: Repeatable Read
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Finds A1, A2
Finds A1, A2, A3
OK since A3 did not exist before
Isolation Level: Serializable
Not the same thing as a serial schedule!!!
Write Locks: Strict 2PL
Read Locks: Strict 2PL
Additional Predicate Lock/Table Lock (No Phantom)
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Side Note
The previous slide stated that the phantom problem can be resolved with table locking. Note that all the previous slides are using a tuple locking mechanism in which we lock individual tuples. If we lock the entire table then there is no phantom problem because you cannot insert into a locked table.
A predicate lock gets the benefits of tuple locking and avoid the phantom problem.
Isolation Level: Serializable
Predicate Lock Example:
In Transaction T, we have a statement:
SELECT * FROM People WHERE age > 18;
In this case, the transaction will grab a predicate lock that prevents inserting and deleting tuples that can affect the predicate/statement.
In this case, the lock prevents inserting and deleting tuples with age > 18.
SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Isolation Levels Reinforced
Isolation Level | Read Lock Behavior | Conflict Example | Conflict Types |
Read Uncommitted | No Read Locks | WR or RW conflict | “Dirty reads” |
Read Committed | Short-Duration Read Locks | RWR conflict | “Unrepeatable reads” |
Repeatable Read | Long-Duration Read Locks (Strict 2PL) | Phantom conflict | Phantom problem |
Serializable | Predicate Locks | Serializable | None |
Isolation Level: Summary
Make sure you can associate the following:
Isolation level name <-> Main problem <-> Locking protocol
Practice:
Identifying isolation levels from a schedule
Coming up with a schedule from an isolation level
RA Operators
Standard:
⋃ - Union
⎼ - Diff.
σ - Select
π - Project
⍴ - Rename
Extended:
δ - Duplicate Elim.
ɣ - Group/Agg.
τ - Sorting
Joins:
⨝ - Nat. Join
⟕ - L.O. Join
⟖ - R.O. Join
⟗ - F.O. Join
✕- Cross Product
⋂ - Intersect
R1⋂R2 = R1–(R1–R2)
R1⋂R2 = R1⨝R2
15