TMF Application Programmer's Guide (G06.26+)

TMF Programming Environment
HP NonStop TMF Application Programmer’s Guide—522419-005
1-13
Levels of Consistency
Finally, other transactions cannot update, delete, or insert anything within the entire
range of rows accessed by the transaction. This guarantees you the opportunity to
reread previously read rows and see exactly the same data values.
You should specify REPEATABLE ACCESS for transactions that make business
decisions based on values that they have obtained by first reading the database. In
particular, if the decision is based upon the content of a range of rows, you do not want
anything to change within that range (including the addition of new rows) until the
transaction has made the appropriate decision and changed the database to a new
consistent state.
The effect of REPEATABLE ACCESS on concurrency is usually negligible compared to
that of STABLE ACCESS. If you are unsure which access mode to use, choose
REPEATABLE ACCESS.
STABLE ACCESS
STABLE ACCESS, sometimes referred to as “level-2 consistency,” provides less
stringent concurrency control than REPEATABLE ACCESS. With STABLE ACCESS
enabled:
•
The transaction will not read uncommitted changes made by other transactions.
•
Other transactions cannot alter any uncommitted changes made by the
transaction.
•
Other transactions cannot update or delete the row in which the transaction’s
cursor is currently positioned. If you are doing processing in which you first read a
row and then update it, this guarantees that data within a row will not change
between reading and updating.
The essential difference between STABLE ACCESS and REPEATABLE ACCESS is
the continuing stability of the entire range of committed data that you access. STABLE
ACCESS provides short-term stability for the row in which the transaction’s cursor is
currently positioned, while REPEATABLE ACCESS provides long-term stability (from
initial access through the end of the transaction) for the entire range of rows accessed
by the transaction.
For many types of transactions, this difference is not really an issue; sometimes,
however, it can be critical.
Consider, for example, the case in which an airline has canceled a particular flight due
to mechanical problems and is rescheduling all existing reservations for that flight to
another. Assume that the application has a NonStop SQL/MP table in which each row
represents a reservation, and that the rows are in ascending order by the passenger’s
last name. Within each row is a flight number field, which is also a nonunique index for
accessing the table.
The problem for the application is to sequentially step through the table, changing the
flight number in each row from the old one to the new one, without having additional
reservations for the old flight number be added to that part of the table that has already