SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-112
Considerations for DELETE
SERIALIZABLE | REPEATABLE READ
specifies that the DELETE 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 DELETE 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-45.
WHERE CURRENT OF {cursor-name | ext-cursor-name}
specifies the name of a cursor (or extended cursor) positioned at the row to delete.
If you specify cursor-name for an audited table or view, the DELETE must
execute within a transaction that also includes the FETCH for the row. For more
information about cursor names and extended cursor names, see DECLARE
CURSOR Declaration on page 3-22 and ALLOCATE CURSOR Statement on
page 3-3.
For more information on searched and positioned DELETE statements in
embedded SQL programs, see the SQL/MX Programming Manual for C and
COBOL.
Considerations for DELETE
In a searched DELETE, rows are deleted in sequence. If an error occurs and you are
not using DP2’s Savepoint feature, NonStop SQL/MX returns an error message and
stops deleting from the table. NonStop SQL/MX automatically rolls back the transaction
to undo the deleted data from the audited table.
If the default INSERT_VSBB is set to USER, NonStop SQL/MX does not use
statement atomicity. Unless you are deleting 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. See DISPLAY_EXPLAIN Command on
page 4-13 for details. For details about these defaults, see INSERT_VSBB on
page 10-61 and UPD_SAVEPOINT_ON_ERROR on page 10-64.
C/COBOL