Neoview SQL Reference Manual (R2.4 SP2)

Lock Holder
Only the lock holder can release a lock. A transaction releases the locks it holds at the end of the
transaction in either of these cases:
• Locks on data read using SERIALIZABLE access
• Locks on rows updated
Transaction Management
A transaction (a set of database changes that must be completed as a group) is the basic recoverable
unit in case of a failure or transaction interruption. Transactions are controlled through client
tools that interact with the database using ODBC or JDBC. The typical order of events is:
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 29)
• “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.
28 Introduction