Autocommit is available for ODBC-based drivers, including SQL Server. When autocommit on, all SQL operations are committed as soon as they are executed. When autocommit is off, all transactions remain open until they are committed with %SSC_COMMIT or rolled back with %SSC_ROLLBACK.
For SQL Server, autocommit is off by default. For other ODBC connections, autocommit is on by default. To turn autocommit on or off, use %SSC_CMD with the SSQL_ODBC_AUTOCOMMIT option (see %SSC_CMD).
Note the following:
- For individual statements (where every statement is its own transaction), turn on autocommit.
- For true transactions that encompass multiple-statements, turn autocommit off and use %SSC_COMMIT and %SSC_ROLLBACK as described below.
- To submit a large number of statements that do not need explicit transactions (e.g., if you are inserting a few thousand items into a customer table as a batch operation), turn off autocommit, start a transaction at the beginning of the batch of statements, and then commit the transaction and start a new transaction after every 1,000 statements. This can significantly improve the performance of inserts with SQL Server.
To control transaction blocks, use the SQL Connection functions %SSC_COMMIT and %SSC_ROLLBACK, as in the following:
begin transaction (%SSC_COMMIT) update order data if error on update rollback (%SSC_ROLLBACK) else update customer if error on update customer rollback (%SSC_ROLLBACK) else commit (%SSC_COMMIT) endif endif
If autocommit is off, %SSC_COMMIT or %SSC_ROLLBACK must be used after data access (DML) operations, and you should call %SSC_COMMIT before invoking a DML operation (this includes insert, update, delete, and select commands). Note, however, that the database determines if %SSC_COMMIT must be called before the database is actually modified.
|
If a database engine employs a cursor caching mechanism, it is critical to end transactions explicitly. If you do not, the cache will quickly exhaust system resources. If you are accessing an Oracle database, Oracle recommends that you explicitly end every transaction in your application with a COMMIT or ROLLBACK statement. This includes the final transaction, the one before you disconnect. If the application terminates abnormally and you have not done this, the last uncommitted transaction is automatically rolled back. |
Row locking and transactions
While write transactions or read-with-lock transactions are in process, affected data rows are locked by the database engine. Depending on the type of lock, adjacent rows may be locked as well (for example, in page-level locking). A lock persists until the database engine commits or rolls back the data, thereby closing the transaction and releasing any affected rows. However, note the following:
- If autocommit is off and you do not commit the transaction using %SSC_COMMIT, SQL Connection will automatically roll back any pending transactions when the %SSC_RELEASE function is called. For more information on locking, see Understanding updates and locking.
- A commit does not cause VTX12_SQLNATIVE to automatically release locks for rows selected with an UPDLOCK hint. (This is a Microsoft restriction.) If the application won’t read another row, be sure to use %SSC_SCLOSE to remove such locks. Additionally, use %SSC_SCLOSE before a rebind if you do not immediately close the cursor with %SSC_CLOSE.
