Databases Reference
In-Depth Information
Figure 10-4
Transaction
T1
Check on hand for item 120
Check credit for customer 1001
Reduce on hand for 120 by 350
Generate order
Commit transaction
Warehouse Database
Transaction
T2
Check on hand for item 120
Check credit for customer 1040
Generate order
Reduce on hand for 120 by 500
Commit transaction
TextBookInventory
100 Chemistry
120 Physics
310 Intro. To Literature
Customer
1001 Northgate
1040 Westview
1910 Eastland
Sample transactions.
10.2.1 The Need for Concurrency Management
The easiest way to see the need for concurrency management is with an exam-
ple. Take a look at Figure 10-4.
Textbook 120 (Physics) has a quantity on hand of 1100. Transaction T1 starts
processing first, then T2. T1 reads the quantity on hand from TextBookInven-
tory, goes through the processing steps listed in the figure, sets the remaining
quantity at 750, and commits the transaction. Now, consider what happens if
T2 reads the quantity on hand after T1, but before T1 reduces the amount by
350 books. T2 does processing based on a quantity of 1100. When it makes its
update to TextBookInventory, it overwrites the value assigned by T1, setting the
quantity at 600, and commits the transaction. You're left with a data error. The
quantity is off, showing 350 more books than are actually there. This is one of
the most common concurrency problems, known as a lost update, which occurs
when one transaction overwrites another transaction's changes.
10.2.2 Recognizing Concurrency Problems
There are four types of concurrency problems that can result from processing
concurrent transactions. You can end up with lost updates, dirty reads, non-
repeatable reads, and phantom reads. If concurrency has to be allowed in
