application developers
can avoid deadlocks by using consistent application logic; for example, transactions
that access Accounts and Orders should always access the tables
in the same order. That way, in the scenario described above, Transaction
B simply waits for transaction A to release the lock on Orders before
it begins. When transaction A releases the lock on Orders, Transaction
B can proceed freely.
The appropriate use of indexes can also help you to avoid deadlocks, since
indexes make table scans less likely and reduce the number of locks obtained.
For more information, see "CREATE INDEX statement" in the
and the topics under "Avoiding
table scans of large tables" in
.
Another tool available to you is the LOCK TABLE statement. A transaction
can attempt to lock a table in exclusive mode when it starts to prevent other
transactions from getting shared locks on a table. For more information, see
"LOCK TABLE statement" in the
.