Conflict - Command affected 0 row(s).

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

Error message: Update conflict with the error message "Error: Command affected 0 row(s)."

In most cases this error is because the source data set is not in sync with the target data set when the replication started, or because a previous conflict was ignored. However, in rare cases the issue is due to an update not being able to update the record, because the insert statement to create the record has not committed yet.

This could be due to a race condition between the 2 RAC nodes. One node does the insert, while the other node after a split seconds does the update. So it is an insert/update in very quick succession both on different nodes. Both the insert and update statements are captured by Replicate, but the commit of the insert comes AFTER the commit of the update in the PLOG and so the insert record is not committed yet before the update is done and that is why the conflict happens. So the apply process does the update, but because the insert has not yet been committed, the record is not yet visible and so the update fails.

Solution

A work around is to manually commit the Insert and then the update will automatically proceed. To find the uncommitted transaction run the following:

dbvrep> list transactions

This give a Transaction ID of all the transaction on apply that are not yet committed. Note this also lists internal transactions so run this command several times. We are only interested in Transaction IDs which stay constant on repeated running of the command.

To cross reference - The Transaction ID is the hex value of the combined columns XIDUSN, XIDSLOT, XIDSQN from v$transaction on the SOURCE database. The Transaction ID is also set in columns MODULE and ACTION from V$SESSION on the TARGET database.

To find the actual SQL statement run the following query and match the Transaction ID:

SQL> select inst_id, to_char(XIDUSN, 'xxxxxxxx'), to_char(XIDSLOT,'xxxxxxxx'), to_char(XIDSQN,'xxxxxxxx') from gv$transaction;

Note: Use v$transaction on Non RAC systems.

We are only interested in the INSERT statement. When the Transaction ID of the INSERT statement is found, then the record can be committed with:

This will then cause the update statement to proceed as the insert statement is now committed on the target database.

The record can also be rolled back with

Arjen Visser July 14, 2013 23:00