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:
Money is taken out of one account.
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
·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.