March 1, 2006

Coverage: [DBCB] Chapter 18, pp. 940-949

Types of Locks

main idea: we want locks, as opposed to some algorithm, to determine if deadlock is possible
read lock (shared lock)
- denoted by `S'
- permits concurrent reads
- request: sl_i(X)
write lock (exclusive lock)
- denoted by `X'
- request: xl_i(X)
u_i(X) releases all locks held on X
we need to update our requirements
- consistency of transactions: need lock appropriate for desired action
- legality of schedules: an element may be locked exclusively by one transaction or by several in read-only mode, but not both

now a simple relation, such as LockTable, does not suffice to manage locks
- use a compatibility matrix
  - a compatibility matrix specifies a policy
  - enforces the legality of schedules
  - does not dictate the order of actions
compatibility matrix for shared and exclusive locks

Lock requested

Lock held

S X

S yes no

X no no
- notice A=A^T, i.e., A equals A transpose

courtesy [DBCB] example 18.13, p. 942:

T1 T2

sl_1(A)

r_1(A)

sl_2(A)

r_2(A)

sl_2(B)

r_2(B)

xl_1(B) DENIED!

u_2(A)

u_2(B)

xl_1(B) OKAY NOW

r_1(B)

w_1(B)

u_1(A)

u_1(B)
this schedule is conflict-serializable
- it is conflict-equivalent to the T2T1 serial schedule
  - because we can push the sl_1(A) r_1(A) actions below T2
- but it is not conflict-equivalent to the T1T2 serial schedule
  - because the w_1(B) action is blocked from shifting ahead of any action in T2 by the r_2(B) action
interesting observations
- this schedule is more or less serial; it achieves limited concurrency
- T1 is greedy; it takes an X lock from the get go, when an S lock will suffice

where `friendly' means only requesting an X lock immediately before you will really need it, i.e., only when you are just about to write
example (courtesy [DBCB] example 18.15, p. 944):

T1 T2

sl_1(A)

r_1(A)

sl_2(A)

r_2(A)

sl_2(B)

r_2(B)

sl_1(B)

r_1(B)

xl_1(B) DENIED!

u_2(A)

u_2(B)

xl_1(B) OKAY NOW

w_1(B)

u_1(A)

u_1(B)

Database Systems: The Complete Book