NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
T-7
Rules for DDL and DML Statements
Rules for DDL and DML Statements
The following rules apply to DDL statements in transactions:
•
A DDL statement always executes within a TMF transaction or as a series of
system-defined TMF transactions.
•
Some DDL statements on audited tables can execute within a user-defined
transaction. Other DDL statements on audited tables, and all DDL statements on
nonaudited tables, cannot execute within a user-defined transaction.
DDL statements that cannot execute in a user-defined transaction automatically execute
in a system-defined transaction or a series of system-defined transactions. An error
occurs if you include these statements in user-defined transactions.
Whether a DDL statement on an audited table can execute within a user-defined
transaction depends on the statement and on the options you specify on the statement. If
a statement cannot be used within a user-defined transaction, the entry for that statement
says so.
The following rules apply to DML statements in transactions:
•
DML statements in audited tables, views of audited tables, and mixed views must be
performed within a TMF transaction except when reading data with BROWSE
ACCESS.
•
If deadlock occurs, the statement receives error 73 and is canceled; the transaction
continues.
Rules for SQLCI
In SQLCI, you do not have to define your own TMF transactions or directly secure
locks on data. By setting the AUTOWORK session option, you can specify system-
defined transactions or user-defined transactions.
•
AUTOWORK ON
If you set AUTOWORK to ON, all data in SQL operations is automatically accessed
within a TMF transaction. By default, SQL obtains locks to guarantee a specific
level of data consistency. You can control the level of data consistency by overriding
the defaults provided by the locking mechanism.
SQL defines a transaction or a series of transactions for each DDL statement and for
each DML statement. A TMF transaction does not affect DML operations on
nonaudited objects, however.
The COPY, PURGE, and SECURE utilities also execute within a TMF transaction
or series of transactions when they operate on audited SQL objects.
•
AUTOWORK OFF
Note. The TMF subsystem works only on audited objects, so a transaction does not protect
operations on nonaudited objects. The simplest approach is to make all tables audited. Audited
is the default.