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

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