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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-236
Considerations for UPDATE
SERIALIZABLE | REPEATABLE READ
specifies that the UPDATE statement and any concurrent process (accessing
the same data) execute as if the statement and the other process had run
serially rather than concurrently.
SKIP CONFLICT
enables transactions to skip rows locked in a conflicting mode by another
transaction. The rows under consideration are the result of evaluating the
search condition for the UPDATE statement. SKIP CONFLICT cannot be used
in a SET TRANSACTION statement.
The default access option is the isolation level of the containing transaction, which
is determined according to the rules specified in Isolation Level on page 10-44.
WHERE CURRENT OF {cursor-name | ext-cursor-name}
specifies the name of a cursor (or extended cursor) positioned at the row to
update. If you specify cursor-name for an audited table or view, the UPDATE
must execute within a transaction that also includes the FETCH for the row. Each
column to be updated must appear in the FOR UPDATE clause of the cursor
declaration.
WHERE CURRENT OF iterator-name
specifies the name of a Java iterator variable.
For more information on searched and positioned UPDATE statements in embedded
SQL programs, see the SQL/MX Programming Manual for C and COBOL or the
SQL/MX Programming Manual for Java.
Considerations for UPDATE
In a searched UPDATE, rows are updated in sequence. If an error occurs and you are
not using DP2’s Savepoint feature, NonStop SQL/MX returns an error message and
stops updating the table. NonStop SQL/MX automatically rolls back the transaction to
undo the updated data in the audited table.
If the default INSERT_VSBB is set to USER, NonStop SQL/MX does not use
statement atomicity. Unless you are updating only a few records, you should not
disable INSERT_VSBB to use statement atomicity, because performance is affected.
Perform UPDATE STATISTICS on the tables so that row estimates are correct.
To see what rollback mode NonStop SQL/MX is choosing, you can prepare the query,
and then perform DISPLAY_OPTIONS:
display_explain options 'f' my_query;
Token “x” means that the transaction will be rolled back. Token “s” means that NonStop
SQL/MX will choose DP2 savepoints. For information, see DISPLAY_EXPLAIN
C/COBOL
Java