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
- The application tries, whenever possible, to create a separate session for each SQL and Table window you open inside a database, and a separate transaction for each database session. This behavior depends on the database type and used database interface. For example, for local BDE databases, all child windows are associated with one transaction. For Interbase and most of FD connections, each window is associated with a separate session and a separate transaction.
- The transaction control buttons and menu items are common for all child windows (database sessions) even if the windows are associated with different transactions. Each time you switch to another SQL or Table window, these buttons and menu items will reflect the current state of the corresponding transaction. For example, when the buttons are enabled, then the transaction of the current window (database session) is active (i.e. not committed or rolled back yet).
- In any floating windows and in DDL operations, initiated through the application GUI (i.e. not from SQL Editor), Autocommit is used, and the corresponding warning is shown.
Transaction isolation level
Transaction isolation level specifies the visibility of changes, if more than one client operates the database:
- Dirty Read - All changes (even not committed) are visible inside the current transaction;
- Read Committed - Only committed changes are visible inside the current transaction;
- Repeatable Read - Only changes, which were done before starting the current transaction, are visible inside the current transaction.
The following options are applied to ADO connections only (in addition to the previous ones):
- Chaos - Changes from more highly isolated transactions cannot be overwritten by the current connection;
- Browse - Uncommitted changes in other transactions are visible;
- Cursor Stability - only committed changes are visible inside the current transaction;
- Serializable, Isolated - Transactions conducted in isolation from other transactions;
- Unspecified - Server is using an isolation level other than what was requested and the specific isolation level cannot be determined.
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).