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.
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.
