Starts a transaction and specifies its characteristics. A
transaction is a group of statements whose changes can be made
permanent or undone only as a unit.
A transaction ends with a COMMIT or ROLLBACK statement. If you
end the transaction with the COMMIT statement, all the changes
made to the database by the statements are made permanent. If you
end the transaction with the ROLLBACK statement, the statements
do not take effect.
You must end the transaction with a COMMIT or ROLLBACK statement
before starting or declaring another transaction. If you try to
start or declare a transaction while another one is active, SQL
generates an error message.
Besides the SET TRANSACTION statement, you can specify the
characteristics of a transaction in one of two other ways:
o If you specify the DECLARE TRANSACTION statement, the
declarations in the statement take effect when SQL starts a
new transaction that is not started by the SET TRANSACTION
statement. SQL starts a new transaction with the first
executable data manipulation or data definition statement
following the DECLARE TRANSACTION, COMMIT, or ROLLBACK
statement.
o If you omit both the DECLARE and SET TRANSACTION statements,
SQL automatically starts a transaction (using the read/write
option) with the first executable data manipulation or data
definition statement following a COMMIT or ROLLBACK statement.
Thus, you can retrieve and update data without declaring or
setting a transaction explicitly.
See the Oracle Rdb SQL Reference Manual for examples of when you
would want to use the DECLARE TRANSACTION statement instead of
the SET TRANSACTION statement.
You can specify many options with the SET TRANSACTION statement,
including:
o Transaction mode (READ ONLY/READ WRITE)
o Lock specification clause (RESERVING options)
o Horizontal partition specification (RESERVING options)
o Wait mode (WAIT/NOWAIT)
o Isolation level
o Constraint evaluation specification clause
o Multiple sets of all the preceding options for each database
involved in the transaction (ON . . . AND ON)
The Arguments section explains these options in more detail.
1 – Environment
You can use the SET TRANSACTION statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
SET TRANSACTION --+---------------+->
+-> tx-options -+
+-> db-txns ----+
tx-options =
--+-+----------------------------------------+-+->
| +-> NAME 'quoted-string' ----------------+ |
| +-> EVALUATING -+- evaluating-clause --+-+ |
| | +-------> , <----------+ | |
| +-> RESERVING --+-> reserving-clause --+-+ |
| | +-------- , <----------+ | |
| +-> isolation-level ---------------------+ |
| +-> transaction-mode --------------------+ |
| +-> wait-option -------------------------+ |
+----------------+-------+-------------------+
+-- , <-+
transaction-mode =
--+-> READ ONLY ----+->
+-> READ WRITE ---+
+-> BATCH UPDATE -+
wait-option =
--+-> WAIT -+---------------------+-+->
| +-> <timeout-value> --+ |
+-> NOWAIT -----------------------+
isolation-level =
---> ISOLATION LEVEL --+-> READ COMMITTED ----+-->
+-> REPEATABLE READ ---+
+-> SERIALIZABLE ------+
evaluating-clause =
-+-------------+-> <constraint-name> -> AT -+-> VERB TIME ---+-->
+-> <alias.> -+ +-> COMMIT TIME -+
reserving-clause =
-+-+-> <view-name> ------------------------------------------------+-+-+
| +-> <table-name> -+-------------------------------------------+-+ | |
| +-> PARTITION --> ( -+-> <part-num> -+-> ) -+ | |
| +------ , <-----+ | |
+----------------------------------- , <----------------------------+ |
+---------------------------------------------------------------------+
+-> FOR -+--------------+--+-> READ ------------+--------------------->
+-> EXCLUSIVE -+ +-> WRITE -----------+
+-> PROTECTED -+ +-> DATA DEFINITION -+
+-> SHARED ----+
db-txns =
--+--------------------------------------------------------------+-->
++-> ON -+-> <alias> -+-> USING --> ( +-> tx-options --+-> ) -++
| +---- , <----+ +-> DEFAULTS ----+ |
+-------------------------- AND <----------------------------+
3 – Arguments
3.1 – alias
Specifies the alias for a constraint.
3.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.3 – constraint-name
Specifies the name of a constraint.
3.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.
3.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.
3.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.
3.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.
3.8 – NAME transaction-name
Supplies a title for the transaction. This information is
displayed by the SET FLAGS TRANSACTION keyword.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
4 – Examples
Example 1: Starting a read-only transaction
SQL> SET TRANSACTION READ ONLY;
This statement lets you read data from the database but not
insert or update data. When you retrieve data, you see the
database records as they existed at the time SQL started the
transaction. You do not see any updates to the database made
after that time.
Example 2: Reserving specific tables with the SET TRANSACTION
statement
The following statement lets you specify the intended action for
each table in the transaction:
SQL> ATTACH 'FILENAME mf_personnel';
SQL> SET TRANSACTION READ WRITE RESERVING
cont> EMPLOYEES FOR PROTECTED WRITE,
cont> JOBS, SALARY_HISTORY FOR SHARED READ;
Assume that this transaction updates the EMPLOYEES table based on
values found in two other tables: JOBS and SALARY_HISTORY.
o The transaction must update the EMPLOYEES table, so EMPLOYEES
is readied for protected write access.
o The program will only read values from the JOBS and SALARY_
HISTORY tables, so there is no need for write access or
protected write access. However, you do intend to update
records in the transaction, so a read-only transaction is
not appropriate.
Example 3: Specifying multiple databases in a SET TRANSACTION
statement
You can access multiple databases from within the same
transaction. This example explains how you can benefit from this
feature.
Read-only transactions use a snapshot version of the data, and
therefore you might encounter older values in the data your
application retrieves. because another transaction using a
read/write transaction might be updating a table.
The snapshot file represents a before-image of the database
rows that the other program is updating. If you require The
very latest data, you should specify read/write access for both
databases, and permit other users to read one of the databases
by including the shared read mode. In this way, you maintain
data consistency during updates, while permitting concurrent data
retrieval from the database that your program reads.
However, any read/write transaction you set offers reduced
concurrent access when compared to read-only access. For that
reason, use read/write transactions only when necessary.
Before you can use the multiple database feature of the SET
TRANSACTION statement, you must issue a DECLARE ALIAS statement
that specifies each database you intend to access. The DECLARE
ALIAS statement must include an alias. For example, the following
DECLARE ALIAS statements identify two databases required by an
update application:
EXEC SQL
DECLARE DB1 ALIAS FOR FILENAME PERSONNEL;
END EXEC
EXEC SQL
DECLARE DB2 ALIAS FOR FILENAME benefits;
END EXEC
Because the program needs to only read the EMPLOYEES table of
the PERSONNEL database but needs to change values in two tables
(TUITION and STATUS) in the BENEFITS database, the update program
might contain the following SET TRANSACTION statement:
EXEC SQL SET TRANSACTION
ON DB1 USING ( READ ONLY
RESERVING DB1.EMPLOYEES FOR SHARED READ )
AND
ON DB2 USING ( READ WRITE
RESERVING DB2.TUITION FOR SHARED WRITE
DB2.STATUS FOR SHARED WRITE )
END EXEC
Example 4: Specifying a multischema database in a SET TRANSACTION
statement
If one of the databases you access is a multischema database,
you must specify it using a delimited identifier. The following
example shows how to access the single-schema personnel
database and the multischema corporate_data database. The table
EMPLOYEES is located within the schema PERSONNEL in the catalog
ADMINISTRATION within the CORPORATE_DATA database.
SQL> ATTACH 'ALIAS CORP FILENAME corporate_data';
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> SET QUOTING RULES 'SQL92';
SQL> SET CATALOG '"CORP.ADMINISTRATION"';
SQL> SET SCHEMA '"CORP.ADMINISTRATION".PERSONNEL';
SQL> --
SQL> SET TRANSACTION ON CORP USING (READ ONLY
cont> RESERVING "CORP.EMPLOYEES" FOR SHARED READ)
cont> AND ON PERS USING (READ WRITE RESERVING
cont> PERS.EMPLOYEES FOR SHARED WRITE);
Example 5: Specifying evaluation at verb time in a SET
TRANSACTION statement
The following example shows an insert into the DEGREES table of a
newly acquired degree for EMPLOYEE_ID 00164. The new degree, MME,
is evaluated and, because it is not one of the acceptable degree
codes, an error message is returned immediately.
SQL> ATTACH 'FILENAME personnel';
SQL> SET TRANSACTION READ WRITE
cont> EVALUATING DEGREES_FOREIGN1 AT VERB TIME,
cont> DEGREES_FOREIGN2 AT VERB TIME,
cont> DEG_DEGREE_VALUES AT VERB TIME
cont> RESERVING DEGREES FOR PROTECTED WRITE,
cont> COLLEGES, EMPLOYEES FOR SHARED READ;
SQL> SHOW TRANSACTION
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Read Write
Evaluating constraint DEGREES_FOREIGN1 at verb time
Evaluating constraint DEGREES_FOREIGN2 at verb time
Evaluating constraint DEG_DEGREE_VALUES at verb time
Reserving table DEGREES for protected write
Reserving table COLLEGES for shared read
Reserving table EMPLOYEES for shared read
Transaction information returned by base system:
a read-write transaction is in progress
- updates have not been performed
- transaction sequence number (TSN) is 153
- snapshot space for TSNs less than 153 can be reclaimed
- session ID number is 21
SQL> INSERT INTO DEGREES
cont> (EMPLOYEE_ID, COLLEGE_CODE, YEAR_GIVEN,
cont> DEGREE, DEGREE_FIELD)
cont> VALUES
cont> ('00164', 'PRDU', 1992,
cont> 'MME', 'Mech Enging');
%RDB-E-INTEG_FAIL, violation of constraint DEG_DEGREE_VALUES caused
operation to fail
-RDB-F-ON_DB, on database DISK1:[JONES.PERSONNEL]PERSONNEL.RDB;1
SQL> ROLLBACK;
Example 6: Explicitly setting isolation levels in a transaction
This statement lets you read data from and write data to the
database. It also sets the transaction to run at isolation
level READ COMMITTED, not at the higher default isolation level
SERIALIZABLE.
SQL> SET TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;
Example 7: Creating index concurrently
The following example shows how to reserve the table for shared
data definition and how to create an index:
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES FOR SHARED DATA DEFINITION;
SQL> --
SQL> CREATE INDEX EMP_LAST_NAME1 ON EMPLOYEES (LAST_NAME);
SQL> --
SQL> -- Commit the transaction immediately.
SQL> --
SQL> COMMIT;
Example 8: Reserving a Partition
SQL> -- This example locks only the second partition of
SQL> -- the EMPLOYEES table in exclusive write mode.
SQL> -- The advantage of this is that the process can insert,
SQL> -- update, or delete from this partition without writing
SQL> -- to the snapshot (.snp) file, and in general, uses fewer
SQL> -- resources for operations on the partition.
SQL> SET TRANSACTION READ WRITE
cont> RESERVING EMPLOYEES PARTITION (2) FOR EXCLUSIVE WRITE;
Example 9: Interaction between RESERVING clause and column
DEFAULT values
This example examines the interaction between the RESERVING
clause and DEFAULT values that reference tables (either directly
and indirectly). The RESERVING clause of SET TRANSACTION limits
the transaction to just those tables listed for the transaction.
Tables directly referenced by constraints, triggers, COMPUTED
BY columns, AUTOMATIC columns and DEFAULT values are implicitly
reserved for SHARED READ. However, if these definitions reference
the table indirectly via a stored function then that table is not
considered for automatic reservation.
This example uses DEFAULT value to contrast three different
mechanisms and their interactions with the RESERVING clause.
The same technique could be applied to other definitions such as
triggers and constraints.
The DEFAULT value is derived from a secondary table (DEFAULTS)
that holds one value for each valid user of the database. The
DEFAULT is retrieved based on the value of CURRENT_USER. In the
three tables below the value is either directly fetched (SAMPLE_
TABLE2), or via a stored function (SAMPLE_TABLE1, and SAMPLE_
TABLE3).
The SQL function GET_DEFAULT3 includes a LOCK TABLE statement to
ensure that the table is correctly reserved. Oracle recommends
this approach since it relieves the programmer from knowing which
tables might be required when coding a RESERVING clause for a
transaction.
SQL> set dialect 'sql99';
SQL>
SQL> create table DEFAULTS
cont> (user_id rdb$object_name primary key,
cont> valid_number integer);
SQL> insert into DEFAULTS values ('SMITH', 100);
1 row inserted
SQL>
SQL> create module UTL1
cont> function GET_DEFAULT1 ()
cont> returns integer
cont> not deterministic;
cont> return (select valid_number from DEFAULTS
cont> where user_id = CURRENT_USER);
cont> end module;
SQL>
SQL> create table SAMPLE_TABLE1
cont> (id integer identity,
cont> quantity integer
cont> default GET_DEFAULT1 ()
cont> );
SQL>
SQL> create table SAMPLE_TABLE2
cont> (id integer identity,
cont> quantity integer
cont> default (select valid_number from DEFAULTS
cont> where user_id = CURRENT_USER)
cont> );
SQL>
SQL> create module UTL3
cont> function GET_DEFAULT3 ()
cont> returns integer
cont> not deterministic;
cont> begin
cont> lock table DEFAULTS for shared read mode;
cont> return (select valid_number from DEFAULTS
cont> where user_id = CURRENT_USER);
cont> end;
cont> end module;
SQL>
SQL> create table SAMPLE_TABLE3
cont> (id integer identity,
cont> quantity integer
cont> default GET_DEFAULT3 ()
cont> );
SQL>
SQL> commit;
The following transactions succeed or fail as explained in the
example.
SQL> /*
***> Fails because the module references a table that is not reserved
***> */
SQL> set transaction read write
cont> reserving SAMPLE_TABLE1 for shared write;
SQL> insert into SAMPLE_TABLE1 default values;
%RDB-E-UNRES_REL, relation DEFAULTS in specified request is not a
relation reserved in specified transaction
SQL> rollback;
SQL>
SQL> /*
***> Succeeds because direct access to the table from the DEFAULT
***> is implicitly added to the reserving list as SHARED READ
***> */
SQL> set transaction read write
cont> reserving SAMPLE_TABLE2 for shared write;
SQL> insert into SAMPLE_TABLE2 default values;
1 row inserted
SQL> rollback;
SQL>
SQL> /*
***> Succeeds because the routine adds the table to the reserved
***> table list using LOCK TABLE.
***> */
SQL> set transaction read write
cont> reserving SAMPLE_TABLE3 for shared write;
SQL> insert into SAMPLE_TABLE3 default values;
1 row inserted
SQL> rollback;
SQL>