Autocommit

To shorten the length of time a lock is held and to reduce network overhead while maintaining a reasonable degree of database consistency, configure the system to automatically commit a command as soon as it is executed. Autocommit increases concurrency by immediately committing commands that change tables within a location. Since the transaction is a single command long, the use of ROLLBACK is limited to occasions when a failure occurs that automatically triggers a rollback. When using autocommit, it is important to remember that while you cannot explicitly roll back a transaction, the system is able to roll back a failed command.

For example, under autocommit, if you issue an UPDATE command from within the SQL Wizard, the command is executed and immediately committed if successful. If you issue a ROLLBACK command at this point, it will not roll back the completed UPDATE command; on the other hand, other programs do not wait for you to either explicitly commit the update command or exit from the SQL Wizard before they can acquire locks on the records you just updated.

If a system failure occurs during the UPDATE command (for example, the power goes off), the partially completed update command will be automatically rolled back when the system restarts. If the server discovers that an application node has failed, the server automatically rolls back any incomplete transaction belonging to the failed node.

When working at the LC (Lock Current) isolation level, autocommit should be turned on. The combination of the LC isolation level and autocommit are particularly useful in interactive processing. When a record is updated, inserted or deleted, the change is immediately committed, and all locks released.

Autocommit can be turned on or off through the Options Utility. Autocommit may also be turned on or off temporarily using the SET AUTOCOMMIT command.