Coverage:
[DBCB] Chapter 18, pp. 957-962 and [DBMS] Chapter 18, pp. 519-523
Warm-up
compatibility matrix from last class:
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 Revisited
example (courtesy [DBCB] example 18.22, pp. 961-962):
consider the following query which we will call T3:
SELECT SUM(length)
FROM Movies
WHERE studioName = 'Disney';
consider another transaction T4 which inserts
a new Disney movie
T3T4 is obviously serializable
however if T3 and T4 both
write another data element with T4
writing it first, there is scope for unserializable
behavior, e.g.,
r_3(D_1) r_3(D_2) w_4(D_3) w_4(X) w_3(L) w3(X)
transaction which inserts (or deletes) tuple must obtain an X
lock
Introduction to Database Recovery
we only get I (of ACID) from locks
why recovery? to preserve the A and D (of ACID)
we get C from constraints, triggers, and active DB elements
transactions are performed in a tentative manner
end of each transaction contains a
COMMIT or ABORT/ROLLBACK action
UNDO operation
undoing the actions of a transaction which did not commit
UNDO when something bad happens (e.g., a negative GPA) or
after a deadlock
ensures A
REDO operation
helps make sure that actions of a committed transactions
survive system crashes
when power goes off, we can REDO
ensures D
recovery manager
one of the most difficult parts of a DBMS to design and implement
UNDO
we have been assuming that WRITE is an atomic operation
this is an unrealistic assumption
undoing is the process of removing effects of an incomplete/aborted
transaction for preserving A
two approaches
STEAL: allows updates made from uncommitted transactions
to overwrite the most-recently committed value of a data element
on non-volatile storage
concept of a dirty page
NO-STEAL: only write pages at the end of the transaction (opposite of
STEAL)
REDO
redoing is the process of reinstating effects of
an uncommitted transaction for preserving D
two approaches
FORCE: write effects of updates to non-volatile storage
immediately when the transaction commits
NO-FORCE: opposite of FORCE
Simplest Policy: NO-STEAL, FORCE
advantages
NO-STEAL: no need to undo changes of an aborted transaction
FORCE: no need to redo the changes of a committed transaction
if there is a crash
(critical) disadvantages
NO-STEAL: modified pages by concurrently executing
transactions may not all fit into the buffer pool
FORCE: results in excessive disk I/O's
20 Txs => 20 I/O's vs. 20 Txs => 1 I/O
Most Commercial Systems Use a STEAL, NO-FORCE Policy
STEAL: if a dirty page is chosen for replacement, it
is written to secondary storage even if the modifying
transaction has yet to commit
NO-FORCE: pages in the buffer pool that are
modified by a transaction are not written (i.e., not forced) to disk when the
transaction commits
the ARIES recovery algorithm is designed to work with a
STEAL, NO-FORCE policy
uses WAL (Write-Ahead Logging): a policy for enforcing STEAL, NO-FORCE
Achieving the `C' in ACID
active and rule-based elements (constraints, triggers)
help encode application-specific constraints
allow DBMS to be reactive
E-C-A paradigm (Event-Condition-Action)
e.g., ON INSERT IF <cond> { <action> }
works for most things; some still need a programming language shell
like a production system from AI
Limitations of ACID
what about security?
usually an afterthought; added only at end
too general for transactions with expressive semantics
we have only discussed transactions with READs, WRITEs, INCs, ....
mobile computing, collaborative computing, etc?
References
[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems:
The Complete Book. Prentice Hall, 2002.
[DBMS] R. Ramakrishnan and J. Gehrke: Database Management Systems.
McGraw-Hill, Third Edition, 2002.
