Databases Reference
In-Depth Information
After the DBMS detects deadlock, the DBMS must break the deadlock. To break the deadlock, the DBMS
chooses one deadlocked user to be the
victim
. For the victim's transaction, the DBMS undoes all com-
pleted updates, releases all locks, and reschedules the transaction. Using this method of handling deadlocks,
the user notices only a delay in the time needed to complete the transaction.
Enterprise DBMSs typically offer sophisticated schemes for locking as well as for detecting and handling
deadlocks. PC-based DBMSs provide facilities for the same purposes, but they usually are more limited than the
facilities provided by enterprise DBMSs. These limitations, in turn, put an additional burden on the program-
mers who write the programs that allow concurrent update.
Although the exact features for handling the problems associated with concurrent update vary from one
PC-based DBMS to another, the following list is fairly typical of the types of facilities provided:
●
Programs can lock an entire table or an individual row within a table, but only one or the other.
As long as one program has a row or table locked, no other program can access that row or table.
●
Programs can release any or all of the locks they currently hold.
●
Programs can inquire whether a given row or table is locked.
This list, although short, makes up the complete set of facilities provided by many PC-based DBMSs. Con-
sequently, the following guidelines have been devised for writing programs for concurrent update:
●
If an update transaction must lock more than one row in the same table, you must lock the
entire table.
●
When a program attempts to read a row that is locked, the program may wait a short period of
time and then try to read the row again. This process can continue until the row becomes
unlocked. However, it usually is preferable to impose a limit on the number of times a programmay
attempt to read the row. In this case, reading is done in a loop, which proceeds until the read is
successful or the maximum number of times the program can repeat the operation is reached.
Programs vary in terms of what action is taken should the loop be terminated without the read
being successful. One possibility is to notify the user of the problem and let the user decide
whether to try the same update again or move on to something else.
237
Because there is no facility to
detect and handle
deadlocks, you must try to
prevent
them. A com-
mon approach to this problem is for every program in the system to attempt to lock all the rows
and/or tables it needs before beginning an update. Assuming each program is successful in this
attempt, it can then perform the required updates. If any row or table that the program needs is
already locked, the program should immediately release all the locks that it currently holds, wait
some specified period of time, and then try the entire process again. In some cases, it might be bet-
ter to notify the user of the problem and see whether the user wants to try again. In effect, this
means that any program that encounters a problem will immediately get out of the way of all the
other programs rather than be involved in a deadlock situation.
●
Because locks prevent other users from accessing a portion of the database, it is important that
no user keep rows or tables locked any longer than necessary. This is especially significant for
update programs. Suppose, for example, that a user is employing an update program to update
information about customers. Suppose further that after the user enters the number of the cus-
tomer to be updated, the customer row is locked and remains locked until the user has entered
all the new data and the update has taken place. What if the user is interrupted by a phone call
before he or she has finished entering the new data? What if the user goes to lunch? The row might
remain locked for an extended period of time. If the update involves several rows, all of which
must be locked, the problem becomes that much worse. In fact, in many DBMSs, if more than one
row from the same table must be locked, the entire table must be locked, which means that entire
tables might be locked for extended periods of time. Clearly, this situation must not be permit-
ted to occur. A variation on the timestamping technique used by some enterprise DBMSs is a pro-
gramming strategy you can use to overcome this problem.
●
