1 – alias
Specifies the alias for a constraint.
2 – BATCH_UPDATE
Specifies the batch-update mode to reduce overhead in large-
load operations. To speed update operations, Oracle Rdb does not
write to snapshot or recovery-unit journal files in a batch-
update transaction. For more information about batch-update
transactions, see the Oracle Rdb Guide to SQL Programming and
the Oracle Rdb SQL Reference Manual.
CAUTION
Before you begin a batch-update transaction in your
programs, you should create a backup copy of the database
using the RMU Backup command. If an error occurs in your
program that would normally result in a rollback of the
transaction, Oracle Rdb marks the database as corrupt.
To recover from a corrupt database, you must create the
database again from the backup copy of the database. After
correcting the error condition, you can restart the program
from the beginning. You should back up the database after
completing a batch-update transaction as well.
3 – constraint-name
Specifies the name of a constraint.
4 – db-txns
Specifies different transaction options. When you attach to
more than one database and want to specify different transaction
options for each database, use this clause.
5 – evaluating-clause
Specifies the point at which the named constraint or constraints
are evaluated. If you specify VERB TIME, they are evaluated when
the data manipulation statement is issued. If you specify COMMIT
TIME, the constraint evaluation is based on the setting of the
SET ALL CONSTRAINTS statement. For read-only transactions, this
clause is allowed but is ignored.
6 – FOR share modes
Syntax options:
FOR EXCLUSIVE
FOR PROTECTED
FOR SHARED
Specifies the SQL share modes. The keyword you choose determines
which operations you allow others to perform on the tables you
are reserving. While you can specify an EXCLUSIVE or PROTECTED
share mode when declaring a read-only transaction, SQL ignores
these entries and specifies SHARED mode. The default is SHARED.
For more information, see the Oracle Rdb SQL Reference Manual.
7 – ISOLATION_LEVEL
Syntax options:
ISOLATION LEVEL READ COMMITTED
ISOLATION LEVEL REPEATABLE READ
ISOLATION LEVEL SERIALIZABLE
Defines the degree to which database operations in an SQL
transaction are affected by database operations in concurrently
executing transactions. It determines the extent to which the
database protects the consistency of your data.
Oracle Rdb supports isolation levels READ COMMITTED, REPEATABLE
READ, and SERIALIZABLE. When you use SQL with Oracle Rdb
databases, by default, SQL executes a transaction at isolation
level SERIALIZABLE. The higher the isolation level, the more
isolated a transaction is from other currently executing
transactions. Isolation levels determine the type of phenomena
that are allowed to occur during the execution of concurrent
transactions. Two phenomena define SQL isolation levels for a
transaction:
o Nonrepeatable read
Allows the return of different results within a single
transaction when an SQL operation reads the same row in
a table twice. Nonrepeatable reads can occur when another
transaction modifies and commits a change to the row between
transaction reads.
o Phantom
Allows the return of different results within a single
transaction when an SQL operation retrieves a range of data
values (or similar data existence check) twice. Phantoms
can occur if another transaction inserted a new record and
committed the insertion between executions of the range
retrieval.
Each isolation level differs in the phenomena it allows. The
following table shows the phenomena permitted for the isolation
levels that you can explicitly specify with the SET TRANSACTION
statement.
Table 1-8 Phenomena Permitted at Each Isolation Level
Nonrepeatable
Isolation Reads Phantoms
Level Allowed? Allowed?
READ Yes Yes
COMMITTED
REPEATABLE No Yes
READ
SERIALIZABLE No No
For read-only transactions, which always execute at isolation
level SERIALIZABLE if snapshots are enabled, the database system
guarantees that you will not see changes made by another user
before you issue a COMMIT statement.
See the Oracle Rdb Guide to SQL Programming for further
information about specifying isolation levels in transactions.
8 – NAME transaction-name
Supplies a title for the transaction. This information is
displayed by the SET FLAGS TRANSACTION keyword.
9 – ON
Syntax options:
ON alias | AND ON alias
Specifies the alias for a database for which you want to specify
transaction options. An alias is a name for a particular attach
to a database.
Use the ON clause when you attach to more than one database and
want to specify different transaction options for each database.
(If you omit the ON clause, the single set of transaction
options in the SET TRANSACTION statement applies to all attached
databases.)
You can include multiple sets of transaction options, one for
each database, in multiple ON clauses separated with the AND
keyword.
10 – PARTITION (part-num)
PARTITION When used with the RESERVING clause specifies a list
of numeric partition numbers so that only a subset of the tables
partitions are reserved. For example, an application could submit
several processing jobs that each reserved a separate partition
of the table for EXCLUSIVE access. The default, if this clause
is omitted, is to reserve all partitions. An error is reported if
the application references a partition if the table that was not
reserved.
11 – part-num
The numeric identifier for the partition. Partition are numbered
from 1. The CREATE INDEX statement allocates these values and
records them in the RDB$STORAGE_MAP_AREAS table in the column
RDB$ORDINAL_POSITION.
12 – reserved_lock_types
Syntax options:
READ
WRITE
DATA DEFINITION
Specifies the lock type. These keywords declare what you intend
to do with the tables you are reserving.
Use READ when you only want to read data from the tables. This is
the default for read-only transactions.
Use WRITE when you want to insert, update, or delete data in
the tables. This is the default for read/write transactions. You
cannot specify WRITE for read-only transactions.
Use DATA DEFINITION when you want to create or alter metadata at
the same time as other users on the same table. This clause can
be used only in read/write transactions.
13 – READ_ONLY
Retrieves a snapshot of the database at the moment the read-only
transaction starts. Other users can update rows in the table
you are using, but your transaction retrieves the rows as they
existed at the time the transaction started. You cannot update,
insert, or delete rows, or execute data definition statements
in a read-only transaction with the exception of declaring a
local temporary table or modifying data in a created or declared
temporary table. Read-only transactions are implicitly isolation
level serializable.
Because a read-only transaction uses the snapshot (.snp) version
of the database, any changes that other users make and commit
during the transaction are invisible to you. Using a read-only
transaction lets you read data without incurring the overhead of
row locking. (You do incur overhead for keeping a snapshot of the
tables you specify in the RESERVING clause, but this overhead is
less than that of a comparable read/write transaction.)
Because of the limited nature of read-only transactions, they are
subject to several restrictions.
14 – READ_WRITE
Signals that you want to use the lock mechanisms of SQL for
consistency in data retrieval and update. Read/write is the
default transaction. Use the read/write transaction mode when
you need to:
o Insert, update, or delete data
o Retrieve data that is guaranteed to be correct at the moment
of retrieval
o Use SQL data definition statements
When you are reading a row in a read/write transaction, no other
user can update that row. Under some circumstances, SQL may lock
rows that you are not explicitly reading.
o If your query is scanning a table without using an index, SQL
locks all the rows in the record stream to maintain isolation
level serializable.
o If your query uses indexes, SQL may lock part of an index,
which has the effect of locking several rows.
15 – RESERVING
Syntax options:
RESERVING table-name
RESERVING view-name
Lists the tables to be locked during the transaction. Include
all the persistent base tables your transaction will access. You
cannot reserve created or declared temporary tables.
If you use the RESERVING clause to specify tables, you can access
only the tables you have reserved. However, specifying a view in
a RESERVING clause is the same as specifying the base tables on
which the view is based.
16 – timeout-value
Specifies the number of seconds for a given transaction to wait
for other transactions to complete. This interval is only valid
for the transaction specified in the SET TRANSACTION statement.
Subsequent transactions return to the database default timeout
interval. A timeout value of 0 specifies NOWAIT.
When starting a transaction, there are three different values
that are used to determine the lock timeout interval for that
transaction. Those values are:
1. The value specified in the SET TRANSACTION statement
2. The value stored in the database as specified in CREATE or
ALTER DATABASE
3. The value of the logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL
The timeout interval for a transaction is the smaller of the
value specified in the SET TRANSACTION statement and the value
specified in CREATE DATABASE. However, if the logical name
RDM$BIND_LOCK_TIMEOUT_INTERVAL is defined, the value of this
logical name overrides the value specified in CREATE DATABASE.
17 – USING
Syntax options:
USING (tx-options)
USING DEFAULTS
Specifies the transaction options you want for the database
referred to by the alias in the preceding ON clause. You can
explicitly specify the transaction, wait mode, and isolation
level option, or you can use the DEFAULTS keyword. Using DEFAULTS
is equivalent to specifying READ WRITE WAIT. For more information
on DEFAULTS, see the Oracle Rdb SQL Reference Manual.
18 – WAIT
Syntax options:
WAIT |NOWAIT
Determines what your transaction does when it encounters a locked
row. The default is WAIT.
o If you specify WAIT, the transaction waits for other
transactions to complete and then proceeds. If you prefer,
you can specify that the transaction proceeds after a certain
time interval instead of waiting for other transactions to
complete. You can specify the timeout interval value after
the WAIT keyword. The timeout interval value is expressed in
seconds.
o If you specify NOWAIT, your transaction returns an error
message when it encounters a locked row.