March 6, 2006

---

More on Being `Friendly' :-)

---

• being friendly did buy us a little more concurrency
• not so fast...friendliness leads to another form of deadlock (beyond that created out of 2PL)
• example (courtesy [DBCB] example 18.16, pp. 944-945):

  ---

  T1	T2
  sl_1(A)
  	sl_2(A)
  xl_1(A) DENIED!
  	xl_2(A) DENIED!

• solution: introduce an update lock

---

Update Locks

---

• denoted by `U'
• request: ul_i(X)
• grants only read-only access (does not grant permission to write)
• can be upgraded later to an exclusive lock
• compatibility matrix for shared, exclusive, and update locks

  	Lock requested
  Lock held	S X U S yes no yes X no no no U no no no

  • U looks like S when being requested
  • U looks like X once granted
  • in other words, you can get a U lock on an element X even if other transactions have S's on X; however, once a U lock is granted on X, no further locks (of any sort) on X may be granted
  • notice now A!=A^T, i.e., A does not equal A transpose
• deadlock example revisited (courtesy [DBCB] example 18.17, p. 945-946):

  ---

  T1	T2
  ul_1(A)
  r_1(A)
  	ul_2(A) DENIED!
  xl_1(A)
  w_1(A)
  u_1(A)
  	ul_2(A)
  	r_2(A)
  	xl_2(A)
  	w_2(A)
  	u_2(A)

  ---

  • no more deadlock...whew! finally
  • however, we are back where we started: a serial schedule

---

Moral of the Story

---

• overall goal: a legal, serializable schedule of concurrently executing consistent transactions which preserves consistent database state without the possibility of deadlock
  • wow...that's a lot to ask for!
• often concurrency and deadlock-prevention are conflicting goals
  • and scheduler errs on the side of caution and choose to prevent deadlock

• 	concurrent?	deadlock possible?
  friendly schedule, no U lock	yes	yes
  schedule employing U lock	not really	no

---

Review of ACID Properties

---

• Atomic - the transaction happens or not; cannot execute 1/2 a transaction
• Consistent - there are no surprises in the world, e.g., `my balance should not go <$0.00!'
• Isolation - effects in parallel = effects in sequence
  • all about `concurrency control'
• Durability - actions are lasting, e.g., `if system crashes after my deposit, my money is still there'

---

Increment Lock

---

• increments and decrements are common actions
  • consider bank accounts or flight reservations
• increments and decrements commute with each other
  • their order is irrelevant to the final value
• new action: INC(A,c) = READ(A,t) t:=t+c WRITE(A,t)
  • denoted by inc_i(X)
• INC(A,c) is atomic
  • software and hardware support for atomicity
• introduce a new type of lock: increment lock
  • denoted by `I'
  • request: il_i(X)
  • conflicts with r_i's and w_i's, but not with inc_i
• again, we need to update our requirements
  • consistency of transactions: need increment lock to perform inc(X) action
  • legality of schedules: any #transactions can have I at once, but once granted on element X, no other transaction can be granted an S or X lock
• final compatibility matrix (for all 4 types of locks):

  	Lock requested
  Lock held	S X U I S yes no yes no X no no no no U no no no no I no no no yes

• example (courtesy [DBCB] example 18.18, p. 948-949):

  ---

  T1	T2
  sl_1(A)
  r_1(A)
  	sl_2(A)
  	r_2(A)
  	il_2(B)
  	inc_2(B)
  il_1(B)
  inc_1(B)
  	u_2(A)
  	u_2(B)
  u_1(A)
  u_1(B)

  ---

  • scheduler will introduce no delays
  • serializable?
  • S: r_1(A) r_2(A) inc_2(B) inc_1(B)
    • conflict-equivalent to both T1T2 and T2T1

---

References

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