03 - More Rows Affected Conflict
- Petr Horejs
In general, there are 3 basic conflict types in replication:
- no rows affected
- more than one row affected
- other type of error
In previous chapter we introduced the first type of the conflict, now we will introduce 2nd type of conflict - more rows affected.
Creation of duplicate rows (potential more than one row affected conflict):
In order to simulate conflict in replication, we have to create at least one row, that is precise clone of another row. This cannot be done on table, that has primary key defined. In RepAttack prepared testing environment, there are just two tables, that do not have primary keys defined:
REPOE.LOGON and REPOE.ORDERENTRY_METADATA. We will use the REPOE.LOGON table for duplicate creation. Run following insert on source database to create duplicity:
REPOE@SOURCE> insert into repoe.logon select * from repoe.logon where logon_id=1000000; 1 row created. REPOE@SOURCE> commit; Commit complete.
Wait until the row gets replicated and confirm it also with dbvrep console.
Check, that the duplicated row also exists in the target database.
REPOE@TARGET> select * from repoe.logon where logon_id=1000000 order by customer_id; LOGON_ID CUSTOMER_ID LOGON_DATE ---------- ----------- ------------------ 1000000 496840 31-MAR-06 1000000 496840 31-MAR-06
Create conflict
Using the duplicate row we prepared in previous chapter, we can now simulate conflict in replication.
On source database run following update command.
REPOE@SOURCE> update repoe.logon set logon_id=logon_id*100 where logon_id=1000000; 2 rows updated. REPOE@SOURCE> commit; Commit complete.
Check in the console, that we have really created the conflict
REPOE.LOGON: 33% Mine:3/3 Unrecov:0/0 Applied:1/1 Conflicts:1/1 Last:23/01/2015 04:05:29/RETRY:Command affected 2 row(s).
At this moment the apply process stopped and is waiting for manual resolve of the conflict.
Listing and Resolving Conflicts
As we have created a conflict, we have to see what exactly happened.
Connect to dbvrep console and run command list conflict
dbvrep> list conflict
Information for conflict 32010159940 (current conflict):
Table: REPOE.LOGON at transaction 000a.01f.000001df at SCN 653532
SQL text (with replaced bind values): update "REPOE"."LOGON"
set "LOGON_ID" = 100000000
where (1=1)
and "LOGON_ID" = 1000000
and "CUSTOMER_ID" = 496840
and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss')
Error: Command affected 2 row(s).
Handled as: PAUSE
Conflict repeated 21 times.
Take a note of how the original query was transformed. You can see, that except our original "LOGON_ID" = 1000000 condition, there were automatically added "CUSTOMER_ID" = 496840 and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss') search conditions. These were added in order to apply be able to uniquely identify the row it needs to change.
Also have a closer look at the row "Error: Command "affected 2 row(s).". This is the core message telling us, why was the row rejected by apply process - in general it is called conflict. Other type of conflict would be affecting of 0 rows (row exists on source, but apply didn't find it on target).
Now we have to resolve the conflict in order to let apply process to continue. Lets choose ignore resolution type:
dbvrep> resolve conflict 32010159940 as ignore Conflict resolution set.
Ignore means, that the row will be discarded. Now look at the top of the console if this resolution unblocked apply process:
APPLY IS running. Currently at plog 32 and SCN 653526 (01/23/2015 04:05:20) and 2 apply conflicts so far (last at 24/01/2015 00:14:17) and WAITING on manual resolve of apply conflict id 32010159942 Progress of replication repoe:MINE->APPLY: total/this execution ------------------------------------------------------------------------------------------------------------------ REPOE.CARD_DETAILS: 100% Mine:2/2 Unrecov:0/0 Applied:2/2 Conflicts:0/0 Last:22/01/2015 23:29:39/OK REPOE.LOGON: 66% Mine:3/3 Unrecov:0/0 Applied:2/2 Conflicts:2/2 Last:24/01/2015 00:12:37/RETRY:Command affected 2 row(s). ------------------------------------------------------------------------------------------------------------------
What happened? The apply process is still stuck. The count of conflicts raised by 1 and apply is again waiting for manual resolution. Lets check the conflicts again:
dbvrep> list conflict
Information for conflict 32010159942 (current conflict):
Table: REPOE.LOGON at transaction 000a.01f.000001df at SCN 653533
SQL text (with replaced bind values): update "REPOE"."LOGON"
set "LOGON_ID" = 100000000
where (1=1)
and "LOGON_ID" = 1000000
and "CUSTOMER_ID" = 496840
and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss')
Error: Command affected 2 row(s).
Handled as: PAUSE
Conflict repeated 21 times.
It seems that the output is the same to the previous one, but there are some important changes there. The conflict ID raised, but most important the SCN number raised by one. Also good to point out, that the transaction id is the same.
Remember, that our update changed 2 rows. It is a single operation on source, but the replication changed the transaction into 2 separate updates. Each of these updates are then run against target database. And each of these updates create conflict.
To help replicate avoid such kind of conflicts it is advisable to have primary or at least unique keys defined on tables being replicated.
Lets resolve the second conflict as force. The force option means, that the command will be run against the target database and conflict will be ignored:
dbvrep> resolve conflict 32010159942 as force Conflict resolution set.
Now we can see, that the apply process finally got unstucked.
APPLY IS running. Currently at plog 32 and SCN 653550 (01/23/2015 04:05:25)
Now we have to check whether our two rows got replicated:
REPOE@TARGET> select count(*) from repoe.logon where logon_id=1000000; COUNT(*) ---------- 0 REPOE@TARGET> select count(*) from repoe.logon where logon_id=100000000; COUNT(*) ---------- 2
For more information about conflicts refer to this Conflicts Chapter.