Transaction Control

See Also

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 BeginTrans, CommitTrans, and Rollback transactional control methods of the OraSession object allows operations to be grouped into larger transactions. BeginTrans tells the session that you are starting a group of operations. CommitTrans makes the entire group of operations permanent. Rollback cancels the entire group. CommitTrans and Rollback end the transaction and the program returns to normal operation: one transaction per 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 BeginTrans 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.

· SQL*Plus does not take a row lock in the case of a failed Update or Delete statement. However, in the case of OO4O, if Update or Delete methods fail on a given row in a dynaset in a global transaction (i.e. in cases in which you issued a BeginTrans), be aware that locks will remain on those rows on which you called Update or Delete. These locks will persist until you call CommitTrans or Rollback.

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 by way of 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, etc.) of the SQL language. DCL and DDL commands always force a commit, which in turn commits everything done before them.