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.

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

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.