Dealing With Conflicts - Pt 2
In the previous post in this series we provided some background context to our discussion of conflict handling in Dbvisit Replicate, by laying the platform for understanding how logical replication works. In this second instalment we will take a look at the options for manual resolution within the application, and then in the third, detail Dbvisit Replicate’s inbuilt automatic conflict handling mechanisms.
So what are our options for dealing with a conflict when it occurs?
As mentioned previously, Dbvisit Replicate has built in mechanisms for dealing with conflicts, beginning with the fact that it will highlight these, rather than letting them slide through. Its default action is to completely PAUSE the replication, and so requires input from a DBA/administrator in order to resolve. There are two types of methods for processing conflicts if they should occur;
Manual resolution, which we will focus on in this post AND
Configure automatic handlers to take care of these (which we will look at in part 3 of this series)
If conflicts occur they are highlighted in the Dbvisit Replicate command console, and manual resolution can also be performed from this context:
To see detailed information on the conflict simply run the following command:
Depending on the nature of the conflict this output will contain the actual SQL statement that produced the conflict, and this can be used to determine the root cause in certain circumstances, as for a zero rows conflict. The syntax for manual conflict resolution is as follows, where “id” is the unique number assigned to the conflict: > RESOLVE CONFLICT <id> AS <resolution> In the example above I have fabricated a conflict scenario by creating a table on the source which already existed on the target. With DDL replication enabled for my configuration this statement was processed on the source (the table created) and then the very same statement attempted on the target, thus producing the conflict. Because I understand the origins of this error (and this is not always easy to do when unpacking the root causes of conflicts) the way forward in resolving this is straightforward, and I can simply ignore the conflict. That is, I don’t require Replicate to intervene here, and it can forget that it even happened, allowing the replication to flow again. I invoke this action as follows:
One this resolution has been processed we can see that the replication begins to flow again - it is no longer paused - although information relating to the last occurrence of a conflict can still be seen in the console:
Making Resolutions
As demonstrated in the above example, a resolution in this context is the action you wish to take in order to process through a conflict, and which will enable data to flow - remembering again that the default action is for Dbvisit Replicate to pause the entire replication (not just one table) when a conflict occurs. And for this the following options are available:
IGNORE
FORCE
OVERWRITE
RETRY
ABORT
RESTART
ROLLBACK
IGNOREALL
Logically speaking we can group these resolutions roughly into two categories, as those that:
Take NO direct action on the data values involved
DO something with the data values in question
So let’s take a look at these groups and options in more detail:
NOT doing something with the data values.
Retry: simply try the original operation again and see if it processes through this time (in the case of intermittent timeouts/locks this may be a successful intervention).
Restart: Whether your conflicting transaction is one, or several thousand rows/records, choosing this option will abandon these changes, going all the way back to the beginning of that transaction, before attempting to apply these same changes again. Restart actually rolls back the transaction and then starts applying again from the beginning.
Rollback: If a conflict is detected you have the option to simply roll back the transaction, instead of committing the change to your target. This backs out the transaction on the TARGET, and all uncommitted changes in that transaction are left undone there, without any further attempts to apply them. This option means that the Apply process won’t try again to apply this specific transaction.
Abort: This kills the Apply process, stopping it from enacting changes to the target database by shutting it down altogether. This option can be considered something like an emergency shut off valve, which gives the DBA/administrators time to assess the cause of the conflict and map out a clear resolution path.
Ignore: In using this command you essentially ignore the statement that generated a conflict, discarding it altogether. However, before you simply ignore an error you should consider whether this has the potential to degrade the data integrity of your replication. If the statement has been run and committed on the source then, without some corrective action, that same data set will diverge from this on the target side. Ignore all: This is similar to “Ignore”. However, if you use “Ignore All” each and every conflict in a particular transaction (which may comprise of many individual changes) will be skipped.
Doing something with the data values.
Force: Unlike the “Ignore” command this option ensures that changes requested in a transaction, before a conflict occurred, are pushed through. However, it only applies in situations where a single change on the target affects more than one row, which is only really possible if there is no (matching) primary/unique key on the target. For example, if a target table has constraints disabled (PK is “off”) and also duplicate rows, then an update/delete made on the source side, which affects these specific duplicate rows, will generate a >1 rows (TOO_MANY) conflict. By resolving this with the Force option we can ensure an outcome in which all of these duplicate rows are actually affected (updated/deleted) by the change initiated on the source, on the target side. To illustrate let’s work with the HR.EMPLOYEES table used in a previous example, which currently looks like this in the target database:
It has a PK so I disable it (again to fabricate a conflict scenario) with the following: > alter table HR.EMPLOYEES disable primary key;
I then insert a duplicate row directly into the target table(remembering that this is an open read-write instance of its own accord), as follows: > insert into HR.EMPLOYEES values (1,'Mike',32,4,'HR’,1); And following a commit it reads as follows:
An update or delete made to this duplicate row (ID=1, name=‘Mike’, etc) on the source will now generate a conflict on the target, because the resultant SQL statement will affect more than one row. So I attempt to update the rating for “Mike” from 4 to 5 on the source side, and commit this change: > update HR.EMPLOYEES set rating=5 where name = 'Mike' This produces the following output in the Dbvisit Replicate console:
And if we drill into the detail of the conflict can see that the issue is “Command affected 2 row(s)”
I manually invoke the Force option as follows:
And now we can see that the Force resolution has resulted in the changes initiated on source being applied to ALL the rows that it has picked up in this target table (i.e.. Both id=1, name=‘Mike’ records have a rating=5 now):
For a DELETE operation selecting FORCE would remove ALL those identified duplicate rows from the target.
Overwrite: In this resolution, instead of using all the columns specified in the SQL statement WHERE clause from the source, Replicate will use just the primary key. That is, it will only update/delete on that primary key and overwrite the new values/remove the row, on top of any pre-existing values. In doing so it tries the failed operation again without checking the old (source) values in the WHERE clause, in preference for the PK values. To illustrate this, let’s clean up our HR.EMPLOYEES table used earlier, so there are no duplicates on the target, and then re-enable the PK:
To manufacture the conflict I then update the age for the employee named Peter, in the target table, incrementing it from 44 to 45: > Update HR.EMPLOYEES set age=45 where name = 'Peter' I then run the following statement on the source, to change Peter’s department from SALES to IT, and commit: > Update HR.EMPLOYEES set department = 'IT' where name = 'Peter' and age=44 And this immediately produces a conflict:
With the following detail:
And I process the conflict manually with the following command: > Resolve conflict 329010162441 as OVERWRITE This clears the conflict, pushing through the change to the department for Peter’s record, based on the PK (id =3) as the predicate:
For delete statements of this sort Overwrite will result in the record being removed from the target table, according to the PK for the row. (Note: in version 2.7 of Dbvisit Replicate there was a bug in this functionality, which has been corrected in 2.8). In this post we have covered off the options for manual conflict resolution with Dbvisit Replicate, and in our next installment we will take a look at the powerful automatic conflict handling mechanisms, which are built into the application.