START_TRANSACTION initiates a transaction. All the statements
that modify records within a transaction become permanent when
the transaction is completed, or none of them do. If you end
the transaction with the COMMIT statement, all the statements
within the transaction execute. If you end the transaction with a
ROLLBACK statement, none of the statements take effect.
To use the two-phase commit protocol for new application
programs that invoke only Oracle Rdb databases use the
DISTRIBUTED_TRANSACTION keyword in the START_TRANSACTION
statement. When you do this, Oracle Rdb invokes the DECdtm system
service calls for your application.
1 – Examples
The following statement starts a transaction with the following
characteristics:
o Default transaction (no user-specified transaction handle)
o READ_WRITE access
o CONSISTENCY mode
o WAIT option (by default)
o Will evaluate the constraint JOB_CODE_REQUIRED when the DML
statement is executed
o Will attempt to reserve the relation JOBS for SHARED_WRITE
access
1.1 – C Statement
START_TRANSACTION READ_WRITE ON pers USING (READ_WRITE CONSISTENCY
EVALUATING pers.JOB_CODE_REQUIRED AT VERB_TIME
RESERVING JOBS FOR SHARED WRITE);
1.2 – Pascal Statement
START_TRANSACTION READ_WRITE ON pers USING (READ_WRITE CONSISTENCY
EVALUATING pers.JOB_CODE_REQUIRED AT VERB_TIME
RESERVING JOBS FOR SHARED WRITE);
2 – Format
(B)0[m[4mSTART_TRANSACTION[m qwqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqwqk
mq> ( qq> [4mTRANSACTION_HANDLE[m var qq> ) qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqwqwqqqqqqq>qqqqqqqqwqk
mq> distributed-transaction-flag qj tq> tx-options qqu x
mq> on-clause qqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwqqqqqqqq>qqqqqqqwqq>
mqq> on-error qqqj
(B)0[mdistributed-transaction-flag =
q> [4mDISTRIBUTED_TRANSACTION[m qwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwq>
m> [4mDISTRIBUTED_TID[m distributed-tid qj
(B)0[mtx-options =
qqwq> [4mBATCH_UPDATE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
x x
mqqwqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqwqqqqqk x
tq> [4mREAD_ONLY[m qqqu tq> [4mWAIT[m qqqu tq> [4mCONCURRENCY[m qqu x x
mq> [4mREAD_WRITE[m qqqj mq> [4mNOWAIT[m qj mq> [4mCONSISTENCY[m qqj x x
x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x x
mqqqwqq>qqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqq>qqqqqk x
mqq> [4mEVALUATING[m qqwq> evaluating-clause qqqwj x x
mqqqqqqqq , <qqqqqqqqqqqj x x
x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x x
mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqq>qqqqqqqj
mqq> [4mRESERVING[m qqqwq> reserving-clause qqwqqj
mqqqqqqqqqqq , <qqqqqqqj
(B)0[mon-clause =
qwq> ON qwq> db-handle qwq> USING qqq> ( qwq> tx-options qwq> )qwq>
x x x mq> DEFAULTS qqqqqj x
x mqqqq , <qqqqqj x
x x
mqqqqqqqqqqqqqqqqqqqqqqqqq<qqq AND <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
(B)0[mevaluating-clause =
qqwqqqqqqqqqqqqqqq>qqqqqqqqwqq constraint qqk
mq> db-handle qq> . qq> qj x
x
lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqq> [4mAT[m qqqwqq> [4mVERB_TIME[m qqqwqqqq>
mqq> [4mCOMMIT_TIME[m qj
(B)0[mreserving-clause =
qqqwqqwqqqqqqqq>qqqqqqqqqqqqwqqq> relation-name qqqqqqqqqqqwqqk
x mq> db-handle qq> . <qj x x
x x x
mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqq> [4mFOR[m qqwq>qqqqqqqqqqqqqqqwqqqwqq> [4mREAD[m qqqwqqqqqqqqqqqqqqk
tq> [4mEXCLUSIVE[m qqqqu mqq> [4mWRITE[m qqj x
tq> [4mPROTECTED[m qqqqu x
mq> [4mSHARED[m qqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x
mqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqq>
tq> [4mWITH[m [4mAUTO_LOCKING[m qqqu
mq> [4mWITH[m [4mNOAUTO_LOCKING[m qj
2.1 – Format arguments
TRANSACTION_HANDLE The TRANSACTION_HANDLE keyword followed by
var a host language variable. A transaction
handle identifies a transaction. If
you do not supply a transaction handle
explicitly, RDML uses the default
transaction handle.
DISTRIBUTED_ You use this clause to specify the
TRANSACTION distributed transaction of which your
transaction will be a part.
DISTRIBUTED_TID A keyword followed by a host language
distributed-tid variable. You use the distributed-
tid variable to hold the value of the
distributed TID that DECdtm services
generate and return to the application.
DECdtm services use the TID to distinguish
the databases involved in a particular
distributed transaction.
tx-options Transaction options. Allow you to specify
the type of transaction you want, when you
want constraints to be evaluated and which
relations you intend to access.
on-clause Allows you to specify which database you
intend to access.
If you do not specify the ON clause, the
default Oracle Rdb behavior is to attempt
to start a transaction on all declared
databases (even if the RESERVING clause of
the START_TRANSACTION statement explicitly
specifies only tables in a particular
database).
DEFAULTS Allows you to specify that you want to
accept the default transaction options.
on-error The ON ERROR clause. Specifies
host language statement(s) to be
performed if an error occurs during the
START_TRANSACTION operation. For more
information see the entry on ON ERROR.
BATCH_UPDATE
READ_ONLY
READ_WRITE Declares what you intend to do with the
transaction as a whole. READ_ONLY is the
default.
WAIT
NOWAIT Specifies what your transaction will do
if it needs resources that are locked by
another transaction:
o WAIT is the default. It causes your
transaction to wait until the necessary
recourses are released or until Oracle Rdb
detects a deadlock.
o With NOWAIT, Oracle Rdb will return an
error if the resources you need are not
immediately available, thereby forcing
you to roll back your transaction.
CONCURRENCY
CONSISTENCY These options specify the consistency mode
of the transaction:
o CONCURRENCY is equivalent to SQL
ISOLATION LEVEL READ COMMITTED
(formerly called CONSISTENCY LEVEL
2).
o CONSISTENCY is the default. Consistency
guarantees that when all transactions
complete by committing or rolling back,
the effect on the database is the
same as if all transactions were run
sequentially.
evaluating-clause Allows you to specify the point at which
the named constraint(s) are evaluated. If
you specify VERB_TIME, they are evaluated
when the data manipulation statement is
issued. If you specify COMMIT_TIME, they
are evaluated when the COMMIT statement
executes. The evaluating clause is allowed
syntactically, but is ignored, with
READ_ONLY transactions.
db-handle Database handle. A host variable used
to refer to a specific database you have
invoked. For more information see the
entry on the Database Handle clause.
constraint The name of an Oracle Rdb constraint.
VERB_TIME
COMMIT_TIME VERB_TIME states when the data
manipulation statement is issued. COMMIT_
TIME reflects when the COMMIT statement
executes. VERB_TIME is the default.
reserving-clause Allows you to specify the relations you
plan to use and attempts to lock those
relations for your access. If you use the
WITH AUTO_LOCKING option (the default),
constraints and triggers defined on the
reserved relations will be able to access
additional relations that do not appear
in the list of reserved relations. The
WITH_AUTOLOCKING option will not work
for other relations not referenced in the
reserving clause.
relation-name The name of a relation in the database.
EXCLUSIVE
PROTECTED
SHARED The Oracle Rdb share modes. The keyword
you choose determines what operations
you allow others to perform on the
relations you are reserving. For READ_ONLY
transactions, EXCLUSIVE and PROTECTED are
syntactically allowed, but are ignored.
SHARED is the default.
READ
WRITE Declares what you intend to do with the
relations you have reserved:
o READ reserves the specified relation(s)
for read-only access
o WRITE reserves the specified
relation(s) for read and write access
WITH AUTO_LOCKING
WITH NOAUTO_LOCKING Oracle Rdb automatically locks any relations
referenced within a transaction unless you
specify the optional WITH NOAUTO_LOCKING
clause. WITH AUTO_LOCKING is the default.