 |
» |
|
|
|
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, BULK SELECT, FETCH or BULK 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 "Expressions"
chapter of the ALLBASE/SQL Reference Manual 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.
Coding Strategies | |
Suppose you are writing an application that will be executed by many
simultaneous users in an online transaction processing environment.
You want each user to be able to locate and update just a few rows in a
table that is frequently accessed by many users. The following scenario illustrates the use of two transactions with different
isolation levels. Figure 11-2 uses the RC isolation level with a BULK SELECT
statement to obtain
data and the RR isolation level with a SELECT statement
based on TID access to verify the data before it is updated. Figure 11-2 Using RC and RR Transactions with BULK SELECT, SELECT, and UPDATE
Define two arrays, one (OrdersArray) to hold the qualifying rows of the Orders
table and another (NewOrdersArray) to hold the rows that the user wants to change.
Be sure to define an element in each array to hold the TID value.
Begin the transaction with RC isolation level. This ensures maximum
concurrency for committed data. Locks are released immediately following
data access.
BEGIN WORK RC
BULK SELECT TID(), OrderNumber, VendorNumber, OrderDate
INTO :OrdersArray, :StartIndex, :NumberOfRows;
FROM PurchDB.Orders
WHERE OrderNumber BETWEEN 30510 AND 30520
COMMIT WORK
Once all qualifying rows have been loaded into OrdersArray, end the
transaction. Then loop through the array displaying the rows and accepting any
user entered changes in NewOrdersArray. Include the appropriate TID
values with each NewOrdersArray entry.
When all user changes have been entered, use a loop to compare the previously
fetched rows (in OrdersArray) with the same rows as they now exist in the
database.
Begin your transaction with the RR isolation level. No other transaction can
access the locked data until this transaction ends, providing maximum data
integrity.
BEGIN WORK RR
For each entry in NewOrdersArray, do the following:
SELECT TID(), *
INTO :TIDvalue, :OrderNumber, :VendorNumber, :OrderDate
FROM PurchDB.Orders
WHERE TID() = :TIDHostVariable
Verify the selected data against the corresponding data in OrdersArray.
If the row is unchanged, update it using TID access.
UPDATE PurchDB.Orders
SET OrderNumber = :NewOrderNumber :NewOrderNumberInd,
VendorNumber = :NewVendorNumber :NewVendorNumberInd,
OrderDate = :NewOrderDate :NewOrderDateInd
WHERE TID() = :TIDHostVariable
If the row has changed or has been deleted, inform the user and offer
appropriate options.
COMMIT WORK
|
Reducing Commit Overhead for Multiple Updates with TID Access | |
Figure 11-3 shows how to reduce COMMIT overhead when performing
multiple updates following a BULK FETCH.
Two loops are used, each with its own cursor and
own set of locks. In the outer loop,
a BULK FETCH is performed with a cursor to load an array.
The transaction enveloping the outer loop uses an RC isolation level
to allow maximum
concurrency while the user is entering data at the terminal.
The locks associated with the BULK FETCH cursor are released
after each fetch. The inner loop uses another cursor to FETCH a single row of data
based on the TID value.
Since an RC isolation is being used, the data must be refetched to
prevent other transactions from modifying it.
The data is verified, and an UPDATE is performed. After the inner loop has finished updating the rows of data, a COMMIT
WORK is issued to actually commit the updates to the data base
and to release the exclusive locks held by the updates in the inner loop.
This use of a single COMMIT WORK for the multiple updates in the inner
loop reduces overhead. Figure 11-3 Using TID Access to Reduce Commit Overhead
Define two arrays, one (PartsArray) to hold the qualifying rows of the Parts
table and another (NewPartsArray) to hold the rows that the user wants to
change. Be sure to define an element in each array to hold the TID value.
Declare the cursor (BulkCursor) used by the BULK FETCH 4 that
loads the PartsArray.
DECLARE BulkCursor CURSOR FOR
SELECT TID(), PartNumber, PartName, SalesPrice
FROM PurchDB.Parts
Declare the cursor (TidCursor) used to UPDATE 11 an individual row based
on the TID value.
DECLARE TidCursor CURSOR FOR 1
SELECT PartName, SalesPrice
FROM PurchDB.Parts
WHERE TID() = :HostPartTid
FOR UPDATE OF PartName, SalesPrice
Begin the transaction with a RC isolation level. This ensures maximum
concurrency while assuring that only commited data is read.
BEGIN WORK RC 2
OPEN the cursor associated with the BULK FETCH 4 . The KEEP CURSOR
parameter maintains the cursor position across transactions until the
CLOSE 6 statement. The WITH NOLOCKS parameter releases all locks
associated with the cursor when the COMMIT WORK 7 statement is executed.
OPEN BulkCursor KEEP CURSOR WITH NOLOCKS
The following COMMIT WORK 3 statement preserves the open cursor
position and automatically starts a new transaction with an RC isolation level.
COMMIT WORK 3
Loop until no more rows are fetched
BULK FETCH BulkCursor INTO :PartsArray 4
Display the rows in PartsArray and move any changes entered by the user
to NewPartsArrray. Include the appropriate TID value with each
NewPartsArray entry.
For each row in the NewPartsArray
VerifyAndUpdate 8
End For
The following COMMIT WORK 5 statement commits the updates 11 in
VerifyAndUpdate and releases the locks held.
COMMIT WORK 5
End Loop
CLOSE BulkCursor 6
The final COMMIT WORK 7 statement ends the transaction started by the
BEGIN WORK RC 2 . Any locks still held are released.
COMMIT WORK 7
Begin the VerifyAndUpdate routine. 8
Assign to HostPartTid the TID value in NewPartsArray.
OPEN TidCursor
Using the cursor declared above 1 as TidCursor, perform a FETCH 9
and REFETCH 10 to verify the data. The REFETCH 10 places a lock
on the data page, to prevent another transaction from modifying the data.
The lock is held until all the rows in the NewPartsArray have been updated
and when the COMMIT WORK 5 is performed.
FETCH TidCursor INTO :PartName, :SalesPrice 9
REFETCH TidCursor INTO :PartName, :SalesPrice 10
Verify the fetched data against the corresponding row in PartsArray.
If the row is unchanged, update it using the TID cursor.
UPDATE PurchDB.Parts 11
SET PartName = :NewPartName,
SalesPrice = :NewSalesPrice
WHERE CURRENT OF TidCursor
If the row has changed or has been deleted, inform the user and offer
appropriate options.
CLOSE TidCursor
End the VerifyAndUpdate routine.
|
|
