Transaction processing

Auto-commit mode

When you work in auto-commit mode, each row's changes are committed to the back-end database as soon as you complete them with the ODBCResultSet object's UpdateRow or DeleteRow methods. Because the changes are committed immediately, you cannot roll them back. When you are updating a single row here and there, auto-commit mode makes sense because you know your changes are recorded before you move on. But if you are modifying large numbers of rows, auto-commit mode requires an inordinate amount of overhead.

While committing your changes to the back-end database, the DBMS must lock all of the rows to be updated, as well as that portion of the database catalog relevant to those rows. In addition, the transaction log is locked so that it can be purged. While this is happening, no other users can modify the transaction log or the locked portions of the database. They must wait until those areas are unlocked, then lock them for their own updates and unlock them afterward.

When a large number of records are committed individually, the DBMS uses a considerable amount of resources managing the locking and unlocking of the transaction log, database, and catalog. The more users competing for access to the database, the more the resources required for managing updates. Users spend more time waiting for events to occur and lose the sense of concurrency that multi-processing normally provides.

If you want to work in auto-commit mode, use the AutoCommit property of the ODBCConnection object to determine whether auto-commit mode is in effect and if not, to enable it. Your DBMS may require you to work in auto-commit mode.

Transaction mode

When you work in transaction mode, all your changes since the last commit are saved in a transaction log by the DBMS; they are not recorded in the database itself until you manually initiate a commit or you terminate the connection to that database. At any time, you can roll back the updates in the transaction log, ensuring that those changes never get recorded in the back-end database.

You can time your commit actions to occur after a specified number of records have been updated, or after certain logical transactions are complete. You can additionally have any updates remaining in the transaction log committed automatically when you terminate your connection to the back-end database.

Committing updates after a number of records has changed allows you to process the records more quickly, yet periodically record changes to the back-end database so the work isn't lost in the case of some failure. For example, when updating the Department Name field in a series of employee records, you might want to commit your changes after every 100 records have been updated.

Committing updates after a logical transaction occurs allows you to ensure that the entire transaction is successful before updating the back-end database, or to roll back the changes if the transaction somehow fails before completion. For example, if you are transferring money from a savings account to a checking account, you would debit the savings account, credit the checking account, and only then commit the update to the back-end database. If crediting the checking account somehow fails, you can roll back the change to the savings account and retry the entire transaction later.

Committing updates when you terminate your connection to the back-end database ensures that any changes remaining in the transaction log are not inadvertently lost; this is generally a good practice.

When you process records in transaction mode, you should implement some mechanism for tracking your changes so you'll know where to re-start your work if a failure forces a rollback.

Use the IsSupported method of the ODBCConnection object to determine whether transaction mode is supported by your DBMS. If it is, you can then use the AutoCommit property to determine whether auto-commit mode is enabled for the back-end database and if necessary, disable it.

While working in transaction mode, use the CommitTransactions method to manually record your updates, and the CommitOnDisconnect property to automatically record any remaining updates in the transaction log when you terminate your connection to the back-end database. Use the RollbackTransactions method to delete your updates from the transaction log without recording them in the back-end database.