SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-118
Considerations for DELETE
Authorization Requirements
DELETE requires authority to read and write to the table or view being deleted from
and authority to read tables or views specified in subqueries used in the search
condition.
Transaction Initiation and Termination
The DELETE statement automatically initiates a transaction if there is no active
transaction and if the statement references an audited table. Otherwise, you can
explicitly initiate a transaction with the BEGIN WORK statement. When a transaction is
started, the SQL statements execute within that transaction until a COMMIT or
ROLLBACK is encountered or an error occurs.
Isolation Levels of Transactions and Access Options of
Statements
The isolation level of an SQL transaction defines the degree to which the operations on
data within that transaction are affected by operations of concurrent transactions.
When you specify access options for the DML statements within a transaction, you
override the isolation level of the containing transaction. Each statement then executes
with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION
statement. See SET TRANSACTION Statement on page 2-240.
The default isolation level of a transaction is determined according to the rules
specified in Isolation Level on page 10-47.
When you specify any statement level attribute, all attributes are used from the
statement specification and they override session level attributes.
When you specify one or more SET TRANSACTION attributes at the statement level,
all the other SET TRANSACTION settings revert to their default values for that
statement instead of the current session-level attribute values. For example, if you
specify the 'in share mode' option with a SQL Statement, the statement level options
will be applied. Thus, all attributes are chosen at the statement level, including the
isolation level.
Therefore, if any attributes are specified for a given statement, all other SET
TRANSACTION session-level settings that do not have the default value should also
be specified.
It is important to note that the SET TRANSACTION statement might cause a dynamic
recompilation of the DML statements within the next transaction. Dynamic
recompilation occurs if NonStop SQL/MX detects a change in the transaction mode at
run time compared with the transaction mode at the time of static SQL compilation. To
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed