NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
W-7
Considerations—WITH SHARED ACCESS
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. (See
COMMIT OPTION for more information.)
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 cause
far less application downtime than equivalent operations without WITH SHARED
ACCESS.
The duration of a WITH SHARED ACCESS operation increases with the number
and length of transactions on the node that contains the source partition, particularly
with the number and length of transactions that involve the source partition and the
amount of activity on the audit trail used for the source partition.