Neoview SQL Reference Manual (R2.4)

— A holdable cursor
— A SELECT statement with an embedded UPDATE or DELETE
— A DDL statement
— An UPDATE STATISTICS statement
• The query plan does not choose VSBB inserts or Executor Server Process (ESP) parallelism.
• The AUTOCOMMIT option must be set to ON.
If these conditions are not met, the transaction is aborted by Neoview SQL if a failure occurs.
This behavior occurs for all INSERT, UPDATE, or DELETE statements in Neoview SQL.
User-Defined and System-Defined Transactions
User-Defined Transactions
Transactions you define are called user-defined transactions. To ensure that a sequence of statements
executes successfully or not at all, you can define one transaction consisting of these statements
by using the BEGIN WORK Statement and COMMIT WORK Statement. You can abort a
transaction by using the ROLLBACK WORK Statement.
System-Defined Transactions
In some cases, Neoview SQL defines transactions for you. These transactions are called
system-defined transactions. Most DML statements initiate transactions implicitly at the start of
execution. See “Implicit Transactions” (page 190). However, even if a transaction is initiated
implicitly, you must end a transaction explicitly with the COMMIT WORK statement or the
ROLLBACK WORK statement. If AUTOCOMMIT is on, you do not need to end a transaction
explicitly.
Rules for DML Statements
If deadlock occurs, the DML statement times out and receives an error.
Effect of AUTOCOMMIT Option
AUTOCOMMIT is an option that can be set in a SET TRANSACTION statement. It specifies
whether Neoview SQL will commit automatically, or roll back if an error occurs, at the end of
statement execution. This option applies to any statement for which the system initiates a
transaction. See “SET TRANSACTION Statement” (page 187).
If this option is set to ON, Neoview SQL automatically commits any changes, or rolls back any
changes, made to the database at the end of statement execution.
Concurrency
Concurrency is defined by two or more processes accessing the same data at the same time. The
degree of concurrency available—whether a process that requests access to data that is already
being accessed is given access or placed in a wait queue—depends on the purpose of the access
mode (read or update) and the isolation level.
Neoview SQL provides concurrent database access for most operations and controls database
access through the mechanism for locking and the mechanism for opening and closing tables.
For DML operations, access and locking options affect the degree of concurrency. See “Data
Consistency and Access Options” (page 27), “Database Integrity and Locking” (page 29), and
“SET TRANSACTION Statement” (page 187).
The following tables describe interactions between SQL operations.
Table 1-1 compares operations with access modes and lists DDL and Utility operations you can
start while DML operations are in progress.
Transaction Management 31