Transactions

A transaction is a logical unit of work that comprises one or more SQL statements executed by a single user. A typical example is transferring money from one bank account to another. Two operations take place:

1. Money is taken out of one account.
2. Money is put into the other account.

These operations need to be performed together. If one were to be done and the other not done (for example, if the network connection went down), the bank's books would not balance correctly.

Normally, when you execute an Update method on a dynaset, the changes are committed to the database immediately. Each operation is treated as a distinct transaction. Using the BeginTransaction, Commit, and Rollback transactional control methods of the OSession object allow operations to be grouped into larger transactions. BeginTransaction tells the session that you are starting a group of operations. Commit makes the entire group of operations permanent. Rollback cancels the entire group. Commit and Rollback end the transaction and the program returns to normal operation: one transaction for each operation. Experienced Oracle users should note the following differences between the operation of Oracle Objects for OLE and many Oracle tools:

• Oracle tools such as SQL*Plus execute as if the BeginTransaction method was called when the tool was started. This means that updates are not committed immediately, but are held until a commit or rollback is executed.
• SQL*Plus always starts a new transaction every time a commit or rollback is executed.
• The autocommit setting in SQL*Plus results in behavior similar to the default of the Oracle Objects for OLE.

If you are connected to more than one database and use the transaction methods, you should understand that Oracle Objects for OLE commits each database separately. This is not the same as the two-phase commit that Oracle provides. If your application needs to guarantee data integrity across databases, you should connect to a single database and then access additional databases through the Oracle database link feature. This method gives you the benefit of Oracle's two-phase commit. Consult your Oracle documentation for more information about two-phase commit, database links and distributed transactions.

Transactions apply only to the Data Manipulation Language (DML) portion of the SQL language (such as INSERT, UPDATE, and DELETE). Transactions do not apply to the Data Control Language (DCL) or Data Definition Language (DDL) portions (such as CREATE, DROP, ALTER, and so on) of the SQL language. DCL and DDL commands always force a commit, which in turn commits everything done before them.