Neoview SQL Reference Manual (R2.5)

1. Transaction is started.
2. Database changes are made.
3. Transaction is committed.
If, however, the changes cannot be made or if you do not want to complete the transaction, you
can abort the transaction so that the database is rolled back to its original state.
This subsection discusses these considerations for transaction management:
• “Statement Atomicity” (page 28)
• “User-Defined and System-Defined Transactions” (page 28)
• “Rules for DML Statements” (page 29)
• “Effect of AUTOCOMMIT Option” (page 29)
• “Concurrency” (page 29)
• “Transaction Access Modes” (page 31)
• “Transaction Isolation Levels” (page 32)
• “Transaction Rollback Mode” (page 33)
Statement Atomicity
To maintain database consistency, transactions must be controlled so that they complete
successfully or are aborted. SQL does not automatically abort transactions unless something
changed that requires work to be rolled back, such as a network problem in the middle of updating
a row.
SQL guarantees that an individual SQL statement within a transaction completes successfully
or has no effect on the database.
When an INSERT, UPDATE, or DELETE statement encounters an error, that transaction is not
aborted, but continues. The effect of the SQL statement is rolled back, so the statement has no
effect on the database, but the transaction is not aborted.
Statement atomicity is met if these conditions are met:
• The UPD_ABORT_ON_ERROR default must be set to OFF (the default).
• The underlying table must not have referential integrity constraints, or triggers.
• The SQL query is not:
— A publish/subscribe query with stream access
— A CALL statement
— 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 use 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. If AUTOCOMMIT is on, you do not have to end
the transaction explicitly as Neoview SQL will end the transaction automatically. Sometimes an
error occurs that requires the user-defined transaction to be aborted. Neoview SQL will
28 Introduction