SET TRANSACTION --+-------->------+-> ;
                   +-> tx-options -+
                   +-> db-txns ----+

Additional information available:

More InformationExamplestx optionsdb txns

 SET TRANSACTION is an executable statement that both starts a
 transaction and specifies its characteristics.  A transaction is
 a group of statements whose changes can be made permanent or
 undone as a unit.  The characteristics specified in a SET
 TRANSACTION statement affect all transactions until the
 transaction ends with a COMMIT or ROLLBACK statement.

 SET TRANSACTION differs from DECLARE TRANSACTION in two ways:

 1.  SET TRANSACTION is an executable statement, so the
     transaction characteristics it specifies take effect when
     you issue it.

 2.  The transaction characteristics specified in SET TRANSACTION
     last only until the next COMMIT or ROLLBACK statement.  The
     characteristics specified by a DECLARE TRANSACTION statement
     are valid until you enter another DECLARE TRANSACTION
     statement.  (A COMMIT or ROLLBACK statement followed by a
     SET TRANSACTION statement may start a transaction with
     different characteristics, but subsequent transactions
     started implicitly will have the characteristics specified
     in the last DECLARE TRANSACTION statement.)

 You can include multiple SET TRANSACTION statements in a host
 language source file or in an SQL language module, providing
 flexibility for changing transaction characteristics in a
 program source file.

 Example 1:  Forcing early constraint evaluation

 By default, SQL checks constraints when you issue a COMMIT
 statement.  The EVALUATING clause makes SQL check the
 EMPLOYEE_ID_CHECK constraint when inserting a new row in
 EMPLOYEES instead.  Use the SHARED WRITE clause when you intend
 to update or insert rows into a table.

 SQL> SET TRANSACTION
 cont> EVALUATING EMPLOYEE_ID_CHECK AT VERB TIME
 cont> RESERVING EMPLOYEES FOR SHARED WRITE;

 Example 2:  Specifying multiple databases and transaction modes

 A batch-update transaction started on a database cannot include
 additional arguments.  However, other databases referred to in
 the same transaction declaration can include other arguments.

 SQL> SET TRANSACTION ON OLD_DB USING (READ ONLY)
 cont>    AND ON NEW_DB USING (BATCH UPDATE);

 tx-options =

  -+---> BATCH UPDATE ----------->---------------------------+->
   +-+------------------+-+------>-----------------------+-+ |
     +-> READ ONLY    --+ +-> WAIT -+--------->--------+-+ | |
     +-> READ WRITE   --+ |         +-> timeout-value -+ | | |
                          +-> NOWAIT --------------------+ | |
     +----------------------<------------------------------+ |
     +-+-------------------->---------+---+                  |
       +-> CONSISTENCY LEVEL -+-> 2 --+   |                  |
                              +-> 3 --+   |                  |
     +------------------------------------+                  |
     +-+----------------->-----------------------+-+         |
       +-> EVALUATING -+-> evaluating-clause --+-+ |         |
                       +-------- , <-----------+   |         |
     +---------------------------------------------+         |
     +-+----------------->-----------------------+----->-----+
       +-> RESERVING --+-> reserving-clause ---+-+
                       +-------- , <-----------+

Additional information available:

BATCH_UPDATEREAD_ONLYREAD_WRITEWAIT
NOWAITCONSISTENCY_LEVELEVALUATINGRESERVING

 BATCH UPDATE reduces overhead in large load operations by not
 writing to any journal files.  No rollback is possible for batch
 update transactions.

 READ ONLY specifies that you want to use a snapshot of the
 database for only reading records.

 READ WRITE specifies that you want to use a locking mechanism of
 SQL to get consistency in data retrieval and update for reading
 and writing records.

 WAIT or NOWAIT determines what your transaction does when it
 encounters a locked record.  WAIT waits for other transactions
 to complete and then proceds.

Additional information available:

timeout-value

 An integer that 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 zero
 specifies NOWAIT.

 WAIT or NOWAIT determines what your transaction does when it
 encounters a locked record.  NOWAIT returns an error message
 when it encounters a locked record.

 CONSISTENCY LEVEL determines the extent to which the database
 protects the consistency of your data.  Relationships between
 data items may not be preserved by LEVEL 2 transactions since
 data items can be updated by another transaction before the
 LEVEL 2 transaction finishes.  LEVEL 3 transactions mean the
 database system guarantees that data you read will not be
 changed by another user before you issue a COMMIT statement.

 EVALUATING determines 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.  For READ ONLY transactions, this clause is allowed
 but meaningless.

Additional information available:

evaluating-clause

 evaluating-clause =

 -+------->-----+-> constraint-name -> AT -+-> VERB TIME ---+-->
  +-> alias. ---+                          +-> COMMIT TIME -+

 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, they are evaluated when the COMMIT statement
 executes.  For read-only transactions, this clause is allowed
 but is meaningless.

 RESERVING specifies the list of tables to be locked during the
 transaction.

Additional information available:

reserving-clause

 reserving-clause =

 --+-+-> table-name -+-+--> FOR --+------->------+-+-> READ --+->
   | +-> view-name --+ |          +-> EXCLUSIVE -+ +-> WRITE -+
   +------- , <--------+          +-> PROTECTED -+
                                  +-> SHARED ----+
 If constraints were defined for your database, you must include
 in the RESERVING clause any tables that the database system must
 access to evaluate the constraint.

 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
 underlying tables on which the view is based.

Additional information available:

EXCLUSIVEPROTECTEDSHARED

 When you specify EXCLUSIVE, other users cannot read records from
 the tables included in your transaction.  If another user refers
 to the same tables in a DECLARE TRANSACTION statement, SQL
 denies access to that user.

 When you specify PROTECTED, Other users can read the tables you
 are using.  They cannot have write access.

 This is the default share mode.  When you specify SHARED, other
 users can also work with the same tables.  Depending on the
 option they choose, they can have read-only or read/write access
 to the tables.

 db-txns =

 -+-------------------------------------+->
  ++-> ON -+-> alias -+-> USING -+      |
   |       +---- , ---+          |      |
   |   +-------------------------+      |
   |   +-> ( -+-> tx-options -+-> ) -+--+
   |          +-> DEFAULTS ---+      |
   |                                 |
   +------------- AND <--------------+

Additional information available:

More Information

 Specifies the options for accessing other databases when using
 the SET TRANSACTION statement.  These options include the alias
 for the database and whether you want to state the transaction
 options individually or accept the default transaction options
 of READ WRITE WAIT plus the consistency option appropriate for
 the database system you are using.