CSE 344: Section 5

Transactions and �Isolation Levels

April 30, 2020

​

Conflicts

READ-WRITE

WRITE-READ

WRITE-WRITE

​

Intra-transactional: don’t mess with the order of operations within a transaction

}

Inter-transactional: represent with a conflict graph

Serializability

“Conflict serializable” is a stronger constraint than “serializable”

I.e. Any schedule that is conflict serializable must be serializable.

​

​

Serializable

Conflict

Serializable

Easy to check

Not easy to check

Serializability

Checking for conflict serializability -> precedence graph and cycle checking

Serializability

S1: w1(Y); w2(Y); w1(X); w2(X); w3(X)

S2: w1(Y); w2(Y); w2(X); w1(X); w3(X)

Are these serializable? Conflict serializable?

Serializability

S1: w1(Y); w2(Y); w1(X); w2(X); w3(X)

S2: w1(Y); w2(Y); w2(X); w1(X); w3(X)

Conflict Serializable

Serializable (but not conflict serializable)

2PL v.s. Strict 2PL

2PL:

• In every transaction, all lock requests must precede all unlock requests
• Ensure conflict serializability
• Might not be able to recover (Dirty Read: Read on some write that gets rolled back)

Strict 2PL:

• Every lock each transaction is held until commit or abort
• Ensure conflict serializability
• Recoverable as each transaction does not affect others until commit/abort

2PL v.s. Strict 2PL

Invalid reads

Trickled unlock and transaction end

2PL Variants

Isolation Level: Read Uncommitted

Write Locks? Strict 2PL

Read Locks? None (Immediate Read)

Problem: Dirty-Read

Reading uncommitted data that can be rolled back

SET TRANSACTION ISOLATION LEVEL... READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Isolation Level: Read Uncommitted

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.

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

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

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 Level: Summary

Usually not the default!

Isolation Levels Reinforced

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