lecture 10 granularity of locks degrees of consistency l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lecture 10: Granularity of Locks & Degrees of Consistency PowerPoint Presentation
Download Presentation
Lecture 10: Granularity of Locks & Degrees of Consistency

Loading in 2 Seconds...

play fullscreen
1 / 27

Lecture 10: Granularity of Locks & Degrees of Consistency - PowerPoint PPT Presentation


  • 256 Views
  • Uploaded on

Lecture 10: Granularity of Locks & Degrees of Consistency. Sept. 27, 2006 ChengXiang Zhai. Most slides are adapted from Kevin Chang’s lecture slides. Transactions. Transactions: each as a “program” of atomic database operations atomic units of database transformation & recovery

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lecture 10: Granularity of Locks & Degrees of Consistency' - lavinia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lecture 10 granularity of locks degrees of consistency

Lecture 10: Granularity of Locks &Degrees of Consistency

Sept. 27, 2006

ChengXiang Zhai

Most slides are adapted from Kevin Chang’s lecture slides

transactions
Transactions
  • Transactions:
    • each as a “program” of atomic database operations
    • atomic units of database transformation & recovery
  • Consistency guaranteed if:
    • each transaction is “well behaved”
      • start/end with consistent DB
    • each transaction runs in serial
      • no interleaving to mess up
acid properties of transactions
ACID Properties of Transactions
  • Atomicity:
    • all or nothing at all
  • Consistency
    • from one consistent state to another
  • Isolation
    • as if executed alone
  • Durability
    • results permanent after commit
transaction concurrency
Transaction Concurrency
  • Concurrency control:
    • ensures that transactions are interleaved correctly

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B + 1

write(B)

read (B)

B = B * 2

write (B)

transaction interleaving
Transaction Interleaving
  • Before: A = 0, B = 0, then?
  • Correct schedule? Wrong schedule? Why?

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B + 1

write(B)

read (B)

B = B * 2

write (B)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B * 2

write(B)

read (B)

B = B + 1

write (B)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(B)

B = B + 1

write(B)

read(A)

A = A * 2

write(A)

read(B)

B = B * 2

write(B)

schedule 1

schedule 2

schedule 3

transaction interleaving6
Transaction Interleaving
  • Schedule 1 (interleaved, more concurrent) = Schedule 3 (serial)
  • Consistency and isolation: transform DB in serial
    • (A=0,B=0) --- T1 --> (A=1,B=1) -- T2 --> (A=2,B=2)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B + 1

write(B)

read (B)

B = B * 2

write (B)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(B)

B = B + 1

write(B)

read(A)

A = A * 2

write(A)

read(B)

B = B * 2

write(B)

schedule 1

schedule 3

dependency of transactions
Dependency of Transactions
  • Dependencies: defining ordering of transactions: rw, wr, ww
  • Serialization graph: Ti --> Tj for a dependency from Ti to Tj
  • Serializable if ... (called conflict serializable)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B * 2

write(B)

read (B)

B = B + 1

write (B)

Xact T1Xact T2

read(A)

A = A + 1

write(A)

read(A)

A = A * 2

write(A)

read(B)

B = B + 1

write(B)

read (B)

B = B * 2

write (B)

T1

T1

T2

T2

schedule 1

schedule 2

atomic actions
Atomic Actions
  • Why only reads and writes?
  • How does CC know what actions are?
  • Will knowing more “action semantics” help?
more semantics of atomic actions
More “Semantics” of Atomic Actions
  • Atomic action:
    • inc(x, i): read(x), x = x + i, write(x)
  • Any dependencies between T1 and T2?

Xact T1Xact T2

inc(A,1)

inc(A,2)

inc(B,1)

inc(B,2)

locking protocol
Locking Protocol
  • Locking: a “protocol” for accessing data
    • well-formed xacts lock/unlock “access units” before/after using them
    • lock manager grants/manages locks
  • Goals of locking protocol
    • ensure serializability
    • preserve high concurrency
  • Parameters of a locking protocol?
locking protocol parameters
Locking Protocol: Parameters
  • What “modes” of locks to provide? Compatibility?
    • e.g., S for shared, X for exclusive
  • What “units” to lock?
    • database? table? tuple? what else?
  • How to “well-behave” to obtain and hold locks?
    • in what sequence?
    • how long to hold?
motivation a simple protocol
Motivation: A “Simple” Protocol
  • Lock modes:
    • S for shared and X for exclusive access
    • compatibility: (S, S) = T, otherwise F
  • Unit:
    • a relation
  • Behavior:
    • lock (the maximal mode) before access
    • unlock immediately after
simple protocol what s wrong
Simple Protocol: What’s Wrong?

Xact T1Xact T2

X.lock(A’s relation)

read(A)

A = A + 1

write(A)

X.unlock(A’s relation)

X.lock(A’s relation)

read(A)

A = A * 2

write(A)

X.unlock(A’s relation)

