What is a Database Transaction

Database transaction is a set of changes in the database, which must be completed together. In simpler words, it is a command or a block of commands (i.e. INSERT, UPDATE, etc), which are applied together by all-or-nothing principle.

Another important purpose of transactions is to ensure independence of the changes from other transactions (transaction isolation) in the concurrent database environment, i.e. when different users or applications work with the database in the same time.

Commit operation makes the database changes made by transaction permanent, and Rollback operation cancels the changes.

Notes

Transactions can work differently for different database types. For example, there are databases, where only data manipulation operations are used in transactions, and other operations (structure changes, privileges control etc) are executed out of transaction context. Some databases do not support transactions at all. See your database documentation for details.

Working with Transactions

In Database Tour, transaction control depends on Autocommit option:

If Autocommit is off (default), the application starts the transaction implicitly whenever needed. When transaction started, the Commit Transaction and Rollback Transaction buttons are enabled, and you must commit or rollback manually.

If Autocommit is on, every database command is committed automatically, and it is impossible to rollback. But you can control transactions manually using Database | Transactions menu or Begin Transaction, Commit Transaction, and Rollback Transaction buttons. If you start a transaction manually, Autocommit is off and you must commit or rollback manually. After commit or rollback, the Autocommit is on again.

Notes

Transaction isolation level

Transaction isolation level specifies the visibility of changes, if more than one client operates the database:

The following options are applied to ADO connections only (in addition to the previous ones):

You can select an isolation level through Database | Transaction Isolation menu.

Not all levels are allowed for all database types: for example, you may use only the Dirty Read level for local BDE connections (DB, DBF, FoxPro).