Starts a transaction using the specified attributes. If DEFAULT
is specified, then the attributes are derived from the user's
profile.
1 – Environment
You can use the START 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
START --+-> DEFAULT TRANSACTION --------------------------+-->
+-> TRANSACTION -+------------------------------+-+
+--+-+-> transaction-mode -+-+-+
| +-> isolation-level --+ |
+---------- , <-----------+
transaction-mode =
--+-> READ ONLY ---+-->
+-> READ WRITE --+
isolation-level =
---> ISOLATION LEVEL --+-> READ COMMITTED ----+-->
+-> REPEATABLE READ ---+
+-> SERIALIZABLE ------+
3 – Arguments
3.1 – DEFAULT
If the keyword DEFAULT is used, the user-specific default
transaction is started. This default is defined in the profile
for the current session user. If none is specified, a READ ONLY
transaction will be started.
SQL> CREATE PROFILE READ_USERS
cont> DEFAULT TRANSACTION READ ONLY WAIT 10;
SQL> ALTER USER JONES PROFILE READ_USERS;
A START DEFAULT TRANSACTION statement executed by JONES will
start a READ ONLY WAIT 10 transaction.
For information on profiles see the ALTER and CREATE PROFILE
statements.
3.2 – 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 START TRANSACTION
statement.
Table 1-9 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.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.4 – 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.
4 – Examples
Example 1: Starting a Default Transaction in a Multistatement
Procedure or as a Single Statement
SQL> START DEFAULT TRANSACTION;
SQL>
SQL> BEGIN
cont> COMMIT;
cont> START DEFAULT TRANSACTION;
cont> END;
SQL>
SQL> ROLLBACK;
Example 2: Starting Several Variations of the START TRANSACTION
Statement
SQL> START TRANSACTION READ WRITE,
cont> ISOLATION LEVEL READ COMMITTED;
SQL> COMMIT;
SQL>
SQL> -- Defaults to serializable
SQL> START TRANSACTION READ WRITE;
SQL> COMMIT;
SQL>
SQL> -- Defaults to read write
SQL> START TRANSACTION ISOLATION LEVEL READ COMMITTED;
SQL> ROLLBACK;
SQL>
SQL> -- Defaults to read write serializable
SQL> START TRANSACTION;
SQL>
SQL> BEGIN
cont> COMMIT;
cont> START TRANSACTION
cont> ISOLATION LEVEL READ COMMITTED,
cont> READ WRITE;
cont> END;
SQL> COMMIT;