Connectivity Guide > Connectivity Guide > Understanding JDBC Connectivity > JDBC Implementation Considerations > How Transactions Are Autocommitted
Was this helpful?
How Transactions Are Autocommitted
Application developers must be aware that the DBMS Server imposes severe limits on the operations that can be performed when autocommit is enabled (the JDBC default transaction mode) and a cursor is opened. In general, only one cursor at a time can be open during autocommit, and only cursor-related operations (cursor delete, cursor update) can be performed. Violating this restriction results in an exception being thrown with the message text:
No MST is currently in progress, cannot declare another cursor
Cursors are opened by the Statement and PreparedStatement executeQuery() methods and remain open until the associated ResultSet is closed. The driver closes a cursor automatically when the end of the result set is reached, but applications must not rely on this behavior. JDBC applications can avoid many problems by calling the close() method of each JDBC object when the object is no longer needed.
autocommit_mode Connection Property--Set Autocommit Processing Mode
The JDBC Driver provides alternative autocommit processing modes that help overcome the restriction of autocommitting transactions or handle problems that applications have with closing result sets.
The autocommit processing modes can be selected by setting the connection property autocommit_mode to one of the following values. For additional information, see JDBC Driver Properties.
Value
Mode
Description
dbms
DBMS (default)
Autocommit processing is done by the DBMS Server and is subject to the restrictions mentioned above.
single
Single-cursor
The DAS allows only a single cursor to be open during autocommit. If a query or non-cursor operation is requested while a cursor is open, the server closes the open cursor. Any future attempts to access the cursor fails with an unknown cursor exception. This mode is useful for applications that fail to close result sets, but does not perform other queries or non-cursor related operations while the result set is being used.
multi
Multi-cursor
Autocommit processing is done by the DBMS Server when no cursors are open. The DAS disables autocommit and begins a standard transaction when a cursor is opened. Because autocommit processing is disabled, multiple cursors can be open at the same time and non-cursor operations are permitted.
When a cursor is closed, and no other cursor is open, the DAS commits the standard transaction and re-enables autocommit in the DBMS. This mode overcomes the restrictions imposed by the DBMS during autocommit, but requires the application to be very careful in closing result sets. Because the DAS does not commit the transaction until all cursors are closed, a cursor left open inadvertently eventually runs into log-file full problems and transaction aborts.
Last modified date: 01/30/2023