ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 197
Concurrency Control through Locks and Isolation Levels
Resolving Conflicts among Concurrent Transactions
Avoiding Deadlock
The tradeoff between deadlock and throughput is one of the central issues in concurrency
control. It is important to minimize the number of deadlocks while permitting the greatest
possible concurrent access to database tables.
Avoiding Deadlock by Using the Same Order of Execution
To avoid deadlock among multiple tables, be sure to have all transactions access them in
the same order. This can often be done by modifying programs to use the same algorithms
to access data in the same order (for example, first update table 1, then table 2), rather
than accessing data in random order. This strategy cannot always be followed, but when it
can be used, processes will wait their turn to use a particular data object rather than
deadlocking.
Avoiding Deadlock by Reading for Update
You can avoid deadlocks that stem from upgrading locks by designing transactions that
use SIX locks, which have the effect of serializing updates on a table while permitting
concurrent reads. To employ SIX locks, read the table with a cursor that includes a FOR
UPDATE clause. You can also obtain SIX locks by using the LOCK TABLE statement,
specifying the SHARE UPDATE option.
Avoiding Deadlock by Using the LOCK TABLE Statement
Locking at the table level should reduce deadlocks when all or most pages in a PUBLIC
table (rows in a PUBLICROW table) are accessed in a query. Locking the table in share
update mode obtains SIX locks on the table and its pages (or rows) when you are reading
data with the intention of updating some data.
Avoiding Deadlock on Single Tables by Using PUBLICREAD and PRIVATE
The use of PUBLICREAD and PRIVATE tables decreases the chance of encountering a
deadlock by forcing serialization of updates within a single table, that is, requiring one
update transaction to be committed before another can obtain any locks on the same table.
Obviously, this reduces concurrency during update operations. You can also use the LOCK
TABLE statement for transactions on PUBLICREAD tables that read data prior to updating it.
Avoiding Deadlock by Using the KEEP CURSOR Option
In applications that declare cursors explicitly, you can use the KEEP CURSOR option in the
OPEN statement to release exclusive locks as quickly as possible. When you use the KEEP
CURSOR option for a cursor you explicitly open in a program, you can use the COMMIT WORK
statement to end the transaction and release locks without losing the cursor's position.
Furthermore, you can either retain or release the locks on the page or row pointed to by
the current cursor position. When you use the KEEP CURSOR option, your transaction
holds individual exclusive locks only for a very short time. Thus, the chance of deadlock is
reduced, and throughput is improved dramatically. For details, refer to the chapter
entitled "Processing with Cursors" in the ALLBASE/SQL application programming guide
for the language of your choice.