SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-6
Avoiding Contention Between DDL or Utility
Operations
Avoiding Contention Between DDL or Utility Operations
Only one DDL or utility statement can operate on a given SQL/MX object (or partition
of an SQL/MX object) at a time. An error occurs if you attempt to execute a DDL or
utility statement while another process is executing a DDL or utility statement on the
same object.
The specific error depends on the statement involved and the phase of the operation at
which the conflict occurs. Common file system errors for this situation include:
12 File in use
40 The operation timed out
73 The table is locked
You can also get this SQL/MX error message:
For detailed information about this and other SQL/MX error messages, see the
SQL/MX Messages Manual.
Other Operational Considerations
In general, SQL/MX utility operations follow a three-step process:
1. Lock the metadata for the object being acted upon. Also acquire a DDL lock to
prevent metadata or label changes from occurring until the utility operation
completes. The DDL lock encompasses several transactions and persists until the
utility operation is complete.
2. Perform the utility operation. This step encompasses many transactions.
3. Update metadata to reflect changes caused by the utility operation.
4. Remove the DDL lock.
Because the locks used in Step 1 of the operation are exclusive, they have no special
priority over other locks that can also be issued on the objects. Therefore, to enable
the exclusive locks required by these functions, you might need to manage the
application activity as follows:
1. During Step 1, do not compile programs that require access to the metadata
involved for update or that refer to affected objects.
2. During Step 2, you can resume application transaction activity.
3. During Step 3, quiesce application transaction activity so that locks are not in
contention.
These situations can arise during the operation of long-running utility functions:
1134 A concurrent utility or DDL is being performed on object
object-name, its parent, or one of its dependencies. That
operation must complete before the requested operation can
run.