Granularity of Locks and Degrees of Consistency in a Shared Data Base

J. N. Gray, R. A. Lorie, G. R. Putzolu, and I. L. Traiger
IBM Research Lab, San Jose, CA

One-line Summary

Overview/Main Points

• Concurrency Control
  • Example: x has $100, y has $200

    T1	T2
    R(x)
    x ← x - 20
    R(y)
    y ← y + 20
    W(x)
    	R(x): $80
    	R(y): $200
    	Total ← x + y: $280
    	End
    W(y)
    End

    After T₁, X has $80, and y has $220.
  • Transaction
    • Take db from one consistent state to another (if run by itself)
    • Then, any sequence of transaction running serially take the DBMS from one consistent state to another.
    • ''seralizable'' means ''look like'' run serially.
    • view transactions as series of reads & writes.
  • A sequence of xacts
    • T₁: R(x)
    • T₂: W(x)
    • T₃: R(z)
    • ...
  • What ''forces'' T₁ to precede T₂? (a list of operations changing the order is consistent)
    • R-W conflict
    • W-R conflict
    • W-W conflict
  • Serialization Graph
    • One node for each xact
    • Edge from T_i to T_j if R-W / W-R / W-W conflicts from T_i to T_j

    T1	T2
    R(x)
    R(y)
    W(x)
    	R(x)
    	R(y)
    W(y)

    
  • Theorem
    • acyclic serialization graph ⇒ serializable
    • a cyclic serialization graph ⇔ conflict serializable
  • 2 kinds of lock
    • S-lock (read lock): must get an s-lock on an object before reading it
    • X-lock (write lock): must get an x-lock on an object before writing it
  • 2-phase locking: as soon as the xact releases a lock, it never can acquire another.
  
  • Theorem: 2-phase locking ⇒ serializable
    • T₁: sum balances of all records
      • Option 1: lock all the records individually
      • Option 2: lock the whole file
    • T₂: update the balances on record, 517,333.
    
  • Granularity (size) options for locking: it is about efficiency (concurrency vs overhead), not consistency
    • DB
    • Files: a xact that scans all records to compute sum of account balance
    • Pages
    • Records: a xact that updates the record 509,317 to increase balance
  • Notification in a hierarchy manner
    • Is there any locks on record in the DB?
    • Is there any locks on record in the files?
    • Is there any locks on record in the pages?
    • Is there any locks on record in the records?
  • lock types
    • S-lock: implicit set S locks on all descendants of the requested node
    • X-lock: implicit set X locks on all descendants of the requested node
    • ''intention'' locks
      • IS (Intention Share): may convert to S later; allow its descendant nodes to lock in S or IS, but no implicit locking for them
      • IX (Intention eXclusive): allow its descendant nodes to lock in any locks (IS, IX, S, SIX, X), but no implicit locking for them
      • SIX (S + IX): implicit set S locks on all descendants of the requested node, and allows its descendants to lock in IX, SIX, or X
  • A xact that reads an entire subtree and updates particular nodes of that subtree
    • Option 1: X at the root of the subtree (low concurrency)
    • Option 2: IX at the root of the subtree with individual I, S, and X at the lower nodes (high locking overhead)
    • New lock: SIX (allow read without further locking, and write in the subtree by setting X on the updating nodes and IX or SIX on the intervening nodes)
  • Xact synchronization
    • compatible in terms of two lock requests for the same node by two different xacts
    • NL: No Lock/Null mode
    • X, S, I are incompatible with one anothter but distinct S requests may be granted together and distint I requests may be granted together.

    Compatible	NL	IS	IX	S	SIX	X
    NL	Y	Y	Y	Y	Y	Y
    IS	Y	Y	Y	Y	Y
    IX	Y	Y	Y
    S	Y	Y		Y
    SIX	Y	Y
    X	Y

  • Is there anything between chaos of serializability? Yes, optimistic CC.
  • Lock manager: maintain a hash table of locks in main memory
  
  • Lock Conversions: T₁ decides to want an X lock on P instead of S
  
  • Deadlock Detection
    • build ''waits-for'' graph in the lock manager
    • search for cycles
    • incrementally whenever a xact waits, update the graph & check for cycles
    • break the cycle by picking a ''victim'' to kill
      • current blocker
      • youngest xact in cycle
      • fewest locks held
      • random
      • minimum work: the xact that has consumed the least amount of resources (e.g., CPU time) so far
      • most cycles: the xact that breaks the largest number of cycles simultaneously
      • most edges: the xact that eliminates as many edges as possible
  • Locks use a lot in serializability
  • Lock acquisition
    
    • Before getting an S or IS lock on an object, (explicit or implicit) get S or IS on at least one parent
    • Before getting an IX, SIX, or X lock on an object, (explicit or implicit) get X or IX on all parents
    • Get locks top-down, release them bottom-up
  • Latch VS lock

    Latch	Lock
    for consistency: no concurrent updates; mutual exclusion	for serializability
    very lightweight (low-level locks)	"heavier"
    protest systematics of the system	protest logic of the system

    
    • Mutual exclusion cannot guarrantee serializability
    • Every xact can have at most one latch
  • DBs
    • Relational DB: IBM's System R & DB2
      • Structural English QUEry Language (SEQUEL)
      • Structural Query Language (SQL)
    • UC-Berkeley's Ingres & postgresql
      • Quel

Relevance

Flaws