ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 195
Concurrency Control through Locks and Isolation Levels
Resolving Conflicts among Concurrent Transactions
Deadlocks
The second kind of conflict is known as a deadlock between two transactions. This happens
when two transactions both need data or indexes that the other already has locked.
Deadlocks involving system catalog pages are also possible. ALLBASE/SQL detects and
resolves deadlocks when they occur. If different priority numbers are assigned to the
transactions in the BEGIN WORK statement, the transaction with the larger priority number
is rolled back. If no priorities are assigned, the more recent transaction is rolled back.
ALLBASE/SQL resolves deadlocks between two transactions at a time. Therefore, if more
than two transactions are deadlocked at one time, the transaction aborted may not be the
transaction with the largest priority number or the newest transaction among all
transactions deadlocked.
By default, the action taken to resolve a deadlock is to roll back one of the transactions.
However, it is also possible to set the deadlock action for a transaction to roll back the
current command instead of the entire transaction by using the SET SESSION or SET
TRANSACTION statements.
Table Type and Deadlock
Specific table types are likely to incur particular types of deadlock. Two transactions can
deadlock on the same PUBLIC or PUBLICROW table when the transactions attempt to access
the same page or row. The larger the table, the less likely it is that two transactions will
need to access the same page or row, so deadlock is reduced. If the table is small, there is
less chance of deadlock when it is defined PUBLICROW rather than PUBLIC.
The following scenario illustrates the development of a deadlock involving two fairly large
PUBLIC tables with indexes in the sample DBEnvironment PartsDBE. Assume that both
transactions are at the RR isolation level.
Transaction 1: UPDATE PurchDB.Parts SET Obtains IX lock on table,
SalesPrice = 1.2*SalesPrice; X on each page.
Transaction 2: SELECT * FROM PurchDB.SupplyPrice; Obtains S lock on table.
Transaction 1: UPDATE PurchDB.SupplyPrice SET Waits for IX on table
UnitPrice = 1.2*UnitPrice;
Transaction 2: SELECT * FROM PurchDB.Parts;
Deadlock.
This sequence results in a deadlock which causes ALLBASE/SQL to choose a transaction
to roll back. In the example, since no priorities are assigned, ALLBASE/SQL rolls back
both of user 2's queries and displays an error message. User 1's second update then
completes. Figure 5-10. shows the deadlock condition that results from the previous
example.