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, 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 because it
uses an OR:
DELETE FROM PurchDB.Parts
WHERE TID() = :PartsTID1 OR TID() = :PartsTID2
See the "Expressions" chapter of the ALLBASE/SQL Reference Manual for a
full explanation about using TID access.
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, the 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.
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 13-3 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.
_________________________________________________________________________________________
| |
| 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. |
| |
_________________________________________________________________________________________
Figure 13-3. Using RC and RR Transactions with BULK SELECT, SELECT, and UPDATE
_______________________________________________________________________________________
| |
| 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 |
| |
_______________________________________________________________________________________
Figure 13-3. Using RC and RR Transactions with BULK SELECT, SELECT, and UPDATE (2 of 2)
Reducing Commit Overhead for Multiple Updates with TID Access
Figure 13-4 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.
______________________________________________________________________________________
| |
| 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 |
______________________________________________________________________________________
Figure 13-4. Using TID Access to Reduce Commit Overhead
_____________________________________________________________________________________
| |
| 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. |
_____________________________________________________________________________________
Figure 13-4. Using TID Access to Reduce Commit Overhead (2 of 2)