ALLBASE/SQL Reference Manual (36216-90216)

194 Chapter5
Concurrency Control through Locks and Isolation Levels
Resolving Conflicts among Concurrent Transactions
Resolving Conflicts among Concurrent Transactions
Several kinds of conflict can occur between transactions that are contending for access to
the same data. The following three are typical cases:
• One transaction has locked an object that another transaction needs and is in a wait
state.
• Two transactions each need an object the other transaction has locked in the same
DBEnvironment and are both in a wait state.
• Two transactions each need an object the other transaction has locked in another
DBEnvironment and are both in a wait state.
The first conflict results in a lock wait, which simply means that the second transaction
must wait until the first transaction releases the lock. The second conflict is known as
conventional deadlock, which is automatically resolved by ALLBASE/SQL. The third
conflict is an undetectable deadlock, which cannot be automatically resolved.
Lock Waits
When a transaction is waiting for a lock, the application pauses until the lock can be
acquired. When a transaction is in a wait state, some other transaction already has a lock
on the row, page, or table that is needed. When the transaction that is holding a lock on the
requested row, page, or table releases its lock through a COMMIT WORK or ROLLBACK WORK
statement, the waiting transaction can then acquire a new lock and proceed.
The amount of time an application waits for a lock depends on the timeout value. A
timeout value is the amount of time a user waits if a requested database resource is
unavailable. If an application times out while waiting for a lock, an error occurs and the
transaction is rolled back. See the SET USER TIMEOUT statement in the "SQL Statements"
chapter of this manual for more information.
The larger the number of lock waits, the slower the performance of the DBEnvironment as
a whole. You can observe the lock waits at any given moment in the DBEnvironment by
issuing the following query:
isql=> SELECT * FROM SYSTEM.CALL WHERE STATUS = 'WAITING ON LOCK';
The use of isolation levels less severe than Repeatable Read can improve concurrency by
reducing lock waits. For example, reporting applications that do not depend on perfect
consistency can use the Read Uncommitted level, while applications that scan an entire
table to update just a few rows can use Read Committed with REFETCH or Read
Uncommitted with REFETCH for the greatest concurrency. Applications that intend to
update a larger number of rows can use Cursor Stability.
You can set the amount of time a transaction will wait for a lock by using the SET USER
TIMEOUT statement, or by setting a default timeout value using the ALTDBE command in
SQLUtil. If no timeout value is set as a default, the transaction will wait until the resource
is released. Consult your database administrator about default timeout values.