AUTOCOMMIT
The SET AUTOCOMMIT ON statement directs the DBMS Server to treat each query as a single-query transaction.
SET AUTOCOMMIT OFF, the default, means an explicit COMMIT or ROLLBACK statement or terminating the session is required to terminate a transaction.
SET AUTOCOMMIT READ auto-commits a transaction that was started in read-only mode.
Transactions are started read only when no transaction is currently started, and:
• The SET [SESSION or TRANSACTION] READ ONLY statement is in effect, or:
• The AUTOCOMMIT state is ON or READ, and one of the following query types is issued:
– A SELECT statement (including SELECT loop in Embedded SQL)
– A COPY INTO statement
An OPEN cursor statement does not suffice to determine a read-only transaction, even in the cursor is declared FOR READONLY.
Actian X cursors can nest or overlap, and an updatable cursor might be opened before the first cursor closes.
If AUTOCOMMIT READ is in effect and an updating statement is issued, a multi-statement transaction will be started. You must explicitly end such a transaction with COMMIT or ROLLBACK.
SET AUTOCOMMIT READ can be helpful for Actian X query sessions, as it releases any locks the transaction might be holding. Even a READLOCK=NOLOCK session takes out internal control locks that would be released by the automatic commit.
The SET AUTOCOMMIT statement cannot be issued in an open transaction. For a description multi-statement transaction behavior, see the chapter “Working with Transactions and Handling Errors.”
Last modified date: 08/14/2024