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

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

DB 70 copies DB 60 copies

t1 BEGIN T1 200

t2 READ inventory 200

t3 inventory = inventory - 70 200

t4 WRITE inventory BEGIN T2 130

t5 READ inventory 130

t6 ROLLBACK inventory = inventory - 60 70

t7 END WRITE inventory 70

t8 COMMIT 70

t9 END 200

Figure 15-10 Concurrent transactions: uncommitted dependency.

TIME T1 -- Count books on DB, DW, VB T2 -- update inventory for DB, DW DB DW VB SUM

t1 BEGIN T1 200 250 300

=

t2 sum 0 BEGIN T2 200 250 300 0

t3 READ DB inventory READ DB inventory 200 250 300 0

= sum + DB inventory = DB inventory - 70

t4 sum DB inventory 200 250 300 200

t5 READ DW inventory WRITE DB inventory 130 250 300 200

= sum + DW inventory

t6 sum READ VB inventory 130 250 300 450

t7 VB inventory

=

VB inventory

-

60 130 250 300 450

t8 WRITE VB inventory 130 250 240 450

t9 READ VB inventory COMMIT 130 250 240 450

= sum + VB inventory

t10 sum END 130 250 240 690

t11 COMMIT 130 250 240 690

t12 END 130 250 240 690

Figure 15-11 Concurrent transactions: inconsistent summary.

Transaction T1 simply reads the inventory records of the topics on the database,
data warehousing, and Visual Basic to find the total number of these topics in the
warehouse. In the meantime, T2 reads and updates two of these records—one after
it is read by T1 and another before it is read by T1. Note the error in the summary
reads and the final sum arrived at the end of transaction T1.

Transactions and Schedules

The foregoing discussions clearly establish the need for concurrency control tech-
niques that can preserve data integrity even while allowing interleaving of opera-
tions belonging to different transactions. But before you can appreciate the
concurrency control techniques, you need to study the operations of concurrent
transactions as they impact the database.
From the point of view of the database, a transaction is simply a list of opera-
tions such as BEGIN, READ, WRITE, END, COMMIT, and ABORT. In concur-
rent executions of different transactions, these operations are interleaved. The
READ of one transaction may be followed by the READ of another transaction.
After that, you may have the WRITE of the first transaction, and so on. These oper-
ations interact with the database. If you observe the operations over a certain time
interval, you note a series of operations belonging to various concurrent transac-