Advanced Database System

1
Advanced Database System

• CS 641
• Lecture 17

2
Enforcing serializability

• If transactions performing their tasks in an
  unconstrained manner, the schedule generated is
  unlikely serializable.
• The scheduler uses locks to enforce
  serializability.
• A transaction obtains locks on the database
  elements it accesses to prevent other
  transactions from accessing these elements at
  roughly the same time and thereby incurring the
  risk of unserializability.

3
A schedule use a lock table
4
Use locks

• When a scheduler use locks, transactions must
  request and release locks, in addition to reading
  and writing database elements.
• Consistency of transactions
• Actions and locks must relate in the expected
  ways
• A transaction can only read/write an element if
  it holds the lock
• If a transaction locks an element, it must unlock
  later
• Legality of schedules
• no two transactions can hold the locks of the
  same
• element at the same time.

5
Notation

• li(X) Ti requests a lock on X
• ui(X) Ti releases its lock on X
• Then,
• Consistency condition
• Ti has an action ri(X) or wi(X), it must has a
  previous action li(X) with no intervening action
  ui(X) and there is a subsequent ui(X)
• Legality of schedules
• if there are actions li(X) followed by lj(X) in
  a schedule, then somewhere between these actions
  there are must be an action ui(X)

6
Example 18.10

• Two transaction T1 and T2
• T1 l1(A) r1(A) AA100 w1(A) u1(A) l1(B)
  r1(B) BB100 w1(B) u1(B)
• T2 l2(A) r2(A) AA2 w2(A) u2(A) l2(B)
  r2(B) BB2 w2(B) u2(B)
• Both transactions are consistent, but the
  schedule may not serializable

7
A legal schedule of consistent transactions, but
not serializable
8
Lock table

• Lock table tells for every database element, the
  transaction, if any, that currently holds a lock
  on that element.
• The table is considered as a relation
  Locks(element, transaction), consisting of pairs
  (X,T)

9
Example 18.11

• Changed T1 and T2
• T1 l1(A) r1(A) AA100 w1(A) l1(B) u1(A)
  r1(B) BB100 w1(B) u1(B)
• T2 l2(A) r2(A) AA2 w2(A) l2(B) u2(A)
  r2(B) BB2 w2(B) u2(B)

10
The locking scheduler delays requests
11
Two-Phase locking

• The condition to guarantee that a legal schedule
  of consistent transactions is conflict-serializabl
  e is two-phase locking (2PL)
• In every transaction, all lock requests precede
  all unlocked requests.
• Example 18.12
• The schedule in example 18.10 does not obey 2PL
  condition
• But in example 18.11, it obeys.

12
Why two-phase locking works

• The conflict-equivalent serial schedule for a
  schedule S of 2PL transactions is the one in
  which transactions are ordered in the same order
  as their first unlocks.

13
Exercises

• 18.3.1
• 18.3.3 a) b)
• 18.3.4

14
A risk of deadlock

• T1 l1(A) r1(A) AA100 w1(A) l1(B) u1(A)
  r1(B) BB100 w1(B) u1(B)
• T2 l2(B) r2(B) BB2 w2(B) l2(A) u2(B)
  r2(A) AA2 w2(A) u2(A)

15
Lock modes

• The problem in two-phase locking
• Transactions have to lock an element if it only
  reads it and not writes it.
• To solve this problem, different locks are
  introduced
• Shared locks (read locks) for reading
• Exclusive locks (write locks) for writing
• For any database element X, there can be either
  one exclusive lock, or no exclusive locks but any
  number of shared locks.

16
Notation

• sli(X) transaction Ti requests a shared lock on
  database element X
• xli(X) transaction Ti requests an exclusive lock
  on database element X
• ui(X) Ti unlocks X i.e., it relinquishes
  whatever lock(s) it has on X.

17
Requirements

• Consistency of transactions in any transaction
  Ti,
• A read action ri(X) must be preceded by sli(X) or
  xli(X), with no intervening ui(X)
• A write action wi(X) must be preceded by xli(X),
  with no intervening ui(X)
• All locks must be followed by an unlock of the
  same element

18
Requirements (Cont.)

• Two-phase locking of transactions
• Locking must precede unlocking
• Legality of schedules
• If xli(X) appears in a schedule, then there
  cannot be a following xlj(X) or slj(X), without
  an intervening ui(X)
• If sli(X) appears in a schedule, then there
  cannot be a following xlj(X), j?i, without an
  intervening ui(X)

19
Example 18.13

• T1 sl1(A) r1(A) xl1(B) r1(B) w1(B) u1(A)
  u1(B)
• T2 sl2(A) r2(A) sl2(B) r2(B) u2(A) u2(B)

20
Example 18.13 (Cont.)

• The resulting schedule is conflict-serializable
• The conflict-equivalent serial order is
• (T2, T1)

21
Compatibility matrices

• A compatibility matrix has a row and column for
  each lock mode.
• The rows correspond to a lock that is already
  held on an element X by another transaction
• The columns correspond to the mode of a lock on X
  that is requested.
• The rule for using a matrix for lock-granting
  decision is
• We can grant the lock in mode C if and only if
  for every row R such that there is already a lock
  on X in mode R by some other transaction, there
  is a Yes in column C.

22
Example 18.14
23
Upgrading locks

• A transaction T takes a shared lock on X, thus
  other transactions can also read the content of
  X later when T is ready to write the new value
  on X, upgrade the lock to exclusive.

24
Example 18.15

• T1 sl1(A) r1(A) sl1(B) r1(B) xl1(B) w1(B)
  u1(A) u1(B)
• T2 sl2(A) r2(A) sl2(B) r2(B) u2(A) u2(B)

25
Example 18.16 (Deadlock problem)
26
Update lock

• An update lock uli(X)
• gives the transaction Ti only the privilege to
  read X, not to write X.
• Only the update lock can be upgraded to a write
  lock later.
• Once there is an update lock on X, we prevent
  additional locks of any kind.

27
Modified compatibility matrix
28
Solving deadlock problem

• T1 ul1(A) r1(A) xl1(A) w1(A) u1(A)
• T2 ul2(A) r2(A) xl2(A) w2(A) u2(A)

29
Problem

• Many transactions operate on the database only by
  incrementing or decrementing stored values.
• The property of increment actions is that they
  commute with each other, since if two
  transactions add constants to the same database
  element, it does not matter which goes first.

30
Two increment actions commute
31
Notation

• INC(A,c) the increment action
• This action adds constant c to database element A
• It can be used to represent (atomic)
• READ(A,t) ttc WRITE(A,t)
• Increment lock
• ili(X)
• inci(X) action in which transaction Ti
  increments X by some constant

32
Requirements

• A consistent transaction can only have an
  increment action on X if it holds an increment
  lock on X at the time
• In a legal schedule, any number of transactions
  can hold an increment lock on X at any time.
• However, if an increment lock on X is held by
  some transaction, then no other transaction can
  hold either a shared or exclusive lock on X at
  the same time.
• The action inci(X) conflicts with both rj(X) and
  wj(X), for j?i, but does not conflict with incj(X)

33
Modified compatibility matrix
34
Example 18.18

• T1 sl1(A) r1(A) il1(B) inc1(B) u1(A) u1(B)
  
• T2 sl2(A) r2(A) il2(B) inc2(B) u2(A) u2(B)

35
Exercises

• 18.4.1 a) d)
• 18.4.2
• 18.4.5