SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-238
Considerations for UPDATE
specifying access options for individual DML statements instead of using SET
TRANSACTION.
Conflicting Updates in Concurrent Applications
If you are using the READ COMMITTED isolation level within a transaction, your
application can read different committed values for the same data at different times.
Further, two concurrent applications can update (possibly in error) the same column in
the same row.
In general, to avoid conflicting updates on a row, use the SERIALIZABLE isolation
level. However, note that when you use SERIALIZABLE, you are limiting concurrent
data access.
Requirements for Data in Row
Each row to be updated must satisfy the constraints of the table or underlying base
table of the view. No column updates can occur unless all these constraints are
satisfied. (A table constraint is satisfied if the check condition is not false—that is, it is
either true or has an unknown value.)
In addition, a candidate row from a view created with the WITH CHECK OPTION must
satisfy the view selection criteria. The selection criteria are specified in the WHERE
clause of the AS query-expression clause in the CREATE VIEW statement.
Reporting of Updates
When an UPDATE completes successfully, NonStop SQL/MX reports the number of
times rows were updated during the operation.
Under certain conditions, updating a table with indexes can cause NonStop SQL/MX to
update the same row more than once, causing the number of reported updates to be
higher than the actual number of changed rows. However, both the data in the table
and the number of reported updates are correct. This behavior occurs when all these
conditions are true:
•
The optimizer chooses an alternate index as the access path.
•
The index columns specified in WHERE search-condition are not changed by
the update.
•
Another column within the same index is updated to a higher value (if that column
is stored in ascending order), or a lower value (if that column is stored in
descending order).
When these conditions occur, the order of the index entries ensures that NonStop
SQL/MX will encounter the same row (satisfying the same search-condition) at a
later time during the processing of the table. The row is then updated again by using
the same value or values.