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:
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 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.