Understanding Data Consistency

In order to understand how DB2 9 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. The best way to define data consistency is by example.

Suppose your company owns a chain of hardware stores and uses a database to keep track of inventory at each store. By design, this database contains an inventory table for each hardware store in the chain; whenever supplies are received or sold by a particular store, its corresponding inventory table is updated. Now, suppose a case of hammers is physically transferred from one hardware store to another. The hammer count value stored in the receiving hardware store's table needs to be raised, and the hammer count value in the donating store's table needs to be lowered, to reflect this inventory move. If a user raises the hammer count value in the receiving hardware store's inventory table but fails to lower the hammer count value in the donating store's inventory table, the data will be inconsistent. The total hammer inventory for the entire chain 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 (the hammer count is lowered in donating store's table, then a system crash occurs before the 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 some really are, because the query read another user's changes before all tables affected by those changes had been properly updated. (Reacting to this misinformation, 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 relies on two mechanisms: isolation levels and locks.