HP 3000 Manuals HP 3000 Manuals
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 a 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[REV BEG] 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[REV END]
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.
Deadlocks
The second kind of conflict is known as a deadlock between two
transactions. This happens when two or more transactions need data one
of the others 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.
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; X on pages.
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 2-10
shows the deadlock condition that results from the previous example.
![[]](../../pic3k/M012555/FFN113c50.gif)
Figure 2-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.[REV BEG]
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.
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.[REV END]
Undetectable Deadlock
[REV BEG]
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 the chapter "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. Example:
CONNECT TO 'DBE1' AS 'CONNECT1';
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 in a sense on your own resources. To
avoid situations like this, be sure to set user timeout values when you
use multi-connect functionality.[REV END]