Partially read-only - Only queries are issued at one or more nodes - Oracle Transaction

Q.  Select the correct case for which only queries are issued at one or more nodes.
- Published on 29 Jul 15

a. Partially read-only
b. Completely read-only with prepare phase
c. Completely read-only without two-phase commit
d. Both B and C

ANSWER: Partially read-only
 

    Discussion

  • Nirja Shah   -Posted on 23 Sep 15
    - When a node is asked to prepare, and the SQL statements affecting the database do not change any data on the node, the node responds with a read-only message.

    - The message indicates that the node will not participate in the commit phase.

    - There are three cases in which all or part of a distributed transaction is read-only:

    1. Partially read-only
    - Any of the following occurs:
    1. Only queries are issued at one or more nodes.
    2. No data is changed.
    3. Changes rolled back due to triggers firing or constraint violations.
    - The read-only nodes recognize their status when asked to prepare.
    - They give their local coordinators a read-only response.
    - Thus, the commit phase completes faster because the database eliminates read-only nodes from subsequent processing.

    2. Completely read-only with prepare phase
    - All of following occur:
    1. No data changes.
    2. Transaction is not started with SET TRANSACTION READ ONLY statement.
    - All nodes recognize that they are read-only during prepare phase, so no commit phase is required.
    - The global coordinator, not knowing whether all nodes are read-only, must still perform the prepare phase.

    3. Completely read-only without two-phase commit
    - All of following occur:
    1. No data changes.
    2. Transaction is started with SET TRANSACTION READ ONLY statement.
    - Only queries are allowed in the transaction, so global coordinator does not have to perform two-phase commit.
    - Changes by other transactions do not degrade global transaction-level read consistency because of global SCN coordination among nodes.
    - The transaction does not use undo segments.

Post your comment / Share knowledge


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)