Neoview SQL Reference Manual (R2.5)

Restrictions for the MULTI COMMIT option:
• Not compatible with the READ UNCOMMITTED isolation level.
• Not compatible with READ ONLY access mode.
• Except for MULTI COMMIT, all of the SET TRANSACTION options are valid for the child
transactions and not the logical parent transaction.
• Not compatible with the NO ROLLBACK option set to ON.
This table shows the compatibility of MULTI COMMIT (ON and OFF) with other SET
TRANSACTION modes:
OFFONMULTI COMMIT
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
READ UNCOMMITTED
READ COMMITTED
SERIALIZABLE
REPEATED READ
ISOLATION LEVEL ACCESS OPTION
Yes
Yes
No
Yes
READ ONLY
READ WRITE
ACCESS MODE
Yes
Yes
Yes
Yes
ON
OFF
AUTOCOMMIT
Yes
Yes
No
Yes
ON
OFF
NO ROLLBACK
Yes
Yes
Yes
Yes
Yes
Yes
TIMEOUT
0
RESET
AUTOABORT
Yes denotes compatible
No denotes not compatible
Implicit Transactions
Most DML statements are transaction initiating—the system automatically initiates a transaction
when the statement begins executing.
The exceptions (statements that are not transaction initiating) are:
• COMMIT, FETCH, ROLLBACK, and SET TRANSACTION
• DML statements executing under READ UNCOMMITTED access
• EXECUTE or EXECUTE IMMEDIATE, which are transaction initiating only if the associated
statement is transaction-initiating
Explicit Transactions
You can issue an explicit BEGIN WORK even if the autocommit option is on. The autocommit
option is temporarily disabled until you explicitly issue COMMIT or ROLLBACK.
Degree of Concurrency
The SET TRANSACTION statement affects the degree of concurrency available to the transaction.
Concurrent processes take place within the same interval of time and share resources. The degree
of concurrency available—that is, whether a process that requests access to data already being
accessed is given access or placed in a wait queue—is affected by:
• The transaction access mode (READ ONLY or READ WRITE)
• The transaction isolation level (READ UNCOMMITTED, READ COMMITTED,
SERIALIZABLE, or REPEATABLE READ)
• The transaction MULTI COMMIT mode
SET TRANSACTION Statement 191