Database 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 completed
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.
'
Locking on PC-Based DBMSs
Enterprise DBMSs typically offer sophisticated schemes for locking as well as for detecting and handling dead-
locks. PC-based DBMSs provide facilities for the same purposes, but they usually are much more limited than
the facilities provided by enterprise DBMSs. These limitations, in turn, put an additional burden on the pro-
grammers 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:
241
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 may access that row or
table.
Programs can release any or all of the locks that 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 program
may 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 that 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.
Because there is no facility to detect and handle deadlocks, you must try to prevent them. A
common 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 proogram 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 better 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 permitted to occur. A variation on the timestamping technique used by some enterprise
DBMSs is a programming strategy you can use to overcome this problem.