X.lock(B’s relation)

read(B)

B = B + 1

write(B)

X.unlock(B’s relation)

X.lock(B’s relation)

read (B)

B = B * 2

write (B)

X.unlock(B’s relation)

simple protocol serializability
Simple Protocol: Serializability?
  • Serializability?
    • fail to interleave dependencies in same order
  • Solution: 2PL protocol
    • for well-formed xacts, legal scheduler: 2PL --> serializable

Xact T1Xact T2

X.lock(A)

X.unlock(A)

X.lock(A)

X.unlock(A)

X.lock(B)

X.unlock(B)

X.lock(B)

X.unlock(B)

Xact T1Xact T2

X.lock(A)

X.lock(B)

X.unlock(A)

X.lock(A)

X.unlock(B)

X.lock(B)

X.unlock(A)

X.unlock(B)

simple protocol granularity
Simple Protocol: Granularity?
  • Unit of locking: How to increase concurrency?
    • coarse units? fine units?
  • Granularity:
    • concurrence vs. overhead
    • hierarchical lockable units:
      • DB, areas, files, pages, tuples, attributes
  • Correctness problem:
    • T1 S.locks a tuple, T2 X.locks the file?
    • solution?
granularity locking
Granularity Locking
  • Database: as hierarchy of lockable units
  • Locking: to lock a unit,
    • first lock all containing units with “intension”
      • why?
    • intension locks: IS, IX, SIX (intension to upgrade)
    • why not just “I” ?
  • Unlocking:
    • release all relevant locks at once, or leaf to root
granularity locking dag
Granularity Locking: DAG
  • Generalization: DAG of units:
    • S locks at least one path to the node
    • X locks all paths to the node
  • Q: Why implicit S if one parent is X (or S, SIX)?
lock compatibilty table
Lock Compatibilty Table

X

SIX

S

IX

IS

NL

privilege ordering

compatibility examples
Compatibility Examples
  • Questions:
    • why SIX is useful?
    • (SIX, S) = No? (SIX, IS) = Yes? (SIX, IX) = No?

Grant S? IS? IX?

SIX

DB

SIX

SIX

Relation Student

Relation Enrollment

X

X

Tuple

Tuple

Tuple

Tuple

consistency dirty data based
Consistency: Dirty-Data Based
  • How to lock for each degree?

T does not overwrite dirty data of other xacts

0

T does not commit any writes until EOT

1

T does not read dirty data from other xacts

2

other xacts do not dirty any data read by T

before T completes

3

consistency locking based
Consistency: Locking-Based
  • Corresponding to each condition:
    • T does not overwrite dirty data of other xacts
      • set write locks on dirty data (well-formed on w)
    • T does not commit any writes until EOT
      • set “long” write locks (2P/EOT on w)
    • T does not read dirty data from other xacts
      • set read locks (well-formed on r)
    • other xacts do not dirty any data read by T before T completes
      • set “long” read locks (2P/EOT on r)
how long to hold a lock
How “long” to hold a lock?
  • 2PL requirement:
    • only “shrink” after “growing”
      • hold locks until no more new locks will be made
    • why doing so?
    • problem?
  • End of transaction:
    • unlock (to make data accessible) at xact commit
    • why?
examples
Examples
  • Lost updates
    • T1: w(A) abort
    • T2: w(A)
    • ? what degree can prevent T1.abort from masking T2.w(A)?
  • Dirty Reads:
    • T1: w(A) abort
    • T2: r(A)
    • ? what degree can prevent T2.r(A) from reading bogus A?
  • Inconsistent Reads
    • T1: w(A)
    • T2: r(A) r(A)
    • ? what degree can ensure repeated reads will be consistent?
additional issues
Additional Issues
  • Deadlock?
  • Concurrency control by timestamps (optimistic=assume rare unserializable behavior)
    • Timestamping
    • Validation
cc support in practice
CC Support in Practice

IBM DB2:

  • Optional explicit locking at table level
    • LOCK TABLE students in SHARE/EXCLUSIVE MODE
    • locks held until and automatically released at EOT
  • Automatic implicit locking with “isolation level” setting

SQL transaction isolation levels:

    • serializable
    • repeated read
      • serializable except not lock indexes, thus phantom insert possible
    • read committed
      • set write/read locks
    • read uncommitted
      • set write but not read locks

(somewhat different names in DB2)

  • For more information, see Jim Gray’s well known book “Transaction Processing”
what you should know
What You Should Know
  • Why is granularity of locks necessary?
  • How is granularity of locks achieved?
  • What is two-phase locking?
  • What are the four degrees of consistency?
  • How are the degrees of consistency related to locking
carry away messages
Carry Away Messages
  • Once again, we see a messy problem gets formulated nicely
    • Look for important, but messy problems
  • A solution that captures the essence of a problem has a long-lasting impact
    • Observe the need for granularity -> a good solution to the problem
  • Cross-field advantages (OS-> DB)