Initiates a group of statements that Oracle Rdb executes as a unit.
A transaction ends with a COMMIT or ROLLBACK statement. If you
end the transaction with the COMMIT statement, all the statements
within the transaction execute. If you end the transaction with
ROLLBACK, none of the statements takes effect.
If an application starts a distributed transaction by explicitly
calling the DEC/dtm system service SYS$START_TRANS, it must
complete the transaction by calling the DEC/dtm SYS$END_TRANS
or SYS$ABORT_TRANS system service.
Examples:
Read-only -- START_TRANSACTION READ_ONLY
Read/write -- START_TRANSACTION READ_WRITE RESERVING -
JOB_HISTORY FOR PROTECTED WRITE, -
SALARY_HISTORY FOR EXCLUSIVE WRITE -
WAIT -
EVALUATING JOB_CODE_EXISTS AT VERB_TIME
Batch-update - START_TRANSACTION BATCH_UPDATE
1 – More
If you have invoked a database, you have the necessary privileges
to use the START_TRANSACTION statement.
If you issue a data manipulation statement without issuing START_
TRANSACTION first, Oracle Rdb automatically starts a transaction
for you, using the READ_ONLY option. Thus, you can perform simple
data retrieval without starting a transaction explicitly.
If you issue a data manipulation statement, such as GET or PRINT,
and then try to use START_TRANSACTION, you may get an error
message warning you that a transaction is already in progress.
To solve this problem, always issue an explicit START_TRANSACTION
statement when you plan to perform more than one operation.
You must have the DISTRIBTRAN database access right to a database
to run a two-phase commit transaction on that database.
2 – Multiple Database Access
You can use different share and access modes in the START_
TRANSACTION statement when you intend to access more than
one database in your application. To use this feature, you
must specify a database handle for each database you invoke.
For example, your application can access any relation in one
database using the read-only transaction to check certain data
values while it updates relations in another database using the
read/write transaction.
Note that any START_TRANSACTION statements already contained in
your application programs will continue to execute normally.
Because the read-only transaction uses a snapshot version of the
database, you might encounter inconsistencies in the data your
application retrieves. Therefore, if your program accesses one
database using read-only, another transaction using read/write
might update a relation in the database file itself. The data
your program reads from the snapshot file represents a before-
image of the record the other program is updating. If you require
absolute data consistency for certain update applications, you
should specify the read/write transaction mode for both databases
and permit others to read one of the databases by including the
SHARED READ reserving option. In this way, you maintain data
consistency during updates while permitting concurrent data
retrieval from the database your program reads.
Before you can use the multiple database feature of the START_
TRANSACTION statement, you must issue an INVOKE statement
for each database you intend to access. The INVOKE statement
must include a database handle. For example, the following
INVOKE statements identify two databases required by an update
application.
&RDB& INVOKE DATABASE DB1 = FILENAME 'PERSONAL$DISK:PERSONNEL'
&RDB& INVOKE DATABASE DB2 = FILENAME 'PERSONAL$DISK:BENEFITS'
Because the program only needs to read the EMPLOYEES relation of
the PERSONNEL database (DB1), but needs to change values in two
relations, TUITION and STATUS of the BENEFITS database (DB2),
the update program might contain the following START_TRANSACTION
statement:
&RDB& START_TRANSACTION
&RDB& ON DB1 USING
&RDB& (READ_ONLY
&RDB& RESERVING EMPLOYEES FOR SHARED READ) AND
&RDB& ON DB2 USING
&RDB& (READ_WRITE
&RDB& RESERVING TUITION FOR SHARED WRITE
&RDB& STATUS FOR SHARED WRITE)
If you refer to more than one Oracle Rdb database for actual update
operations within one transaction, use a distributed transaction.
For complete information on distributed transactions, see the
Oracle Rdb Guide to Distributed Transactions.
3 – 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
3.1 – TRANSACTION_HANDLE
A keyword followed by a host language variable. A transaction
handle identifies each instance of a transaction. If you do not
declare the transaction handle explicitly, Oracle Rdb attaches an
internal identifier to the transaction.
If you specify a transaction handle on a START_TRANSACTION
statement, you must also specify the transaction handle on any
COMMIT, FOR, ROLLBACK, START_STREAM, and STORE statements that
relate to the transaction.
You can specify this clause only once within a single START_
TRANSACTION statement.
In Callable RDO, use !VAL as a marker for host language
variables.
Normally, you do not need to use this argument. The ability to
declare a transaction handle is provided for compatibility with
other database products and future releases of Oracle Rdb.
Do not use this argument in interactive RDO.
3.2 – distributed-transaction-flag
(B)0[mdistributed-transaction-flag =
q> [4mDISTRIBUTED_TRANSACTION[m qwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwq>
m> [4mDISTRIBUTED_TID[m distributed-tid qj
3.2.1 – DISTRIBUTED_TRANSACTION
A distributed transaction is a transaction that uses more than
one database handle. Examples of a distributed transaction
include:
o a transaction that attaches more than once to a single Oracle Rdb
database
o a transaction that attaches to two or more Oracle Rdb databases
o a transaction that attaches to more than one database
management system (an Oracle Rdb database and a Oracle CODASYL
DBMS database, for example)
Distributed transactions are managed by the DECdtm services.
DECdtm services assigns a unique distributed transaction
identifier (distributed TID) to each distributed transaction
when the distributed transaction is started.
Use the DISTRIBUTED_TRANSACTION keyword by itself (without the
DISTRIBUTED_TID distributed-tid clause) when you want to start a
distributed transaction.
For complete information on distributed transactions, see the
Oracle Rdb Guide to Distributed Transactions.
3.2.2 – DISTRIBUTED_TID
A keyword followed by a host language variable (distributed-tid)
in application programs. When you want the transaction you are
starting to join a distributed transaction, use this clause to
specify the distributed TID of the distributed transaction that
you want your transaction to join.
The distributed-tid is a host language variable. You use the
distributed-tid variable to hold the value of the distributed TID
that DECdtm services generates and returns to the application.
DECdtm services uses the distributed TID to distinguish the
databases involved in a particular distributed transaction.
Note that if you want to start a distributed transaction, you
should use only the DISTRIBUTED_TRANSACTION clause. However, if
you want your application to start a transaction that will join a
distributed transaction, then you must use both the DISTRIBUTED_
TRANSACTION clause and the DISTRIBUTED_TID distributed-tid
clause.
Your application must explicitly call the SYS$START_TRANS system
service and you must specify the DISTRIBUTED_TID distributed-
tid clause if you want the transaction you are starting to join
a distributed transaction. The distributed-tid variable is an
octaword (16 bytes) that you should declare and initialize to
zero at the beginning of your application.
Do not use the DISTRIBUTED_TID clause in interactive RDO. It is
valid only in RDBPRE and Callable RDO.
For complete information on distributed transactions, see the
Oracle Rdb Guide to Distributed Transactions.
3.3 – tx-options
(B)0[mtx-options =
qwqq> [4mBATCH_UPDATE[m qqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw>
mwqqqqqqqqqqqqqqqqqqwqwqqqqqq>qqqqqqwqwqqqqqqqqqqqqqqqqqqwqk x
tq> [4mREAD_ONLY[m qqu tq> [4mWAIT[m qqqqqu tqq> [4mCONCURRENCY[m qqu x x
mq> [4mREAD_WRITE[m qqj mq> [4mNOWAIT[m qqqj mqq> [4mCONSISTENCY[m qqj x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mwqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqwqk x
mq> [4mEVALUATING[m qwq> evaluating-clause qqwqj x x
mqqqqqqqq , <qqqqqqqqqqqj x x
lqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj x
mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwj
mq> [4mRESERVING[m qwq> reserving-clause qwqwqqqqqqqqqqq>qqqqqqqqqqqwj
mqqqqqqqq , <qqqqqqqqqj tq> [4mWITH[m [4mAUTO_LOCKING[m qqu
mq> [4mWITH[m [4mNOAUTO_LOCKING[m j
3.3.1 – BATCH_UPDATE
Before you begin a batch-update transaction in your programs, you
should create a backup copy of the database.
You can reduce overhead in large, initial load operations by
using the BATCH_UPDATE option. To speed update operations,
Oracle Rdb does not write to the process' recovery-unit journal
(RUJ) file in the batch-update transaction. Therefore, you
cannot explicitly roll back a batch-update transaction with a
ROLLBACK statement. If Oracle Rdb attempts to perform an automatic
rollback due to any error (for example, a constraint condition is
violated) that you do not trap in your program, the transaction
fails and your database is permanently corrupted (because no
RUJ file exists). You must then re-create the database from
the backup copy you created prior to starting the batch-update
transaction. After you have corrected the error condition, you
can restart the program from the beginning.
For critical information on the hazards of batch-update
transactions, read the START TRANSACTION statement's section
on batch-update transactions in the RDO reference manual.
3.3.2 – READ_ONLY
If you start a read-only transaction, you can retrieve a snapshot
of the database at the moment the transaction started. Other
users can update records in the relation you are using, but your
transaction retrieves the records as they existed at the time
the transaction started. Any changes that other users make and
commit during the transaction are invisible to you. A read-only
transaction lets you read data without incurring the overhead
of record locking. You cannot modify, store, or erase records or
execute data definition statements in a read-only transaction.
3.3.3 – READ_WRITE
Signals that you want to use the locking mechanisms of Oracle Rdb
to get consistency in data retrieval and update. Use a read/write
transaction mode when you need to:
o Store, modify, or erase data
o Retrieve data that is guaranteed to be correct at the moment
of retrieval
o Use Oracle Rdb data definition statements
3.3.4 – WAIT_NOWAIT
Determines what your transaction does when it encounters a locked
record:
o If you specify WAIT, the transaction waits for others to
complete and then proceeds. WAIT is the default.
o If you specify NOWAIT, your transaction returns an error
message when it encounters a locked record.
3.3.5 – CONSISTENCY_CONCURRENCY
The CONSISTENCY and CONCURRENCY options control the extent to
which the database protects the consistency of your data.
The broadening of isolation level support in Oracle Rdb V4.2 changed
the way that Oracle Rdb treats RDO, RDBPRE, and RDML applications
that use CONCURRENCY transactions. With Oracle Rdb V4.1 and earlier
databases, these applications ignored the CONCURRENCY setting by
running their transactions at the default CONSISTENCY setting.
With Oracle Rdb V4.2 and later databases, the transactions are run
at the CONCURRENCY setting specified.
Because Oracle Rdb V4.2 expanded the CONCURRENCY setting in the
START_TRANSACTION statement of RDO, your DBA should check those
RDO, RDBPRE, and RDML applications that specify the CONCURRENCY
keyword in START_TRANSACTION statements to ensure that the
applications return expected results at the reduced consistency
level defined by the CONCURRENCY setting.
Your RDO, RDBPRE, and RDML applications that explicitly specify
CONCURRENCY in START_TRANSACTION statements will now operate in
Oracle Rdb V4.2 and later versions at the equivalent (SQL)
ISOLATION LEVEL READ COMMITTED (formerly called CONSISTENCY
LEVEL 2) when attached to either non-Oracle Rdb databases
or Oracle Rdb V4.2 and later databases. Those RDO, RDBPRE, and
RDML applications that do not explicitly specify a consistency
level or explicitly specify CONSISTENCY (default) will not change
transaction behavior.
In pre-Oracle Rdb Version 4.2 releases, Oracle Rdb ran application
transactions at the CONCURRENCY setting against non-Oracle Rdb
databases only and ignored the CONCURRENCY setting when
attached to Oracle Rdb databases. Instead of running these latter
applications at CONSISTENCY LEVEL 2 (CONCURRENCY), as you might
expect, Oracle Rdb ran them at the default CONSISTENCY LEVEL 3.
For Oracle Rdb Version 4.2 and later versions, however, RDO, RDBPRE,
and RDML applications that use the CONCURRENCY (CONSISTENCY
LEVEL 2) setting in transactions attached to Oracle Rdb Version
4.2 and later databases will no longer automatically revert
to CONSISTENCY LEVEL 3 as was true in Oracle Rdb Version 4.1
and earlier releases. Instead, they will run at the specified
CONCURRENCY (CONSISTENCY LEVEL 2) setting.
Refer to the "Differences in Relational Terminology" table in the
"Terminology" subtopic of the DECRDB topic in the DCL help file
for the relationship between SQL isolation level terminology and
its equivalent RDO, RDBPRE, and RDML terminology.
You can use the Oracle Rdb RDMS$DEBUG_FLAGS logical "T" option to
determine the consistency level at which your executable images
are running. The "T" option displays application transaction
characteristics and will display TPB$K_DEGREE2 for those
applications that use the CONCURRENCY option.
Because some 4GL's also use CONCURRENCY, you should contact
your 4GL vendor about possible changes in transaction behavior;
however, Rdb expects that in most cases the CONCURRENCY
option reflects how your DBA wants transactions to run and also
that your DBA understands that the reduced isolation level should
not be detrimental to applications.
In other database systems that you might want to access using
the remote feature of Oracle Rdb, this option specifies the degree
to which you want to control the consistency of the database. In
such systems, the CONCURRENCY option sacrifices some consistency
protection for improved performance with many users.
If you use the CONCURRENCY option, you may be able to transport
your programs to another system that takes advantage of that option
and achieve improved performance.
The default for Oracle Rdb is CONSISTENCY. When CONSISTENCY is
specified, Oracle Rdb always guarantees degree 3 consistency. Degree
3 consistency means that the database system guarantees that
data you have read will not be changed by another user before you
issue a COMMIT statement.
3.3.6 – evaluating-clause
(B)0[mevaluating-clause = qwqqqqqqqqqqqqqqqqwqq> constraint qq> [4mAT[m qqwq> [4mVERB_TIME[m qqqwq> mq> db-handle . qj mq> [4mCOMMIT_TIME[m qj
3.3.6.1 – Options
VERB_TIME When the data manipulation statement is issued
(default)
COMMIT_TIME When the COMMIT statement executes
db-handle A database handle (for multiple databases)
3.3.7 – reserving-clause
(B)0[mreserving-clause =
qqwqwqqqqqqqq>qqqqqqqqqqqqwqwq> relation-name qqwqwqqqk
x mq> db-handle qq> . qqj mq> view-name qqqqqqj x x
mqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mq> [4mFOR[m qqqwqqqqqqqq>qqqqqqqqqwqqqqwqq> [4mREAD[m qqqwqqq>
tqq> [4mEXCLUSIVE[m qqqqu mqq> [4mWRITE[m qqj
tqq> [4mPROTECTED[m qqqqu
mqq> [4mSHARED[m qqqqqqqj
3.3.7.1 – More
The list of relations to be locked during the transaction.
In general, include all the relations your transaction will
access. If you specify the AUTO LOCKING clause, 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.
Note that if you use the RESERVING clause without the AUTO
LOCKING clause, you can access only those relations that you
have explicitly reserved.
3.3.7.2 – Options
Option Access Constraints
SHARED Other users can work with the same relations
as you. Depending on the option they choose,
they can have read-only or read and write
access to the relations.
PROTECTED Other users can read the relations you are
using. They cannot have WRITE access.
EXCLUSIVE Other users cannot read records from the
relations included in your transaction. If
another user refers to the same relation in
a START_TRANSACTION statement, Oracle Rdb denies
access to that user.
READ You will only read data from the relations.
WRITE You will store, modify, or erase data in
the relations.
3.3.8 – AUTO_LOCKING
An optional clause that can be specified with the RESERVING
clause. When you specify the WITH AUTO_LOCKING clause, Oracle Rdb
automatically locks any relations referenced from constraints and
triggers defined on the reserved relations when the referenced
relations are accessed from the constraints or triggers. If one
of these referenced relations is also a reserved relation, the
explicitly specified lock mode must not conflict with the lock
mode required by the constraint or trigger that references the
relation.
The default is WITH AUTO_LOCKING. Specify the WITH NOAUTO_LOCKING
clause if you do not want to use the auto-locking option.
3.4 – on-clause
(B)0[mon-clause = qwq> [4mON[m qwq> db-handle qwq> [4mUSING[m q> ( qwq> tx-options qwq> ) qwq> x mqqqqq , <qqqqqj mq> [4mDEFAULTS[m qqqj x mqqqqqqqqqqqqqqqqqqqqqqqqq [4mAND[m <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
3.4.1 – db-handle
A host language variable used to refer to the database.
3.4.2 – tx-options
For more information on transaction options, select the subtopic
tx-options of the START_TRANSACTION Format subtopic.
3.5 – on-error
The ON ERROR clause. Specifies host language statement(s) to
be performed if an error occurs during the START_TRANSACTION
operation.
4 – Examples
Example 1
Ready a relation with defaults:
START_TRANSACTION READ_WRITE
This statement allows access to all the relations in the current
database so that all users can modify records. It is equivalent
to readying all the relations for the SHARED WRITE reserving
option.
Example 2
Start a read-only transaction:
START_TRANSACTION READ_ONLY
This statement lets you read data from the database but not store
or modify data. When you retrieve data, you see the state of
the records as they existed at the time of the START_TRANSACTION
statement. You do not see any updates to the database made after
that time.
Example 3
Start a transaction with locked relations:
The following statement lets you specify the intended action for
each relation in the transaction.
START_TRANSACTION READ_WRITE RESERVING
EMPLOYEES FOR PROTECTED WRITE,
JOBS, SALARY_HISTORY FOR SHARED READ
Assume that this transaction updates the EMPLOYEES relation based
on values found in two other relations, JOBS and SALARY_HISTORY:
o The transaction must update EMPLOYEES, so EMPLOYEES is readied
for the PROTECTED WRITE option.
o The program will only read values from JOBS and SALARY_
HISTORY, so there is no need for PROTECTED or WRITE access.
However, you do intend to update records in the transaction,
so the read-only transaction mode is not appropriate.
Example 4
In the following example, the user [SQL,HALSTON] receives the
READ, WRITE, MODIFY, and ERASE rights. HALSTON also gets the
DISTRIBTRAN right, which allows HALSTON to run the two-phase
commit protocol on the current database.
RDO> DEFINE PROTECTION FOR DATABASE
cont> POSITION 2
cont> IDENTIFIER [SQL,HALSTON]
cont> ACCESS "READ+WRITE+MODIFY+ERASE+DISTRIBTRAN".
RDO>
RDO> SHOW PROTECTION FOR DATABASE
(IDENTIFIER=[SQL,RICK],ACCESS=READ+WRITE+MODIFY+ERASE+SHOW+DEFINE+
CHANGE+DELETE+CONTROL+OPERATOR+ADMINISTRATOR+REFERENCES+SECURITY+
DISTRIBTRAN)
(IDENTIFIER=[SQL,HALSTON],ACCESS=READ+WRITE+MODIFY+ERASE+DISTRIBTRAN)
(IDENTIFIER=[*,*],ACCESS=NONE)
Example 5
The following START_TRANSACTION statement starts a read/write
transaction reserving the EMPLOYEES table for shared read. Note
that the SHOW TRANSACTION statement shows that fast commit is
enabled for the database.
RDO> START_TRANSACTION READ_WRITE RESERVING
cont> EMPLOYEES FOR SHARED READ
RDO> SHOW TRANSACTION
All Transactions in Database with filename MF_PERSONNEL
a read-write transaction is in progress
- updates have not been performed
- fast commit is enabled
- transaction sequence number (TSN) is 121
- snapshot space for TSNs less than 121 can be reclaimed
- session ID number is 42