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 autocommits 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.
Ingres 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 Ingres 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: 01/30/2023