Oracle 11g R2 MAX_COMMIT_PROPAGATION_DELAY

Last update: 4 August 2017

Oracle 11g R2 MAX_COMMIT_PROPAGATION_DELAY

Summary

In the Fabasoft Folio White Paper "Oracle Database Preparation Guide.pdf" chapter "Installation" the Oracle Parameter MAX_COMMIT_PROPAGATION_DELAY is documented to set to 0 for Real Application Cluster (RAC) environments.

This parameter was deprecated and removed by Oracle for Oracle Database 11g R2 and above. The parameter was replaced by the parameter _IMMEDIATE_COMMIT_PROPAGATION, that defaults to TRUE. This default has the same effect than the old parameter set to 0.

Therefore in Oracle 11g R2 RAC both MAX_COMMIT_PROPAGATION_DELAY and _IMMEDIATE_COMMIT_PROPAGATION don't need to be changed.

Note: This only applies for Oracle Real Application Cluster installations. Normal Oracle Database installations are not affected.

Information

The MAX_COMMIT_PROPAGATION_DELAY parameter sets the delay to deploy inserted and updated data across the RAC nodes. The default value is 700, meaning the maximal time to distribute COMMITs to other RAC nodes can be 7 seconds. This may lead to inconsistent reads when concurrency occurs for the same data on different RAC nodes. Therefore Fabasoft recommends in the white paper to set the MAX_COMMIT_PROPAGATION_DELAY parameter to 0 to avoid "unique constraint violated" error messages.

In Oracle 11g R2 Oracle changed the default behaviour to always deploy commits immediately to other RAC nodes, so inconsistency is avoided. The MAX_COMMIT_PROPAGATION_DELAY parameter was removed. Therefore, for Oracle 11g R2 the MAX_COMMIT_PROPAGATION_DELAY need not to be set.

To double-check that the default value _IMMEDIATE_COMMIT_PROPAGATION of Oracle 11g R2 is set to TRUE, you can run this query:

SELECT ksppinm, ksppstvl FROM x$ksppi x, x$ksppcv y 
WHERE x.indx = y.indx AND ksppinm = '_immediate_commit_propagation'

Solution

Assumed you have an Oracle Real Application Cluster installation:

  • Below  Oracle 11g R2 set the MAX_COMMIT_PROPAGATION_DELAY to 0.
  • From and above  Oracle 11g R2 do not set this parameter and leave _IMMEDIATE_COMMIT_PROPAGATION in default value TRUE.

Without Oracle Real Application Cluster the settings do not apply.

Applies to

  • Oracle Database 11g R2 using RAC
  • Fabasoft Folio (all versions)
  • Fabasoft eGov-Suite (all versions)
  • Fabasoft eCRM-Suite (all versions)