Specifies the characteristics for a default transaction. 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 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.
The characteristics specified in a DECLARE TRANSACTION statement
affect all transactions (except those started by the SET
TRANSACTION or START TRANSACTION statement) until you issue
another DECLARE TRANSACTION statement. The characteristics
specified in a SET TRANSACTION or START TRANSACTION statement
affect only that transaction.
A DECLARE TRANSACTION statement does not start a transaction.
The declarations made in a DECLARE TRANSACTION statement do not
take effect until SQL starts a new transaction. SQL starts a new
transaction with the first executable data manipulation or data
definition statement following a DECLARE TRANSACTION, COMMIT,
or ROLLBACK statement. In the latter case (following a COMMIT or
ROLLBACK statement), SQL applies the transaction characteristics
you declared for the transaction that just ended to the next one
you start.
In addition to the DECLARE TRANSACTION statement, you can specify
the characteristics of a transaction in one of two ways:
o If you specify the SET TRANSACTION or START TRANSACTION
statement, the declarations in the statement take effect
immediately and SQL starts a new transaction.
o You can retrieve and update data without declaring or
setting a transaction explicitly. If you omit the DECLARE
TRANSACTION, SET TRANSACTION or START 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.
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 or START TRANSACTION statement.
You can specify many options with the DECLARE TRANSACTION
statement, including:
o A transaction mode (READ ONLY/READ WRITE/BATCH UPDATE)
o A lock specification clause (RESERVING options)
o A wait mode (WAIT/NOWAIT)
o An isolation level
o A constraint evaluation specification clause
o Multiple sets of all the preceding options for each database
involved in the transaction (ON clause)
1 – Environment
You can use the DECLARE TRANSACTION statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o In a context file
o As part of the DECLARE section in an SQL module
o As part of the module header in a CREATE MODULE statement
o In dynamic SQL as a statement to be dynamically executed
In host language programs, you can have only a single DECLARE
TRANSACTION statement in each separately compiled source file.
See the Oracle Rdb SQL Reference Manual for more information.
The DECLARE TRANSACTION statement is an extension to standard
SQL syntax. If your program must adhere to standard SQL syntax,
you can isolate a DECLARE TRANSACTION statement by putting it in
a context file. For more information on context files, see the
Oracle Rdb Guide to SQL Programming.
2 – Format
DECLARE TRANSACTION --+---------------+->
+-> tx-options -+
+-> db-txns ----+
tx-options =
--+-+----------------------------------------+-+->
| +-> NAME 'quoted-string' ----------------+ |
| +-> EVALUATING -+- evaluating-clause --+-+ |
| | +-------> , <----------+ | |
| +-> RESERVING --+-> reserving-clause --+-+ |
| | +-------- , <----------+ | |
| +-> isolation-level ---------------------+ |
| +-> transaction-mode --------------------+ |
| +-> wait-option -------------------------+ |
+----------------+-------+-------------------+
+-- , <-+
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 ----+
isolation-level =
---> ISOLATION LEVEL --+-> READ COMMITTED ----+-->
+-> REPEATABLE READ ---+
+-> SERIALIZABLE ------+
transaction-mode =
--+-> READ ONLY ----+->
+-> READ WRITE ---+
+-> BATCH UPDATE -+
wait-option =
--+-> WAIT -+---------------------+-+->
| +-> <timeout-value> --+ |
+-> NOWAIT -----------------------+
db-txns =
--+--------------------------------------------------------------+-->
++-> ON -+-> <alias> -+-> USING --> ( +-> tx-options --+-> ) -++
| +---- , <----+ +-> DEFAULTS ----+ |
+-------------------------- AND <----------------------------+
3 – Arguments
The DECLARE TRANSACTION arguments are the same as the arguments
for the SET TRANSACTION statement. See the SET_TRANSACTION
statement for more information about the arguments for both
statements.
4 – Defaults
The DECLARE TRANSACTION defaults are the same as the defaults for
the SET TRANSACTION statement. See the SET_TRANSACTION statement
for complete information.
In general, you should not rely on default transaction
characteristics. Use explicit DECLARE TRANSACTION statements,
specifying read/write, read-only, or batch-update options;
a list of tables in the RESERVING clause; and a share mode
and lock type for each table. The more specific you are in a
DECLARE TRANSACTION statement, the more efficient your database
operations will be.
When a transaction starts using characteristics specified in a
DECLARE TRANSACTION statement, any transaction characteristics
unspecified in the DECLARE TRANSACTION statement take the SQL
defaults. This is true even if the characteristics unspecified in
DECLARE TRANSACTION were specified in an earlier SET or DECLARE
TRANSACTION statement.
5 – Examples
Example 1: Illustrating DECLARE and SET TRANSACTION differences
In the following example, the first executable statement
following the DECLARE TRANSACTION statement starts a transaction.
In contrast, the subsequent SET TRANSACTION statement itself
starts a transaction.
SQL> DECLARE TRANSACTION READ WRITE NOWAIT;
SQL> --
SQL> -- Notice the "no transaction is in progress" message:
SQL> --
SQL> SHOW TRANSACTION
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
no transaction is in progress
- session ID number is 80
SQL> --
SQL> -- The first executable statement following the
SQL> -- DECLARE TRANSACTION statement starts the transaction.
SQL> -- In this case, SELECT is the first executable statement.
SQL> --
SQL> SELECT LAST_NAME FROM CURRENT_SALARY;
LAST_NAME
Toliver
Smith
Dietrich
.
.
.
SQL> --
SQL> -- Note the transaction inherits the read/write characteristics
SQL> -- specified in the DECLARE TRANSACTION statement:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
a read-write transaction is in progress
- updates have not been performed
- transaction sequence number (TSN) is 416
- snapshot space for TSNs less than 416 can be reclaimed
- session ID number is 80
SQL> --
SQL> ROLLBACK;
SQL> --
SQL> -- Again, notice the "no transaction is in progress" message:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
no transaction is in progress
- transaction sequence number (TSN) 416 is reserved
- snapshot space for TSNs less than 416 can be reclaimed
- session ID number is 80
SQL> --
SQL> -- Unlike DECLARE TRANSACTION, the SET TRANSACTION statement
SQL> -- immediately starts a transaction:
SQL> --
SQL> SET TRANSACTION READ ONLY WAIT;
SQL> --
SQL> -- Note the transaction characteristics show the
SQL> -- read-only characteristics:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Wait
Read only
Transaction information returned by base system:
a snapshot transaction is in progress
- all transaction sequence numbers (TSNs) less than 416 are visible
- TSN 416 is invisible
- all TSNs greater than or equal to 417 are invisible
- session ID number is 80
Example 2: Using a DECLARE TRANSACTION statement in a context
file
The following example shows a context file, test_declares.sql,
that contains declarations for precompiling source file test.sco:
DECLARE ALIAS FOR FILENAME personnel;
DECLARE TRANSACTION READ WRITE
RESERVING EMPLOYEES FOR PROTECTED WRITE,
JOB_HISTORY FOR PROTECTED WRITE,
DEPARTMENTS FOR SHARED READ,
JOBS FOR SHARED READ;
The section in the Oracle Rdb Guide to SQL Programming about
program transportability explains when you may need an SQL
context file to support a program that includes SQL statements.
Example 3: Explicitly setting the isolation level in a DECLARE
TRANSACTION statement
In this example, you declare the default characteristics for
a read/write transaction, which includes changing the default
ISOLATION LEVEL SERIALIZABLE to ISOLATION LEVEL REPEATABLE READ.
SQL> DECLARE TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;
Example 4: Reserving a Partition
SQL> -- Determine the ordinal position of the EMPLOYEES
SQL> -- partitions.
SQL> SELECT RDB$MAP_NAME, RDB$AREA_NAME, RDB$ORDINAL_POSITION
cont> FROM RDB$STORAGE_MAP_AREAS
cont> WHERE RDB$MAP_NAME='EMPLOYEES_MAP';
RDB$MAP_NAME RDB$AREA_NAME
RDB$ORDINAL_POSITION
EMPLOYEES_MAP EMPIDS_LOW
1
EMPLOYEES_MAP EMPIDS_MID
2
EMPLOYEES_MAP EMPIDS_OVER
3
3 rows selected
SQL> --
SQL> -- Reserve EMPIDS_MID and EMPIDS_OVER for
SQL> -- exclusive write.
SQL> --
SQL> DECLARE TRANSACTION
cont> RESERVING EMPLOYEES PARTITION (2,3)
cont> FOR EXCLUSIVE WRITE;