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>