1  SET_TRANSACTION
   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.
 

2  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

 (B)0SET TRANSACTION qqwqqqqqqqqqqqqqqqwq>   
                   tq> tx-options qu     
                   mq> db-txns qqqqj     



 (B)0tx-options =                                         
                                                      
 qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> 
   x tq> NAME 'quoted-string' qqqqqqqqqqqqqqqqu x
   x tq> EVALUATING qwq evaluating-clause qqwqu x
   x x               mqqqqqqq> , <qqqqqqqqqqj x x
   x tq> RESERVING qqwq> reserving-clause qqwqu x     
   x x               mqqqqqqqq , <qqqqqqqqqqj x x
   x tq> isolation-level qqqqqqqqqqqqqqqqqqqqqu x
   x tq> transaction-mode qqqqqqqqqqqqqqqqqqqqu x
   x mq> wait-option qqqqqqqqqqqqqqqqqqqqqqqqqj x
   mqqqqqqqqqqqqqqqqwqqqqqqqwqqqqqqqqqqqqqqqqqqqj
                    mqq , <qj



 (B)0transaction-mode =
 
 qqwq> READ ONLY qqqqwq>
   tq> READ WRITE qqqu
   mq> BATCH UPDATE qj



 (B)0wait-option =
 qqwq> WAIT qwqqqqqqqqqqqqqqqqqqqqqwqwq>
   x         mq> <timeout-value> qqj x
   mq> NOWAIT qqqqqqqqqqqqqqqqqqqqqqqj



 (B)0isolation-level =
 
 qqq> ISOLATION LEVEL qqwq> READ COMMITTED qqqqwqq>
                        tq> REPEATABLE READ qqqu
                        mq> SERIALIZABLE qqqqqqj



 (B)0evaluating-clause =                                               
                                                                   
 qwqqqqqqqqqqqqqwq> <constraint-name> q> AT qwq> VERB TIME qqqwqq> 
  mq> <alias.> qj                            mq> COMMIT TIME qj    
                                                                   



 (B)0reserving-clause =                                                         
                                                                            
 qwqwq> <view-name> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqk   
  x mq> <table-name> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqj x x   
  x                   mq> PARTITION qq> ( qwq> <part-num> qwq> ) qj   x x   
  x                                        mqqqqqq , <qqqqqj          x x   
  mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj x   
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   
  mq> FOR qwqqqqqqqqqqqqqqwqqwq> READ qqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqq>   
           tq> EXCLUSIVE qu  tq> WRITE qqqqqqqqqqqu                         
           tq> PROTECTED qu  mq> DATA DEFINITION qj                         
           mq> SHARED qqqqj                                                 
                                                                            



 (B)0db-txns =                                                            
                                                                      
 qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>
   mwq> ON qwq> <alias> qwq> USING qq> ( wq> tx-options qqwq> ) qwj   
    x       mqqqq , <qqqqj               mq> DEFAULTS qqqqj      x    
    mqqqqqqqqqqqqqqqqqqqqqqqqqq AND <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj    
                                                                      

 

2  Arguments
 

3  alias

   Specifies the alias for a constraint.
 

3  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.
 

3  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  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  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  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  NAME_transaction-name

   Supplies a title for the transaction. This information is
   displayed by the SET FLAGS TRANSACTION keyword.
 

3  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  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  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  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  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  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  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  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  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  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.
 

2  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>