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

Consistency. The consistency requirement in this case is that the combined total of
savings and checking balances must be $450,000. From Figure 15-1, you will note
that the combined total is the same at the start as well as at the end of the trans-
action. Figure 15-2 similarly shows the combined total to be same at the start and
end of the transaction. Whether the transaction reaches a successful end or is
aborted, consistency of the database is maintained.

Isolation. To understand the property of isolation, consider another transaction, T2,
that simply makes a deposit of $30,000 to the savings account. Now assume that T1
and T2 execute concurrently and that the database operations of T1 and T2 are
interleaved. If T1 executes first, the balance in the savings account is $390,000. If T2
follows, at the end of T2 the balance in the savings account is $420,000. Now reverse
the order. If T2 executes first, the savings balance is $470,000. If T1 follows, at the
end of T1 the savings balance is $420,000. The property of isolation requires that
even if the database operations of T1 and T2 are interleaved, the final savings
balance is still $420,000 as if the two transactions executed independently without
any interference between them.

Durability. Look again to Figures 15-1 or 15-2. At the successful end of the trans-
action shown in Figure 15-1, the checking balance is $60,000 and the savings balance
is $390,000. Figure 15-2 shows these amounts as $10,000 and $440,000 respectively.
If transaction T1 executed successfully, the values of $60,000 and $390,000 must
persist in the database. Even after a hardware failure or a system failure, if there
are no intervening transactions affecting these balances, these values must persist.

Transaction States

As a transaction proceeds through its various operations, it passes through several
states. Each state marks a transition point where some specific action takes place
or which signals a specific stage in the processing. You know that the database
system is a transaction-processing environment. To understand and appreciate the
types of data integrity problems and possible solution options, you need a good
understanding of transaction processing. This means that you need to trace through
the different states of a transaction, clearly comprehend the significance of each
state and also what happens between two consecutive states, and understand the
branch points. The recovery manager of the DBMS keeps track of the operations
of each transaction.
Figure 15-3 illustrates the various operations and the state of the transaction at
the end of each operation.
Here is a list of operations and states as shown in the figure:

BEGIN Marks the beginning of the transaction.

Transaction enters active state.

READ Transaction performs read operation.

Transaction remains in active state.

WRITE Transaction performs write (insertion or update or deletion)

operation.
Transaction remains in active state.