SET TRANSACTION ──┬────────>──────┬─> typebox (;)
                   ├─> 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 =

  ─┬───> 
B
A
T
C
H 
U
P
D
A
T
E ───────────>───────────────────────────┬─>
   └─┬──────────────────┬─┬──────>───────────────────────┬─┐ │
     ├─> 
R
E
A
D 
O
N
L
Y    ──┤ ├─> 
W
A
I
T ─┬─────────>────────┬─┤ │ │
     └─> 
R
E
A
D 
W
R
I
T
E   ──┘ │         └─> timeout-value ─┘ │ │ │
                          └─> 
N
O
W
A
I
T ────────────────────┘ │ │
     ┌──────────────────────<──────────────────────────────┘ │
     └─┬────────────────────>───────────────────────┬───┐    │
       └─> 
I
S
O
L
A
T
I
O
N
L
E
V
E
L ───┬─> typebox (R)typebox (E)typebox (A)typebox (D) typebox (C)typebox (O)typebox (M)typebox (M)typebox (I)typebox (T)typebox (T)typebox (E)typebox (D) ───┤   │    │
                              ├─> typebox (R)typebox (E)typebox (P)typebox (E)typebox (A)typebox (T)typebox (A)typebox (B)typebox (L)typebox (E) typebox (R)typebox (E)typebox (A)typebox (D) ──┤   │    │
                              └─> typebox (S)typebox (E)typebox (R)typebox (I)typebox (A)typebox (L)typebox (I)typebox (Z)typebox (A)typebox (B)typebox (L)typebox (E) ─────┘   │    │
     ┌──────────────────────────────────────────────────┘    │
     └─┬─────────────────>───────────────────────┬─┐         │
       └─> 
E
V
A
L
U
A
T
I
N
G ─┬─> evaluating-clause ──┬─┘ │         │
                       └──────── , <───────────┘   │         │
     ┌─────────────────────────────────────────────┘         │
     └─┬─────────────────>───────────────────────┬─────>─────┘
       └─> 
R
E
S
E
R
V
I
N
G ──┬─> reserving-clause ───┬─┘
                       └────────── , <─────────┘

Additional information available:

BATCH_UPDATEREAD_ONLYREAD_WRITEWAIT
NOWAITISOLATION_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 proceeds.

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.

 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.

 Rdb/VMS supports isolation levels READ COMMITTED, REPEATABLE
 READ, and SERIALIZABLE.  When you use SQL with Rdb/VMS
 databases, SQL by default 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 a 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 a 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:

  o  ISOLATION LEVEL SERIALIZABLE transaction

     Does not allow either nonrepeatable reads or phantoms.

  o  ISOLATION LEVEL REPEATABLE READ transaction

     Does not allow nonrepeatable reads but does permit phantoms.

  o  ISOLATION LEVEL READ COMMITTED transaction

     Allows both nonrepeatable reads and phantoms.

 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 ─> 
A
T ─┬─> 
V
E
R
B 
T
I
M
E ──┬─>
  └─> alias. ─┘                          └─> 
C
O
M
M
I
T 
T
I
M
E ┘

 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 ─┬┬─> 
F
O
R ┬──────── > ──┬┬─> 
R
E
A
D ─┬>
  │└─> view-name ──┘│       ├─> 
E
X
C
L
U
S
I
V
E ┤└─> 
W
R
I
T
E ┘
  └─────── , <──────┘       ├─> 
P
R
O
T
E
C
T
E
D ┤
                            └─> 
S
H
A
R
E
D ───┘
 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 =
 ─┬──────────────────────────────────────────────────┬>
  └┬> 
O
N ┬> alias ┬> 
U
S
I
N
G ─> ( ┬─> tx-options ┬> ) ┬┘
   │     └─── , <─┘             └─> 
D
E
F
A
U
L
T
S ──┘    │
   └────────────────── 
A
N
D <────────────────────────┘

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.