DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 474

Transaction: Enter payment of $500 received from a customer.

CUSTOMER
ACCOUNT
RECORD

COMPANY
CASH
RECORD

DB in prior
consistent state

Initial Balances $1000 $100000

Update Customer Account - 500

Update Company Cash +500

DB in new
consistent state

Balances at end of transaction

$500 $100500

Failur e afte r Cus tomer Acco unt Up date

DB in
inconsistent
state

Balances after failure $500 $100000

Figure 15-26 Transaction in progress at time of failure.

consistent state. Again, remember that in this example we have considered only one
transaction in progress. In practice, many transactions are normally in progress at
the time of a failure. The effects of partial database updates by all of the in-flight
transactions must be addressed.
The underlying principle for recovery is redundancy. Keep enough redundant
data to enable recovery. This includes keeping backup copies of the database and a
file of copies of the updates since the last backup. A transaction is the basic unit of
recovery in case of failures. The recovery system must be able to recover the effects
of transactions in progress—for all of them, one by one. The recovery manager of
the DBMS must be able to guarantee the properties of atomicity and durability.
To summarize, a DBMS must provide the following facilities for recovery from
failures:

• A backup mechanism to create periodic backup copies of the entire database
• Journalizing or logging facilities to maintain an audit trail of transactions and
database changes
• A checkpoint facility for the DBMS to suspend all processing, from time to
time, and synchronize its files and journals
• A recovery manager module to bring the database to a consistent state and
resume transaction processing

Logging

Please go back to the example shown in Figure 15-26. You note that the failure inter-
rupts the transaction and stops it before it can complete successfully. You quickly
realize that you could easily bring the database to a consistent state if only you had
information about the updates intended by the transaction. Using the information