Using Transactions

By default, when you run an SQL statement (such as an Update or Delete), the database is immediately changed.

Another option is to run your SQL statement as a Transaction. When this is done:

• your changes are applied to the database but not committed, eg. are not yet permanent.
• once you have decided that your changes to the databases are what you want, you can Commit your changes. Your changes to the database will then become permanent.
• if you decide that your changes to the database are not what you want, you can Rollback your changes. The changes to the database are then undone.

How to use Transaction Mode

Within both the Run SQL and Row Update windows, you have the option of starting a transaction. This is done with the menu item Transaction > Begin Trans:

• once you have done this, you will get a Transaction Mode warning in the status-bar at the bottom right.
• every time you run a query, you will get a warning message that you have some uncommitted changes. Note that this message can be switched off with Options > Run SQL > Prompt when in transaction mode.
• you can Commit your changes with Transaction > Commit Trans
• you can Rollback your changes with Transaction > Rollback Trans
• you can also commit or rollback your changes by running the SQL statements Commit or Rollback in the Run SQL window. These commands can be included as part of an SQL script.

Once you have Committed or Rolled back, your transaction is terminated (except when AutoCommit is de-selected - see the next section).

Auto-Commit

By default, every update you make to the database is applied and committed to the database immediately. This is sometimes referred to as Auto-Commit mode.

You can switch off Auto-Commit mode by de-selecting Options > Technical Parameters > Auto Commit. When auto-commit is switched off:

• AQT will start a transaction as soon as you connect to a database
• your SQL will only be committed or rolled-back once you explicitly click on the Commit Trans or Rollback Trans items
• once you click on Commit Trans or Rollback Trans, a new transaction will be started. Your AQT session will remain in a transaction until Auto Commit is de-selected.

If you change the Auto Commit option when you are signed onto a database, nothing immediately happens:

• if you are currently in a transaction, the transaction will not be terminated until you commit or rollback
• if you are not currenty in a transaction, one will not be started until you click on Begin Trans

If Auto Commit of off, Safe Update Mode will be disabled.

You must Commit or Rollback

Once you have made some changes, a message at the bottom of the screen will warn you of this. It is highly recommended that you Commit or Rollback promptly and do not leave uncommitted changes for any length of time.

• if you are using the option Disconnect if Idle, the Disconnect may fail if there are uncommitted changes. If so, you will remain connected to the database.

  

• if AQT crashes, or looses communication with the server, the results can be unpredictable. See AQT Closes without Committing Changes below.
• before you close a database, or shut down AQT, you will be prompted to Commit your changes.
  • It is highly recommended that you click Yes to Commit.
  • Only click No if Commit fails. This might be the case, for instance, if your machine has lost contact with the database due to communication or other problem.
  • Clicking No will terminate AQT with either Committing or Rolling Back the changes. Only use this if all other options do not work. See the next section.

AQT Closes without Committing Changes

This can happen if:

• you have uncommitted changes and AQT either crashes or looses communication with the server
• when disconnecting from the database, you select the No option.

In this case, most databases will automatically rollback the outstanding changes. You will have lost any work you have done.

In some circumstances, the AQT process can remain in a suspended state on Windows if is has not terminated cleanly. In this case, it will continue to hold locks and may interfere with other tasks on the database. This may require a DBA to terminate the task on the server.

Other Notes on Transactions

• if you are doing a large or critical change to your database, it is a good idea to run the change as a transaction. The ability to undo the change can be very useful should the change not do what you want!!
• once you have run your SQL, you do not need to decide immediately whether to commit or rollback. You can display the table and run queries to determine whether your change has had the desired effect. Once you have determined this, you can choose whether to commit or rollback.

  Note that you should display the table using the same AQT session and connection as the one that made the change. If you display the data using a different AQT connection you may or may not see the unchanged version of the data.

• while your transaction is active, the rows you have changed in the database are locked and cannot be updated by other users. In some circumstances, other users will not even be able to display the data. You should not leave your transaction active for any longer than you need to.

  If your transaction is active, and your PC or connection to the database fails, your changes will be rolled back. Once you have determined that your changes are OK, you should Commit immediately!

• there are number of limitations of transactional control
  • some databases do not have the ability to rollback DDL statements (Create, Drop, Alter etc). These changes will be run outside of transactional control.
  • some statements (for instance Oracle Truncate) cannot be rolled back.