SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
W-7
Considerations—WITH SHARED ACCESS
warnings 1618 and 1619 to notify the user that the operation is ready to
commit and waits for the user to respond with a CONTINUE statement.
If the operation cannot move into the commit phase immediately (because it
must wait for a time window or a CONTINUE statement) the audit fix-up
processes continue reading audit trails and updating target objects to maintain
the ready-to-commit state.
3. Commit phase and command completion
SQL begins a transaction and acquires an exclusive table lock on each source
object. This stops DML transaction activity against the source objects so that
the audit fix-up processes can complete their work.
When the audit fix-up processes have finished, SQL acquires label and file
locks on all objects that participate in the operation, except source objects,
which are already locked.
(For ALTER INDEX MOVE or ALTER TABLE MOVE, objects that participate in
the operation include all partitions of the index or table involved in the move,
not just the specific partition being moved.)
SQL then updates file labels and catalog tables. At this time, the commit phase
has completed. A few extra steps such as program invalidation are performed
within the same transaction. After these final steps are performed the
transaction commits and the operation completes.

Error considerations
Errors before or after the commit phase of a WITH SHARED ACCESS operation
cause SQL to cancel changes to the database and terminate the operation, as do
nonretryable errors during the commit phase.
Retryable errors during the commit phase cause SQL to take the action specified
in the ONCOMMITERROR option of the COMMIT specification in effect. The
default is to cancel changes to the database and terminate the operation. For more
information, see COMMIT OPTION.
If the process that started a WITH SHARED ACCESS operation terminates
abnormally, the DDL operation in progress stops without being either committed or
canceled. (This halting also occurs if the user fails to issue a CONTINUE
statement in response to warning 1619, as discussed under CONTINUE.) If this
event occurs, the changes are not made to the database, but you (or another user
with the super ID) must use CLEANUP to remove the new objects. If the operation
was a CREATE INDEX operation on a table with the AUDITCOMPRESS attribute,
you must also use ALTER TABLE to reset the AUDITCOMPRESS attribute.

Performance considerations
Operations that use WITH SHARED ACCESS usually require more time to finish
than those that do not. However, because WITH SHARED ACCESS operations
allow concurrent read and write access to the source partition, such operations