HP 3000 Manuals HP 3000 Manuals
Transaction Management with TID Access
TID data access is fast, and it must be used with care. A great deal of
flexibility of use is possible, and exactly how it should be used depends
on your application programming needs.
The next sections look at performance, concurrency and data integrity
issues involved in designing database transactions that use TID access.
Although a possible usage scenario is given, you must decide how to
combine the elements of transaction management to best suit your
purposes. The following concepts are highlighted:
* Comparing TID Access to Other Types of Data Access.
* Insuring that the Optimizer Chooses TID Access.
* Verifying Data that is Accessed by TID.
* Stable versus Volatile Data.
* Using Isolation Levels with the TID Function.
* Considering Interactive User Applications.
* Coding Strategies.
TID access requires an initial SELECT or FETCH to obtain TID values. You
can then SELECT, UPDATE or DELETE data by TID.
Comparing TID Access to Other Types of Data Access
When using TID functionality, data access speed is always improved
compared to the speed of other ALLBASE/SQL access methods, for the
following reasons:
* Index access must lock more pages (i.e. index pages).
* Relation access locks more pages to find the TID of any qualifying
row.
* Hash access employs more search overhead.
Note that use of the TID function in a WHERE clause does not guarantee
that TID access will be chosen by the optimizer. For example, the
following statement would utilize TID access:
DELETE FROM PurchDB.Parts
WHERE TID() = :PartsTID AND PartName = 'Winchester Drive'
However, in the next statement TID access would not be used:
DELETE FROM PurchDB.Parts
WHERE TID() = :PartsTID1 OR TID() = :PartsTID2
See the "Command Syntax" chapter in this document under the "Description"
section for an explanation of the above and additional optimization
criteria.
Verifying Data that is Accessed by TID
It is important to note that a TID in ALLBASE/SQL is unique and is valid
until its related data is deleted. You must take precautions to assure
that the data you are selecting or changing exists when your statement
executes. (Note that a TID can be reassigned after its data has been
deleted.)
You can rely on the existence of a given TID, if you know its data won't
be deleted. That is, you know the nature of the data is non-volatile.
In this case, you can select the TID and update by TID with the assurance
that data integrity will be maintained. An example might be a table that
has been created as private. Another example might be a table that you
know is currently being accessed only by your application. (You have
begun the transaction with the RR isolation level, or you have used the
LOCK TABLE command.)
By contrast, you may be dealing with data that changes frequently. In
cases where you are using the CS, RC, or RU isolation levels, you must
verify that your data has not changed between the time you select it and
the time you update or delete it. A method is to end the transaction in
which you selected the data, and begin an RR transaction in which you use
a SELECT statement with the TID function in the WHERE clause. See the
following section titled "Coding Strategies" for an example.
When you attempt to access a row for update or delete, status checking
procedure is the same as for a statement that does not contain the TID
function. An application must check the sqlcode field of the sqlca for a
value of 100. ISQL displays, "Number of rows selected is 0" for a SELECT
statement and "Number of rows processed is 0" for an UPDATE or DELETE
statement.
Status checking is discussed in detail in the ALLBASE/SQL application
programming guides. Refer to the guide for the language you are using.
Considering Interactive User Applications
Some transaction management basics that apply to TID functionality when
used in interactive applications are listed below:
* Be sure to avoid holding locks against the database within a
transaction driven by interactive user input. This is sometimes
termed "holding locks around terminal reads." It means that the
speed at which the user enters required data determines the
execution time of your transaction and thus the time span of
transaction locks.
* Does your transaction use the RR isolation level? If so, there is
no need to verify your data prior to updating or deleting within
the same transaction.
* Does your transaction use the CS, RC, or RU isolation level? If
so, in order to maintain data integrity, you must verify that the
data has not changed before you attempt to update or delete it.
By verifying the data in this way, you insure that it still exists
and can determine whether or not it has changed from the time it
was last presented to the user.