Transaction (in its simplified meaning) is a set of data changes, which must be completed together. Commit makes the data changes, made in the transaction, permanent, and Rollback cancels the data changes.
In Database Tour, transaction control depends on Autocommit option:
If Autocommit is on (default), 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.
If Autocommit is off, 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.
Notes
1. Transactions may work differently for different database types. See your server documentation for details.
2. 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.
3. 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).
4. In any floating windows and in DDL operations, initiated through the application GUI (i.e. not from SQL Editor), Autocommit is used.
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).