SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-259
Considerations for self-referencing inserts
Audited and Nonaudited Tables
SQL/MX tables must be audited. You can run NonStop SQL/MX against nonaudited
SQL/MP tables.
The TMF product works only on audited tables, so a transaction does not protect
operations on nonaudited tables. Nonaudited tables follow a different locking and error
handling model than audited tables. Certain situations such as DML error occurrences
or utility operations with DML operations can lead to inconsistent data within a
nonaudited table or between a nonaudited table and its indices.
To avoid problems, do not run DDL or utility operations concurrently with DML
operations on nonaudited tables. When you try to delete data in a nonaudited table
with an index, NonStop SQL/MX returns an error.
Considerations for self-referencing inserts
•
The Halloween problem occurs when rows are updated using an index that
contains a column being updated. As the column is updated, the current index
entry is deleted, creating a possibility that a new index could be inserted ahead of
the current entry. Similarly, when a self-referencing insert uses the primary key or
an index to select rows from a table that are inserted into the same table, the newly
inserted rows may also qualify the selection criteria and thus the insert statement
may create an infinite loop. When this happens, the rows appear repeatedly.
SQL/MX Release 3.2 overcomes the Halloween problem using the following
methods:
•
Blocking Plan Operator Method - A blocking operator is introduced in the
query plan to ensure that all the selected rows are read from the target
table before inserting new rows. Self-referencing UPDATE, DELETE
statements and UPDATE statements on primary key unique index columns
use this method.
•
DP2 Locks Method (DP2 Locks) - DP2 ensures that newly added rows
are skipped for the scan. By default, the DP2 Locks Method is used to
overcome the Halloween problem.
The optimizer chooses the Blocking Plan Operator Method over the DP2
Locks Method when the following criteria are met:
•
Estimated number of rows to be inserted (input cardinality) is greater
than the lock escalation limit per partition
•
SERIALIZABLE ACCESS for the insert-source
•
READ UNCOMMITTED ACCESS for the insert-source
•
CQD BLOCK_TO_PREVENT_HALLOWEEN is ON
•
TABLELOCK is set to ON on the target table