In manual commit mode, a transaction consists of one or more statements. When an application submits an SQL statement and no transaction is open, the driver implicitly begins a transaction. The transaction remains open until the application explicitly commits or rolls back the transaction with SQLEndTran.
In autocommit mode, each SQL statement is a complete transaction, which is automatically committed when the statement finishes executing, except in the following circumstances:
▪ Isolation level is not READ ONLY
When the isolation level is REPEATABLE READ or SERIALIZABLE (see Controlling transaction isolation levels), all statements must be explicitly committed, even if the server is in autocommit mode. solidDB saves the "read-level" of each transaction and, until that transaction is committed, all subsequent transactions from other connections are also maintained in memory. This behavior is part of the row versioning performed by the Bonsai tree technology, see Data storage for disk-based tables.
If a transaction is not committed, the server needs more and more memory as other transactions accumulate; this reduces performance, and eventually the server might run out of available memory. For more details, see Performance tuning.
▪ Statement is a SELECT statement
Using autocommit mode does not ensure that SELECT statements are committed. The server cannot automatically commit SELECT statements because SELECT statements do not execute as a single statement. Each SELECT statement involves opening a cursor, fetching rows, and then closing the cursor.
The server cannot automatically commit after the final FETCH statement because, until the user closes the cursor, the server does not know which statement is the final FETCH statement.
When in autocommit mode, a commit operation automatically takes place after the following actions:
– Execution of an explicit COMMIT WORK statement.
– Execution of a statement to which autocommit does apply (a statement other than a SELECT statement).
– Opening of a cursor. To ensure that the data in the cursor is consistent and recent, the server does a commit operation immediately prior to opening the cursor. The server then immediately starts a new transaction to contain the subsequent FETCH statements. This new transaction, like any other transaction, must be committed (or rolled back).
– Closure of the only open cursor. If the server is in autocommit mode, the server automatically commits when a cursor is closed and there are no other open cursors.
However, in most cases when you are doing SELECT statements in autocommit mode, you should explicitly close each cursor as soon as you are finished with it and then explicitly commit the transaction.