SQL START TRANSACTION Statement

< > Query Statements Advanced Statements Example Flow Chart Table of Contents

The SQL START TRANSACTION statement starts a transaction by changing a connection from auto-commit mode to manual-commit mode. A transaction isolation level may also be specified.

ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection on which to start a transaction. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

TxnIsolationLevel (input). This argument must specify a numeric integer value. The value identifies the transaction isolation level to use for the transaction. The value specified must be one of sql-TXN-Read-Uncommitted, sql-TXN-Read-Committed, sql-TXN-Repeatable-Read, or sql-TXN-Serializable as defined in lisqldef.cpy and must be supported by the data source identified by the connection handle. This argument is optional; when the argument is omitted, the configured TransactionIsolation value is used. (See the topic InstantSQL Configuration for more information on configuring InstantSQL.)

Until a transaction is started, all query statements are executed in auto-commit mode. After a transaction is committed or rolled back, another SQL START TRANSACTION statement must be executed to start a new transaction; otherwise, InstantSQL will revert to auto-commit mode when an SQL statement is executed with the SQL START QUERY statement outside of a transaction for the connection.

If a transaction has already been started for the connection, the SQL START TRANSACTION statement has no effect.

Transactions are a connection level feature. Transactions may be used on one connection and not used on another connection, even when the two connections are to the same data source. However, a logical transaction, from the point of view of the application, should not be spread across multiple connections because there is no way to guarantee commit or rollback of the entire transaction.

An active transaction exists only after an SQL START QUERY statement is executed subsequent to executing the SQL START TRANSACTION statement for the same connection as the query. An active transaction must be ended with either an SQL COMMIT TRANSACTION or SQL ROLLBACK TRANSACTION statement before attempting to disconnect from the data source with an SQL DISCONNECT DATASOURCE statement. Otherwise, the disconnect attempt will fail because of the active transaction.

The default transaction isolation level is repeatable read (sql-Repeatable-Read). If the data source does not support repeatable read transaction isolation, then the TxnIsolationLevel argument must be specified or the configuration option TransactionIsolation must be changed to an isolation level supported by the data source. (See the topic InstantSQL Configuration for more information on configuring InstantSQL.) When the data source does not support the specified isolation level, the ODBC function SQLSetConnectOption will return an error, which will be reported by the SQL START TRANSACTION statement. For example, the Microsoft Access 97 driver does not support repeatable read; thus, for the default transaction isolation level, it returns the error "[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented." with SQL state S1C00, driver not capable. The SQL START TRANSACTION statement still changes the connection to manual-commit mode, but the transaction isolation level will be read committed instead of repeatable read. In this case, the error that occurs for the SQL START TRANSACTION statement can be eliminated by configuring TransactionIsolation="Read_Committed" or specifying a value of sql-TXN-Read-Committed for the TxnIsolationLevel argument.

Even when the transaction isolation level is repeatable read or serializable, multiple executions of SELECT statements within the transaction may not return the same results unless the cursor concurrency is changed from the default of sql-Concur-Read-Only to sql-Concur-Lock in the SQL PREPARE QUERY statement. For some drivers, for example, DB2, the SELECT statement must also specify the FOR UPDATE clause.

Transaction isolation is commonly achieved by use of a locking scheme in the data source. Some data sources do not support transactions. Other data sources support transactions, but the locking scheme may use page level locking instead of row level locking, which may limit the effective concurrency of transactions. Each different data source has its own lock manager that follows a particular locking scheme to achieve the specified isolation level. Please see the documentation for the data source(s) you plan to use with your application for details on the locking scheme used by that data source.

If the SQL START TRANSACTION statement changes the transaction isolation level for the connection, any open cursors for the connection will be automatically closed and any pending results discarded. This is required because some drivers (for example, the DB2 driver) do not allow the transaction isolation level to be changed when there are open cursors for the connection.

SQL START TRANSACTION Statement Examples:

      *Start a transaction using configured isolation level.
            SQL START TRANSACTION
               sql-ConnectionHandle.

      *Start a transaction using repeatable read isolation.
            SQL START TRANSACTION
               MyConnection(Conn-Num),
               sql-TXN-Repeatable-Read.

      *Start a transaction using a selected isolation level.
            EVALUATE Activity
              WHEN Act-Browsing
                MOVE sql-TXN-Read-Uncommitted TO MyIsolationLevel
              WHEN Act-Updating
                MOVE sql-TXN-Serializable TO MyIsolationLevel
            END-EVALUATE.
            SQL START TRANSACTION
               PayrollConnection,
               MyIsolationLevel.