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

TIME T1 -- Chicago Order: T2 -- Boston Order: INVENTORY

DB 70 copies DB 60 copies

t1 BEGIN T1 200

t2 READ inventory BEGIN T2 200

t3 inventory = inventory - 70 READ inventory 200

t4 WRITE inventory inventory = inventory - 60 130

t5 COMMIT WRITE inventory 140

t6 END COMMIT 140

t7 END 140

Figure 15-9 Concurrent transactions: lost update.

the same database item conflict with each other if at least one of them is a write
operation.
When two transactions execute concurrently, the operations of one transaction
interleave with the operations of the other transaction. So it becomes obvious that,
to avoid integrity problems, all types of interleaving of operations may not be
allowed.
Three potential problems arise when operations of concurrent transactions are
interleaved: the lost update problem, the problem resulting from dependency on
uncommitted update, and the inconsistent summary problem. We will discuss these
problems individually. If concurrency has to be allowed in transaction processing,
then these problems must be resolved. Before considering solution options, try to
get a clear understanding of the nature of the three types of problems.

Lost Update In this scenario, an apparently successful database update by one
transaction can be overridden by another transaction. Figure 15-9 repeats the
second case presented in the Figure 15-8. Here, both of transactions T1 and T2 read
and write the inventory record for database textbook.
Note the apparent successful update by transaction T1 writing out the inventory
balance as 130. Transaction T2, however, overwrites the inventory balance as 140,
which is incorrect.

Dependency on Uncommitted Update Just as in the previous case, the opera-
tions of transactions T1 and T2 interleave. Transaction T2 starts slightly later after
both read and write operations of T1 are completed but before the update is com-
mitted to the database. However, transaction T1 aborts for whatever reason and
rolls back. Figure 15-10 presents this case.
Why does this case present an integrity problem? Transaction T2 is allowed to
see the intermediate result of transaction T1 before the update is committed. So
transaction T2 starts out with the uncommitted value of 130 and comes up with the
final result of 70. That would be correct if transaction T1 had not aborted and rolled
back its update.

Inconsistent Summary In this case, each transaction does not both read and
write. One transaction reads several records while the second transaction, inter-
leaved with the other transaction, updates some of the records being read by the
first transaction. Figure 15-11 illustrates this situation.