February 20, 2006
Coverage: [DBCB] Chapter 18, pp. 932-938
Review of Conflict-Serializable Schedule Undetectable by Swapping
Locking/Unlocking
- idea is that scheduler uses locks to prevent unserializable behavior
- request and release locks
- notation: l_i(X), u_i(X)
- schematic:
(courtesy [DBCB] Fig. 18.11, p. 933)
Two Requirements for the Use of Locks
- consistency of transactions
- says
- you can only read or write a data element if you have its lock
- at some point you must unlock any data element you
previously locked
- applies to the structure of transactions
- not be confused with a consistent database state
- legality of schedules
- says a data element cannot be locked more than once;
enforces intended semantics of a lock
- applies to the structure of schedules
- legal != serializable
- example (courtesy [DBCB] Fig. 18.12, p. 935):
consistency: A = B
| T1 | T2 | A | B |
| | 25 | 25 |
| l_1(A) | | | |
| r_1(A) | | | |
| A+=100 | | | |
| w_1(A) | | 125 | |
| u_1(A) | | | |
| l_2(A) | | |
| r_2(A) | | |
| A*=2 | | |
| w_2(A) | 250 | |
| u_2(A) | | |
| l_2(B) | | |
| r_2(B) | | |
| B*=2 | | |
| w_2(B) | | 50 |
| l_2(B) | | |
| l_1(B) | | | |
| r_1(B) | | | |
| B+=100 | | | |
| w_1(B) | | | 150 |
| u_1(B) | | | |
- a legal schedule containing consistent transactions,
- scheduler would grant locks on every request
in arrival-time order
- but it is not serializable
- such schedules must be avoided
- end-goal: a `legal schedule' of `consistent transactions' that is
conflict-serializable
Managing Locks
- use a lock table
- Locks(element, transaction)
- tuples of form (X, T)
- indicates that transaction T has a lock on database element
X
- scheduler issues simple INSERT and DELETE
queries to this relation
- assume only one type of lock
Sometimes Lock Requests Cannot Be Granted
- example (courtesy [DBCB] Fig. 18.13, p. 936):
consistency: A = B
| T1 | T2 | A | B |
| | 25 | 25 |
| l_1(A) | | | |
| r_1(A) | | | |
| A+=100 | | | |
| w_1(A) | | 125 | |
| l_1(B) | | | |
| u_1(A) | | | |
| l_2(A) | | |
| r_2(A) | | |
| A*=2 | | |
| w_2(A) | 250 | |
| l_2(B) DENIED! | | |
| r_1(B) | | | |
| B+=100 | | | |
| w_1(B) | | | 125 |
| u_1(B) | | | |
| l_2(B) OKAY NOW | | |
| u_2(A) | | |
| r_2(B) | | |
| B*=2 | | |
| w_2(B) | | 250 |
| u_2(B) | | |
- minor modification, enormous effect
- now the schedule is serializable
Two-Phase Locking
- a condition which guarantees that a legal schedule
of consistent transactions is conflict-serializable
- says all lock requests should precede all unlock
requests in every transaction
- greedy approach to locking
- the two phases
- obtain locks
- release locks
- schematic:
(courtesy [DBCB] Fig. 18.14, p. 937)
- a schedule that satisfies this condition is
called a two-phase-locked transaction, or,
simply, a 2PL transaction
- first schedule above is not 2PL
- second schedule above is 2PL
- `why didn't I think of that?'
- 2PL is a contribution to the DB world by Jim Gray
- simple, but beautiful and very effective idea
- followed by commercial locking systems
Why Does Two-Phase Locking Work?
References
[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems:
The Complete Book. Prentice Hall, 2002.
