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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-191
Considerations for SELECT
Authorization Requirements
SELECT requires authority to read all views and tables referred to in the statement,
including the underlying tables of views referred to in the statement.
Transactions
Queries on audited tables must be performed within a transaction unless the SELECT
statement uses READ UNCOMMITTED access.
Locking Modes
When specifying the locking mode for a SELECT statement:
•
Use SHARE mode when the process reads data but does not modify it. Specifying
READ COMMITTED access and SHARE mode ensures a higher level of
concurrency.
•
Use EXCLUSIVE mode when the process reads data and then modifies it with
DELETE or UPDATE. Requesting EXCLUSIVE locks on the SELECT prevents
other processes from acquiring SHARE locks on the accessed rows between the
time of the SELECT and the time of the subsequent DELETE or UPDATE. Such
locks by other processes would prevent the process from escalating its own
SHARE locks to the EXCLUSIVE locks required for a DELETE or UPDATE
operation, causing the process to wait or timeout.
•
Do not specify the IN clause for READ UNCOMMITTED access. If you omit the IN
clause for other access options, SQL uses SHARE until an attempt is made to
modify the data, and then escalates the lock to EXCLUSIVE.
Locking modes are relevant only to SELECT operations that use a cursor. In a
standalone SELECT statement, locks are maintained only for the duration of the
SELECT.
Use of Views With SELECT
When a view is referenced in a SELECT statement, the specification that defines the
view is combined with the statement. The combination can cause the SELECT
statement to be invalid. If you receive an error message that indicates a problem but
the SELECT statement seems to be valid, check the view definition.
For example, suppose that the view named AVESAL includes column A defined as
AVG (X). The SELECT statement that contains MAX (A) in its select list is invalid
because the select list actually contains MAX (AVG (X)), and an aggregate function
cannot have an argument that includes another aggregate function.