March 8, 2006

---

Anatomy of a Locking Scheduler

---

---

Granularity of Locks

---

• choices:
  • lock whole relation
  • lock pages
  • lock tuples
  • lock all three (or any subset)
• decision is application specific
• sometimes benefits of locking at a fine level of granularity is not worth the effort
• locking whole relations is easy, but not very useful
  • concurrency?

---

The Warning Protocol

---

• idea: place warnings (with warning locks) along the path to the data element so other transactions can see them
  • prefixed with `I' (for `intention to')
  • also called `intention locks'
• useful with a hierarchical structure of database elements
  
  (courtesy [DBCB] Fig. 18.27, p. 959)
  
  
• compatibility matrix for shared, exclusive, and intention locks:

  	Lock requested
  Lock held	IS IX S X IS yes yes yes no IX yes yes no no S yes no yes no X no no no no

• example (courtesy [DBCB] example 18.21, pp. 960-961):
  • consider the following two queries, which we will call T1 and T2, respectively:

    SELECT *
    FROM Movies
    WHERE title = 'King Kong';
    
    UPDATE Movie
    SET year = 1939
    WHERE title = 'Gone With the Wind';
    

  • hierarchy consists of only the entire Movies relation (the root) and individual tuples (the leaves)
  • how are the locks requested and granted?
    
    (courtesy [DBCB] Fig. 18.29, p. 962)
    
    
  • what if T2 had requested and received an X lock on Movies instead of an IX lock on it?
    
    

---

Idea of Lock Domination

---

• way to determine which lock is stricter than another if they are both granted on a common data element
• we will say lock X dominates lock Y if and only if lock X has a `no' in each row and column of the compatibility matrix in which lock Y has a `no'
• dominant lock desirable so that we can summarize the effects of many locks with a group mode
• U then dominates S, and
• X dominates both S and U
• what about IS and IX?
• what about IS and S?
• what about IX and S?
  • these are a strange couple, why?
• note about compatibility matrix: only applies to two different transactions
• compatibility matrix augmented with SIX lock:

  	Lock requested
  Lock held	IS IX S X SIX IS yes yes yes no yes IX yes yes no no no S yes no yes no no X no no no no no SIX yes no no no no

• if you want	you must already have on all ancestors
  IS, S	IS or IX
  IX, X, SIX	IX or SIX

---

Phantom Menace Problem

---

• locking only works for tuples that actually exist
• be careful when inserting new tuples
  • insertion can be viewed as a write operation
• phantom tuple - one that should have been locked, but was not, because it did not exist at the time the lock was granted
• transaction which inserts (or deletes) tuple must obtain an X lock

---

References

[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems: The Complete Book. Prentice Hall, 2002.