Database Reference
In-Depth Information
Change the dryer record in the Part table to decrease the record
'
s number of units on hand
by two.
Change the Brookings Direct record in the Customer table to increase the balance by the total
amount of the order.
Change the Richard Hull record in the Rep table to increase the commission by the commission
amount owed to the rep for the order.
For this order, the DBMS updates seven records in the database; it adds three records and changes four
records.
Each task that a user completes, such as filling an order, is called a transaction. A transaction is a set of
steps completed by a DBMS to accomplish a single user task; the DBMS must successfully complete all trans-
action steps or none at all for the database to remain in a correct state.
For transactions such as filling an order, in which a single user task requires several updates in the data-
base, what should the DBMS do about locks? How long does the DBMS hold each lock? For safety
240
s sake, the
DBMS should hold locks until it completes all the updates in the transaction. This approach for handling locks
is called two-phase locking. The first phase is the growing phase, in which the DBMS locks more rows and
releases none of the locks. After the DBMS acquires all the locks needed for the transaction and has com-
pleted all database updates, the second phase is the shrinking phase, in which the DBMS releases all the locks
and acquires no new locks. This two-phase locking approach solves the lost update problem.
'
Deadlock
Because each user transaction can require more than one lock, another problem can occur. Suppose Ryan is
filling the Brookings Direct order for the sale of three washers and two dryers and Elena is filling another cus-
tomer order that includes the sale of washers and dryers. Further suppose for Ryan
s transaction, the DBMS
holds a lock on the washer record and is attempting to lock the dryer record, as shown in Figure 7-9. How-
ever, the DBMS has already locked the dryer record for Elena
'
s transaction, so Ryan must wait for the DBMS
to release the lock. Before the DBMS releases the lock on the dryer record for Elena
'
s transaction, however, it
needs to update (and thus lock) the washer record, which is currently locked for Ryan
'
s transaction. Ryan is
waiting for the DBMS to act for Elena (release the lock on the dryer record), while Elena is waiting for the
DBMS to act for Ryan (release the lock on the washer record). Without the aid of some intervention, this
dilemma could continue indefinitely. Terms used to describe such situations are deadlock and the deadly
embrace. Obviously, some strategy is necessary to prevent, minimize, or manage deadlocks. You can mini-
mize the occurrence of deadlocks by making sure all programs lock records in the same order whenever pos-
sible. For example, all programs for the Premiere Products database should lock records in the Rep table and
then lock records in the Customer table consistently. A consistent locking strategy prevents situations in
which a user first locks a record in the Rep table, a second user first locks a record in the Customer table,
and both users are deadlocked while they wait for the release of records they need to lock next.
'
Ryan is waiting to lock
the dryer record already
locked by Elena
Dryer record
locked
Washer record
Dryer record
Elena is waiting to lock
the washer record already
locked by Ryan
Ryan
Database on disk
Elena
FIGURE 7-9
Two users experiencing deadlock
One strategy to manage deadlocks is to let them occur and then have the DBMS detect and break any
deadlock. To detect a deadlock, the DBMS must keep track of the collection of records it has locked for each
transaction, as well as the records it
s waiting to lock. If two transactions are waiting for records held by the
other, a deadlock has occurred. Actually, more than two users could be involved. Ryan could be waiting for a
record held by Elena, while Elena is waiting for a record held by Pat, who in turn is waiting for a record held
by Ryan.
'
