DB2 Universal Database V8.1 Certification Exam 700 Study Guide

In order to understand how DB2 Universal Database attempts to maintain data consistency in both single- and multi- user environments, you must first understand what data consistency is, as well as be able to identify what can cause a database to be placed in an inconsistent state. One of the best ways to learn both is by studying the following example:

Suppose your company owns a chain of hardware stores and a database is used to keep track of the inventory stored at each store. By design, this data base contains an inventory table for each hardware store in the chain. Whenever supplies are received or sold at a particular store, the inventory table that corresponds to that store is updated accordingly . Now, suppose a case of hammers is physically moved from one hardware store to another. In order to reflect this inventory move, the hammer count value stored in the donating store's inventory table needs to be lowered , and the hammer count value stored in the receiving store's inventory table needs to be raised. If a user lowers the hammer count value in the donating store's inventory table, but fails to raise the hammer count value in the receiving store's inventory table, the data will become inconsistent. Now, the hammer inventory count for the entire chain of hardware stores is no longer accurate.

A database can become inconsistent if a user forgets to make all necessary changes (as in the previous example), if the system crashes while a user is in the middle of making changes (hammer count lowered in donating store's table, then system crash occurs before hammer count is raised in receiving store's table), or if, for some reason, a database application stops execution prematurely. Inconsistency can also occur when several users attempt to access the same data at the same time. For example, using the same hardware store scenario, one user might query the database and discover that no more hammers are available (when there really are) because the query read another user's changes before all tables affected by those changes had been properly updated. Reacting to this information, the user might then place an order for more hammers when none are needed.

To ensure that users and applications accessing the same data at the same time do not inadvertently place that data in an inconsistent state, DB2 UDB relies on two mechanisms, known as isolation levels and locks.