LOOP PREVENTION

Owned by Herman Kall (Unlicensed)
Last updated: Apr 14, 2015
2 min read

From version 2.8 onwards Replicate parameters APPLY_LOOP_PREVENTION and MINE_LOOP_PREVENTION will become public ones.

This page is trying to shed some light onto the meaning of the APPLY_LOOP_PREVENTION and MINE_LOOP_PREVENTION parameters.

 

Replicate does filtering of transactions based on TRANSACTION_NAME which contains "DBREPL_database_name_XID". 

 

APPLY_LOOP_PREVENTION ( YES on default)

Purpose:  

  •  Changes that are mined on the source server and then applied on the target server are not mined again on the target server. This is prevented by the APPLY_LOOP_PREVENTION setting.
  • Based on TRANSACTION_NAME the APPLY_LOOP_PREVENTION throws transaction out on APPLY - if the database name matches current database meaning it just completed a loop.
  • It also checks if the DDL was done by an apply process on this db; modify the DDL by adding comment to the end so next mine/apply can check that it was done by apply.

Valid Settings: YES or NO



MINE_LOOP_PREVENTION ( NO on default)

Purpose:  

  • Changes that are mined on the source server and then applied on the target server. This is controlled by the MINE_LOOP_PREVENTION setting.
  • MINE_LOOP_PREVENTION throws out transaction based on the TRANSACTION_NAME that contains "DBREPL_database_name_XID". 

                 If set to YES it checks if Replicate does skip this DDL due to: 

    • it is created by an APPLY process and APPLY adds a comment to end of the DDL.

This is disabled - Replicate need to pass at least CREATE TABLE to APPLY; it just lets APPLY_LOOP_PREVENTION do it's job for DDL

    • an established isolation level triggered by a SET TRANSACTION statement


Comment: MINE_LOOP_PREVENTION is preferred to APPLY_LOOP_PREVENTION as the changes are filtered sooner and don't even get written to a PLOG file. But it's too aggressive in more complicated scenarios.

Valid Settings: YES or NO


 

 If both APPLY_LOOP_PREVENTION and MINE_LOOP_PREVENTION are disabled APPLY will not set/use transaction name 'DBREPL_%'.

 



USE CASES:

One-To-One Replication: MINE_LOOP_PREVENTION is not needed.

One-To-Many: MINE_LOOP_PREVENTION is not needed.

One-On-One (cascade A -> B -> C): don't use MINE_LOOP_PREVENTION, it would prevent changes from first to last. APPLY_LOOP_PREVENTION is not needed.

One-On-One-Back (triangle A -> B -> C -> A): don't use MINE_LOOP_PREVENTION. Use APPLY_LOOP_PREVENTION to stop the loop when s change gets back "home".

Bi-direction (or 2-way): MINE_LOOP_PREVENTION can be used. 

 

The settings for APPLY_LOOP_PREVENTION and MINE_LOP_PREVENTION can be different on each DB, but usually it's not necessary. And if they are kept the same, then using MINE_LOOP_PREVENTION implies APPLY_LOOP_PREVENTION has no effect. (MINE already filtered the changes.)

 

See also:

Filtering deletes at a transaction level

Filter the data to be replicated