Command affected 0 row(s) conflict

Owned by Copy Space for Confluence
Jan 22, 2017
3 min read

The conflict "Command affected 0 row(s)" is the most common type of conflict that can occur with replication. This conflict can be on a delete or update statement.

This conflict indicates that the data is no longer in sync between the source and target data. 

This conflict can have the following causes in order of most likely:

  1. The data was not 100% insync when replication started
  2. A previous conflict was ignored and so the data is now out of sync
  3. The data was independently manipulated on the target database
  4. There are uncommitted transactions on the APPLY database. Please see Uncommitted transactions

The first step is to determine where the data is different between the source and target. For example which column is different.

To view the SQL causing the conflict see https://dbvisit.atlassian.net/wiki/display/ugd8/Viewing+the+conflicts

In order to determine where the difference is, the conflicting SQL can be manually run on the target database.
The process is to run the SQL repeatedly and each time removing one of the predicates in the where clause until the data is matched. 
For example if the conflicting SQL is:

 

DELETE from SCOTT."REQUESTS" 
where (1=1)
and ID = 3567
and TYPE IS NULL
and VERSION = 4
and STATE IS NULL
and STATUS = 'A'
and NAMES_SPACEID = 1
and STATUS_LASTMODIFIED = to_timestamp('2013.06.12 13:40:18.360000000', 'yyyy.mm.dd hh24:mi:ss.ff')
and ISRELATIVE IS NULL
and RELATIVE_STATUS = 3
and ACCEPTED_DATETIME = to_timestamp('2013.06.12 14:39:21.000000000', 'yyyy.mm.dd hh24:mi:ss.ff')
Error: Command affected 0 row(s).
Handled as: RETRY 
Conflict repeated 11 times.

Then this query can be turned into a SELECT statement that can be run manually on the target database:

SELECT * from SCOTT."REQUESTS" 
where (1=1)
and ID = 3567 
and TYPE IS NULL 
and VERSION = 4 
and STATE IS NULL 
and STATUS = 'A'
and NAMES_SPACEID = 1 
and STATUS_LASTMODIFIED = to_timestamp('2013.06.12 13:40:18.360000000', 'yyyy.mm.dd hh24:mi:ss.ff') 
and ISRELATIVE IS NULL 
and RELATIVE_STATUS = 3
and ACCEPTED_DATETIME = to_timestamp('2013.06.12 14:39:21.000000000', 'yyyy.mm.dd hh24:mi:ss.ff');

This query will also return 0 rows when it is run on the target as it has the same conditions as the delete statement which caused the conflict. 

Now remove the last predicate in the where clause and run again. In the case "and ACCEPTEDDATETIME" is removed:

SELECT * from SCOTT."REQUESTS" 
where (1=1) 
and ID = 3567 
and TYPE IS NULL 
and VERSION = 4 
and STATE IS NULL 
and STATUS = 'A'
and NAMES_SPACEID = 1 
and STATUS_LASTMODIFIED = to_timestamp('2013.06.12 13:40:18.360000000', 'yyyy.mm.dd hh24:mi:ss.ff') 
and ISRELATIVE IS NULL 
and RELATIVE_STATUS = 3;

If this now returns a record on the target database, then the data conflict is due to predicate that has just been removed. In this case "ACCEPTEDDATETIME"

If this still returns 0 rows, then remove the next predicate (RELATIVE_STATUS = 3) and run again.

Repeat this process until the select statement returns data. 

Why is the data different

The next step is to determine why the data is different. Compare the same row on source and target to determine what the difference is. Is it due to:

  1. The data not being in sync before the replication started?
  2. Someone else changed the data on the target?
  3. Open transactions on the target database. Please see Uncommitted transactions

Going back in time

It also helps to understand when the column was changed on the source system. This can be done with the AS OF SCN statement. This has the affect of going back in time to see the history of changes for the particular value. 

SQL> select ACCEPTEDDATETIME from SCOTT."REQUESTS" as of SCN
SQL> select ACCEPTEDDATETIME from SCOTT."REQUESTS" as of SCN-1
SQL> select ACCEPTEDDATETIME from SCOTT."REQUESTS" as of SCN-2
...

Where SCN is the current SCN which can be obtained as follows:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
  352654804

Resolving the conflict

Once it is understood why the conflict occurs, then conflict can be resolved. Please see Resolving Current Conflict.

If the data divergence is too great and it is not possible to resolve it through the conflict, then the table should be manually resynched. Please see http://support.dbvisit.com/entries/24422248-Synching-up-one-table-when-it-gets-out-of-sync

Search terms:

  • 0 rows affected
  • delete conflict
  • update conflict
  • Error: Command affected 0 row(s)
  • Handled as: RETRY 
  • Conflict repeated x times