Lecture 26

Lecture 26#

Announcements#

  • This is our last class!

SQL Isolation Levels#

  • read uncommitted;

    • Not supported in Postgresql

  • read committed;

    • Does not allow read of uncommitted values, but the transaction may read the same value twice and get different results

  • repeatable read ;

    • Does not allow the same read producing different results, but it allows phantom updates (changes that are not directly read but impact the query results)

  • serializable ;

    • Does not allow phantom updates either.

-- T1:
begin transaction ;
set transaction isolation level read uncommitted;
   --not implemented
set transaction isolation level read committed;
set transaction isolation level repeatable read ;
set transaction isolation level serializable ;

commit ;
end;

--- T2
begin transaction ;

commit ;
end;

Implementing serializability#

Two main approaches:

  • Locking mechanism: 2 Phase locking

  • Optimistic mechanism: Multi-version control (check at commit time if a change should be allowed)

Two phase locking#

  • Lock any item before a read or write operation

    • If the lock is available, xact can get it and continue processing

    • If the lock is not available, xact must wait until it becomes available

    • Only a single transaction can lock an item at any point in time

  • A transaction can get new locks if it is in the growing phase

    • Transactions cannot release locks in the growing phase

  • A transaction can release locks if it is in the shrinking phase

    • Transactions cannot get any more locks in the shrinking phase

  • As soon as a transaction in the growing phase releases a lock, it enters the shrinking phase.

  • Two phase locking guarantees serializability!

  • If a transaction management system uses 2PL (two phase locking), then all the schedules produced by this system are guaranteed to be serializable.

  • Example:

  r1(x) r2(x) w1(x)
  
  Conflicts:
  r1(x) w2(x)
  r2(x) w1(x)

Not serializable, there is a cycle.

If I were using 2PL, this schedule is not possible.

lock1_(x) r1(x) unlock1(x) lock2(x) r2(x) unlock2(x) XXXXXw1(x) 
                ^T1 enters                           Not possible
                shrinking phase                      T1 in shrinking
                and has no lock on x

  • 2PL guarantees serializability

    • Problems may occur with deadlocks and lock granularity (for higher concurrency)

Locking with shared/exclusive locks#

  • Two types of locks

    • Read lock, shared lock (S lock)

      • To read an item, transactions must obtain a shared lock (S)

      • Multiple transactions can hold an S lock on the same item

    • Write lock, exclusive lock (X lock)

      • To write an item, transaction must obtain an exclusive lock (X)

      • If T1 exclusively locks an item, no other transaction can hold any lock lock on this item

    • If a transaction is the only one holding an S lock, then it can upgrade it to a write lock without unlocking (otherwise you will enter your shrinking phase)

  • These two schedules are possible under 2PL with two types of locks.

sl1(x) r1(x) xl1(x) w1(x)

sl1(x) r1(x) sl2(x) r2(x) unlock2(x) xl1(x) w1(x)

  • This transaction is not possible because T2 has entered shrinking phase when it unlocked x and not needs to lock x again to write x. Not possible!

sl1(x) r1(x) sl2(x) r2(x) unlock2(x) xl1(x) w1(x) unlock1(x)   ???? not possible w2(x)

DB Tuning with indexing#

  • Queries and their cost ( Cost(Q) )

  • Target queries that are very costly or very frequntly asked or both

  • Workload: Sum over all queries ( Cost(Q) * Frequency(Q) )

Contents