5.25 Explain the lock table statement (continued)
Key points (continued)
The following are good uses of the LOCK TABLE statement on a PUBLICREAD table:
- If a transaction initially reads from the table and later updates it, you
should use the
LOCK TABLE IN EXCLUSIVE MODE statement to obtain an X lock on
the table prior to the first read. This action will help minimize deadlocks
with other similar transactions. When data is read, an S lock is
obtained on the PUBLICREAD table by default. Before the data can be updated, the S lock
needs to be upgraded to an X lock. A deadlock situation will arise if two
transactions have both obtained an S lock on a table, and both are trying to
upgrade their lock to an X lock. When the table is locked with an X lock
before the first read, other users can not access the table for
the duration of the transaction. Other transactions that read and update the
table will wait until the first transaction has completed, instead of
entering into a deadlock with it.
- The example above illustrates one of the most common mechanisms for
encountering deadlocks: lock promotion. To minimize deadlocks, a transaction
should avoid relying on ALLBASE/SQL to promote a weaker lock into a stronger
lock.
- A transaction should request the strongest lock that it needs before a weaker
lock is obtained. In other words, if a transaction will read data and perform
an update, it should acquire an X lock before it acquires an S lock (as was
recommended in the example above). If a transaction will only read data, it
should only acquire an S lock. You can increase the strength of the locks
obtained by a transaction by using the
LOCK TABLE command and by using the
FOR UPDATE clause when a cursor is declared (this will be explained later in
this module).
- Sometimes it is useful to split an original transaction into two transactions:
one that only reads data (so it only obtains S locks), and a second one that
refetches data (which obtains an SIX lock) to confirm the current value of the
row prior to making a modification (which obtains an X lock). A promotion
from SIX to X will usually not cause a deadlock. The
REFETCH statement
will be explained later in this module.
- You can also encounter deadlocks by obtaining locks in
different orders. All transactions should use similar
algorithms to obtain locks in the same order. This will minimize deadlocks.
- For example, if one transaction obtains an exclusive lock on table A and then
on table B, and another transaction obtains an exclusive lock on table B and
then on table A, there is a good chance that they will enter into a deadlock.
- If both transaction obtain locks in the same order (first on table A and then
on table B), the transactions will always wait on each other, instead of
deadlocking.
Page last updated on November 29, 1995
|
Printable version
