Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 401

section of memory. Again, according to the record, there are 10 items in inventory. Now User A
takes five, decrements the count of items in its copy of the data to five, and rewrites the record
for Item 100. Then User B takes three, decrements the count in its copy of the data to seven,
and rewrites the record for Item 100. The database now shows, incorrectly, that there are seven
Item 100s in inventory. To review: We started with 10 in inventory, User A took 5, User B took 3,
and the database shows that 7 are in inventory. Clearly, this is a problem.
Both users obtained data that were correct at the time they obtained them. But when
User B read the record, User A already had a copy that it was about to update. This situation is
called the lost update problem or the concurrent update problem . A similar problem is the
inconsistent read problem . With this problem, User A reads data that have been processed
by a portion of a transaction from User B. As a result, User A reads incorrect data.
One remedy for the inconsistencies caused by concurrent processing is to prevent multiple
applications from obtaining copies of the same record when the record is about to be changed.
This remedy is called resource locking .

Resource Locking
One way to prevent concurrent processing problems is to disallow sharing by locking data that
are retrieved for update. Figure 9-6 shows the order of processing using a lock command.
Because of the lock, User B's transaction must wait until User A is finished with the Item
100 data. Using this strategy, User B can read Item 100's record only after User A has completed
the modification. In this case, the final item count stored in the database is two, as it should be.
(We started with 10, User A took 5, and User B took 3, leaving 2.)
Lock Terminology
Locks can be placed either automatically by the DBMS or by a command issued to the DBMS
from the application program. Locks placed by the DBMS are called implicit locks ; those
placed by command are called explicit locks . Today, almost all locking is implicit. The program
declares the behavior it wants, and the DBMS places locks accordingly. You will learn how to do
that later in this chapter.
In the preceding example, the locks were applied to rows of data. Not all locks are applied
at this level, however. Some DBMS products lock groups of rows within a table, some lock entire
tables, and some lock the entire database. The size of a lock is referred to as lock granularity .
Locks with large granularity are easy for the DBMS to administer, but frequently cause conflicts.

User A User B

Figure 9-6
Concurrent Processing
with Explicit Locks

1. Lock item 100.
2. Read item 100.
3. Reduce count by 5.
4. Write item 100.

1. Lock item 100.
2. Read item 100.
3. Reduce count by 3.
4. Write item 100.

Order of processing at database server

1. Lock item 100 for A.
2. Read item 100 for A.
3. Lock item 100 for B; cannot,
so place B in wait state.
4. Set item count to 5 for A.
5. Write item 100 for A.
6. Release A's lock on item 100.
7. Place lock on item 100 for B.
8. Read item 100 for B.
9. Set item count to 2 for B.
10. Write item 100 for B.
11. Release B's lock on item 100.

A's transaction

B's transaction