ALLBASE/SQL Reference Manual (36216-90216)

198 Chapter5
Concurrency Control through Locks and Isolation Levels
Resolving Conflicts among Concurrent Transactions
Undetectable Deadlock
Applications that connect to multiple DBEnvironments may encounter deadlocks that
cannot be detected and resolved by ALLBASE/SQL. An example follows:
Transaction 1: SET CONNECTION 'DBE1';
UPDATE TABLEA SET COL1 = 5;
Obtains X table lock.
Transaction 2: SET CONNECTION 'DBE2';
UPDATE TABLEB SET COL1 = 7;
Obtains X table lock.
Transaction 1: SET CONNECTION 'DBE2';
SELECT * FROM TABLEB;
Waits.
Transaction 2: SET CONNECTION 'DBE1';
SELECT * FROM TABLEA;
Waits--Undetectable Deadlock.
This kind of deadlock is called undetectable because ALLBASE/SQL can only detect a
deadlock within a single DBEnvironment session. It is your responsibility to coordinate
your system's use of distributed transactions so as to prevent undetectable deadlock. You
can enable ALLBASE/SQL to identify and roll back what probably are undetectable
deadlocks by setting appropriate user timeout values for each DBEnvironment connection.
For more information refer to "Using Multiple Connections and Transactions with
Timeouts" in Chapter 2 , “Using ALLBASE/SQL.”
A similar condition known as an
undetectable wait state can also arise when you are
using multi-connect functionality. An undetectable wait occurs when you connect more
than once to the same DBEnvironment from the same application in multi-transaction
mode and attempt to obtain resources held by your other connection. For example:
CONNECT TO 'DBE1' AS 'CONNECT1';a
CONNECT TO 'DBE1' AS 'CONNECT2';
SET CONNECTION 'CONNECT1';
UPDATE TABLEA SET COL1 = 5;
Obtains X table lock.
SET CONNECTION 'CONNECT2';
UPDATE TABLEA SET COL1 = 7;
Waits--Undetectable wait.
In this instance, you are waiting on your own resources. To avoid situations like this, be
sure to set user timeout values when you use multi-connect functionality
.