ALLBASE/SQL Reference Manual (36216-90216)

196 Chapter5
Concurrency Control through Locks and Isolation Levels
Resolving Conflicts among Concurrent Transactions
Figure 5-10. Deadlock
The use of PRIVATE tables ensures there will be no deadlock on the same table, because
access to the table is serialized. However, deadlock across two or more tables is common
with PUBLICREAD and PRIVATE tables that are accessed by different transactions in
different order. The following example shows a deadlock involving a PRIVATE table:
Transaction 1: SELECT * FROM TABLEA; Obtains X lock on table.
Transaction 2: SELECT * FROM TABLEB; Obtains X lock on table.
Transaction 1: SELECT * FROM TABLEB; Waits for X on table.
Transaction 2: SELECT * FROM TABLEA; Deadlock.
A common deadlock scenario for PUBLICREAD tables is to do a SELECT, thus obtaining a
table level share lock, and then an UPDATE, which must upgrade the lock to exclusive:
Transaction 1: SELECT * FROM TABLEA; Obtains S lock on table.
Transaction 2: SELECT * FROM TABLEA; Obtains S lock on table.
Transaction 1: UPDATE TABLEA; Waits to upgrade to X on table.
Transaction 2: UPDATE TABLEA; Deadlock.
The need to upgrade frequently results in deadlock.
Table Size and Deadlock
The size of a table is another factor affecting its susceptibility to deadlock. If the table is
small, it is highly probable that several users may need the same pages, so deadlocks may
be relatively frequent when page level locking is used. The probability of collision is
highest when the table is small and its rows are also small, with many stored on one page.
If the table is large, it is relatively unlikely that multiple users will want the same pages
at the same time, so page level locking should cause relatively few deadlocks.