Executing transactions

In auto-commit mode, each SQL statement is a complete transaction, which is automatically committed when the statement finishes executing. See the important notes in the “Committing read-Only transactions” section below on committing read-only SELECTs.

In manual-commit mode, a transaction consists of one or more statements. In manual-commit mode, 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 commits or rolls back the transaction with SQLEndTran.

▪When the isolation level is other than READ COMMITTED, even read-only statements (for example, SELECT) must be committed. Furthermore, the user must commit SELECT statements even if the server is in autocommit mode. Failure to commit statements can reduce performance or cause the server to run out of memory. This is explained in more detail below.

▪If the isolation level is READ COMMITTED, read-only statements need not be committed. In that case, the explanation below does not apply.

Even a read-only statement must be committed. The reason for this is that solidDB^® saves the “read-level” of each transaction and until that transaction commits, 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 solidDB^® Administration Guide for more details about the Bonsai Tree.) If a transaction is not committed, the server will need more and more memory as other transactions accumulate; this will reduce performance, and eventually the server may run out of available memory. For more details, see “Performance Tuning” in solidDB^® Administration Guide.

Using autocommit mode does not ensure that SELECT statements are committed. The server cannot automatically commit SELECTs because SELECTs do not execute as a single statement. Each SELECT involves opening a cursor, fetching rows, and then closing the cursor.

There are two possible ways that the server could automatically commit when fetching multiple rows: the server could commit after the final fetch, or the server could commit after each individual fetch. Unfortunately, neither of these is practical, and therefore the server cannot commit the SELECT statement even in autocommit mode.

The server cannot automatically commit after the final fetch because the server does not know which fetch is the final fetch — the server does not know how many rows the user will fetch. (Until the user closes the cursor, the server does not know that the user is done fetching.)

It is not practical to commit after each individual fetch because each transaction should see the data as it was at the time that the transaction started, and therefore if each fetch is in a different transaction then the data can be from a different “snapshot” of the database. Putting each fetch in a different transaction would also make REPEATABLE READ and SERIALIZABLE transaction isolation levels confusing or meaningless for the cursor, even though the cursor is for a single SELECT statement.

To commit the SELECT statement, the user may:

▪If the cursor is the only open cursor, then the user may commit by explicitly closing the cursor (the server automatically commits when a cursor is closed and there are no other open cursors (and the server is in autocommit mode). This is part of why we recommend that you explicitly close every cursor as soon as you are done with it.

Note To ensure that the data in the cursor is consistent and recent, the server actually does an automatic commit immediately prior to opening the cursor (if autocommit is on). The server then immediately starts a new transaction to contain the subsequent FETCH statement(s). This new transaction, like any other transaction, must be committed (or rolled back).

All statements must be committed, even if they are read-only statements, if an isolation level other than READ COMMITTED is used.

In most cases when you are doing SELECT statements in autocommit mode, you should explicitly close each cursor as soon as you are done with it and then explicitly COMMIT, even though you are in autocommit mode.