Databases Reference
In-Depth Information
and respond appropriately. The most common error, as with the situation in Fig-
ure 10-8, is that one transaction eventually times out. As a response, this means
recognizing that an update has timed out, rolling back the transaction, and then
resubmitting the changes in the context of a new transaction.
Avoiding Blocked Transactions
The best solution, whenever possible, is to avoid blocked transactions and dead-
locks in the first place. Earlier in the chapter you were given guidelines for
designing transactions that minimize concurrency errors. These same guidelines
apply to avoiding blocks and deadlocks.
Two key considerations are the transaction length and the order in which
transactions access resources. In Figure 10-8, if both transactions accessed
resources in the same order you would have had one temporarily blocking the
other, but could have avoided the deadlock. Of course, this would depend on
an ideal development environment where developers, even those working on dif-
ferent applications, keep in close contact with each other to ensure that this stan-
dard is met. Each developer will probably have his or her idea as to the best
order. The order will also be influenced by the application's specific require-
ments. In the real word, the best you can usually hope for is to have the trans-
actions within a single application access resources in a consistent order.
You can also minimize the occurrence of blocked transactions and deadlocks
by using appropriate locking levels. You might have avoided the deadlock if the
transactions acquired locks at the row level instead of the table level. As long as
the transactions don't need access to the same rows within a table, neither is
blocked by the other.
You need to avoid holding a transaction open any longer than necessary. As
soon as the transaction completes its processing, you should either commit or
roll back as appropriate. Either action will cause the transaction to release any
locks it has acquired. Leaving a transaction open is sometimes an unexpected
consequence of nested transactions.
One way that database management systems detect blocked transactions is
through time-outs. A
time-out
occurs when a transaction is unable to complete pro-
cessing within a specified time. You can configure the maximum time allowed, typ-
ically in milliseconds, or configure the DBMS to allow the transaction to run indef-
initely until it finishes (usually not recommended because of the potential adverse
affect on performance).
The most common reason for a transaction to time-out is that it can't access
the resources it needs and waits for those resources until they either become avail-
able or the transaction times out. Time-outs can sometimes result because there is
an excessive load on the database server, especially if the time-out periods are set
too short for the operational environment. They can also occur as the result of an
error.
