14 Resolving Replication Conflicts

This chapter includes these topics:

How replication conflicts occur
Using a timestamp to resolve conflicts
Configuring timestamp comparison
Reporting conflicts
The conflict report XML Document Type Definition

How replication conflicts occur

Tables in databases configured in a bidirectional replication scheme may be subject to replication conflicts. A replication conflict occurs when applications on bidirectionally replicated databases initiate an update, insert or delete operation on the same data item at the same time. If no special steps are taken, each database can end up in disagreement with the last update made by the other database.

These types of replication conflicts can occur:

Update conflicts: This type of conflict occurs when concurrently running transactions at different databases make simultaneous update requests on the same row in the same table, and install different values for one or more columns.
Uniqueness conflicts: This type of conflict occurs when concurrently running transactions at different databases make simultaneous insert requests for a row in the same table that has the same primary or unique key, but different values for one or more other columns.
Delete conflicts: This type of conflict occurs when a transaction at one database deletes a row while a concurrent transaction at another database simultaneously updates or inserts the same row. Currently, TimesTen can detect delete/update conflicts, but cannot detect delete/insert conflicts. TimesTen cannot resolve either type of delete conflict.

See "Reporting conflicts" for example reports generated by TimesTen upon detecting update, uniqueness, and delete conflicts.

Note:

TimesTen does not detect conflicts involving TRUNCATE TABLE statements.

Update and insert conflicts

Figure 14-1 shows the results from an update conflict, which would occur for the value of X under the following circumstances:

Steps	On Database A	On Database B
Initial condition	X is 1.	X is 1.
The application on each database updates X simultaneously.	Set X=2.	Set X=100.
The replication agent on each database sends its update to the other database.	Replicate X to database B.	Replicate X to database A.
Each database now has the other's update.	Replication says to set X=100.	Replication says to set X=2.

Note:

Uniqueness conflicts resulting from conflicting inserts follow a similar pattern as update conflicts, but the conflict involves the whole row.

Figure 14-1 Update conflict

Description of "Figure 14-1 Update conflict"

If update or insert conflicts remain unchecked, the master and subscriber databases fall out of synchronization with each other. It may be difficult or even impossible to determine which database is correct.

With update conflicts, it is possible for a transaction to update many data items but have a conflict on a few of them. Most of the transaction's effects survive the conflict, with only a few being overwritten by replication. If you decide to ignore such conflicts, the transactional consistency of the application data is compromised.

If an update conflict occurs, and if the updated columns for each version of the row are different, then the non-primary key fields for the row may diverge between the replicated tables.

Note:

Within a single database, update conflicts are prevented by the locking protocol: only one transaction at a time can update a specific row in the database. However, update conflicts can occur in replicated systems due to the ability of each database to operate independently.

TimesTen replication uses timestamp-based conflict resolution to cope with simultaneous updates or inserts. Through the use of timestamp-based conflict resolution, you may be able to keep the replicated databases synchronized and transactionally consistent.

Delete/update conflicts

Figure 14-2 shows the results from a delete/update conflict, which would occur for Row 4 under the following circumstances:

Steps	On database A	On database B
Initial condition	Row 4 exists	Row 4 exists
The applications issue a conflicting update and delete on Row 4 simultaneously	Update Row 4	Delete Row 4
The replication agent on each database sends the delete or update to the other	Replicate update to database B	Replicate delete to database A
Each database now has the delete or update from the other database	Replication says to delete Row 4	Replication says to update Row 4

Figure 14-2 Delete/update conflict

Description of "Figure 14-2 Delete/update conflict"

Although TimesTen can detect and report delete/update conflicts, it cannot resolve them. Under these circumstances, the master and subscriber databases fall out of synchronization with each other.

Although TimesTen cannot ensure synchronization between databases following such a conflict, it does ensure that the most recent transaction is applied to each database. If the timestamp for the delete is more recent than that for the update, the row is deleted on each database. If the timestamp for the update is more recent than that for the delete, the row is updated on the local database. However, because the row was deleted on the other database, the replicated update is discarded. See "Reporting delete/update conflicts" for example reports.

Note:

There is an exception to this behavior when timestamp comparison is enabled on a table using UPDATE BY USER. See "Enabling user timestamp column maintenance" for details.

Using a timestamp to resolve conflicts

For replicated tables that are subject to conflicts, create the table with a special column of type BINARY(8) to hold a timestamp value that indicates the time the row was inserted or last updated. You can then configure TimesTen to automatically insert a timestamp value into this column each time a particular row is changed, as described in "Configuring timestamp comparison".

Note:

TimesTen does not support conflict resolution between cached tables in a cache group and an Oracle database.

How replication computes the timestamp column depends on your system:

On UNIX systems, the timestamp value is derived from the timeval structure returned by the gettimeofday system call. This structure reports the time of day in a pair of 4-byte words to a resolution of 1 microsecond. The actual resolution of the value is system-dependent.
On Windows systems, the timestamp value is derived from the GetSystemTimeAsFileTime Win32 call. The Windows file time is reported in units of 0.1 microseconds, but effective granularity can be as coarse as 10 milliseconds.

TimesTen uses the time value returned by the system at the time the transaction performs each update as the record's insert or update time. Therefore, rows that are inserted or updated by a single transaction may receive different timestamp values.

When applying an update received from a master, the replication agent at the subscriber database performs timestamp resolution in the following manner:

If the timestamp of the update record is newer than the timestamp of the stored record, TimesTen updates the row. The same rule applies to inserts. If a replicated insert is newer than an existing row, the existing row is overwritten.
If the timestamp of the update and of the stored record are equal, the update is allowed. The same rule applies to inserts.
If the timestamp of the update is older than the timestamp of the stored record, the update is discarded. The same rule applies to inserts.
If a row is deleted, no timestamp is available for comparison. Any update operations on the deleted row are discarded. However, if a row is deleted on one system, then replicated to another system that has more recently updated the row, then the replicated delete is rejected. A replicated insert operation on a deleted row is applied as an insert.
An update that cannot find the updated row is considered a delete conflict, which is reported but cannot be resolved.

Note:
If the ON EXCEPTION NO ACTION clause is specified for a table, then the update, insert, or delete that fails a timestamp comparison is rejected. This may result in transactional inconsistencies if replication applies some, but not all, the actions of a transaction. If the ON EXCEPTION ROLLBACK WORK clause is specified for a table, an update that fails timestamp comparison causes the entire transaction to be skipped.

Timestamp comparisons for local updates

To maintain synchronization of tables between replicated sites, TimesTen also performs timestamp comparisons for updates performed by local transactions. If an updated table is declared to have automatic timestamp maintenance, then updates to records that have timestamps exceeding the current system time are prohibited.

Normally, clocks on replicated systems are synchronized sufficiently to ensure that a locally updated record is given a later timestamp than that in the same record stored on the other systems. Perfect synchronization may not be possible or affordable, but by protecting record timestamps from "going backwards," replication can help to ensure that the tables on replicated systems stay synchronized.

Configuring timestamp comparison

To configure timestamp comparison:

Include a column in your replicated tables to hold the timestamp value. See "Including a timestamp column in replicated tables".
Include a CHECK CONFLICTS clause for each TABLE element in the CREATE REPLICATION statement to identify the timestamp column, how timestamps are to be generated, what to do in the event of a conflict, and how to report conflicts. See "Configuring the CHECK CONFLICTS clause".

Including a timestamp column in replicated tables

To use timestamp comparison on replicated tables, you must specify a nullable column of type BINARY(8) to hold the timestamp value. The timestamp column must be created along with the table as part of a CREATE TABLE statement. It cannot be added later as part of an ALTER TABLE statement. In addition, the timestamp column cannot be part of a primary key or index. Example 14-1 shows that the rep.tab table contains a column named tstamp of type BINARY(8) to hold the timestamp value.

Example 14-1 Including a timestamp column when creating a table

CREATE TABLE rep.tab (col1 NUMBER NOT NULL,
                      col2 NUMBER NOT NULL,
                      tstamp BINARY(8),
                      PRIMARY KEY (col1));

If no timestamp column is defined in the replicated table, timestamp comparison cannot be performed to detect conflicts. Instead, at each site, the value of a row in the database reflects the most recent update applied to the row, either by local applications or by replication.

Configuring the CHECK CONFLICTS clause

When configuring your replication scheme, you can set up timestamp comparison for a TABLE element by including a CHECK CONFLICTS clause in the table's element description in the CREATE REPLICATION statement.

Note:

A CHECK CONFLICT clause cannot be specified for DATASTORE elements.

The syntax of the CREATE REPLICATION statement is described in Oracle TimesTen In-Memory Database SQL Reference. Example 14-2 shows how CHECK CONFLICTS might be used when configuring your replication scheme.

Example 14-2 Automatic timestamp comparison

In this example, we establish automatic timestamp comparison for the bidirectional replication scheme defined in Example 9-29. The DSNs, west_dsn and east_dsn, define the westds and eastds databases that replicate the repl.accounts table containing the tstamp timestamp table. In the event of a comparison failure, discard the transaction that includes an update with the older timestamp.

CREATE REPLICATION r1
ELEMENT elem_accounts_1 TABLE accounts
  CHECK CONFLICTS BY ROW TIMESTAMP
    COLUMN tstamp
    UPDATE BY SYSTEM
    ON EXCEPTION ROLLBACK WORK
  MASTER westds ON "westcoast"
  SUBSCRIBER eastds ON "eastcoast"
ELEMENT elem_accounts_2 TABLE accounts
  CHECK CONFLICTS BY ROW TIMESTAMP
    COLUMN tstamp
    UPDATE BY SYSTEM
    ON EXCEPTION ROLLBACK WORK
  MASTER eastds ON "eastcoast"
  SUBSCRIBER westds ON "westcoast";

When bidirectionally replicating databases with conflict resolution, the replicated tables on each database must be set with the same CHECK CONFLICTS attributes. If you need to disable or change the CHECK CONFLICTS settings for the replicated tables, use the ALTER REPLICATION statement described in "Eliminating conflict detection" and apply to each replicated database.

Enabling system timestamp column maintenance

Enable system timestamp comparison by using:

CHECK CONFLICTS BY ROW TIMESTAMP
  COLUMN ColumnName
  UPDATE BY SYSTEM

TimesTen automatically maintains the value of the timestamp column using the current time returned by the underlying operating system. This is the default setting.

When you specify UPDATE BY SYSTEM, TimesTen:

Initializes the timestamp column to the current time when a new record is inserted into the table.
Updates the timestamp column to the current time when an existing record is modified.

During initial load, the timestamp column values should be left NULL, and applications should not give a value for the timestamp column when inserting or updating a row.

When you use the ttBulkCp or ttMigrate utility to save TimesTen tables, the saved rows maintain their current timestamp values. When the table is subsequently copied or migrated back into TimesTen, the timestamp column retains the values it had when the copy or migration file was created.

Note:

If you configure TimesTen for timestamp comparison after using the ttBulkCp or ttMigrate to copy or migrate your tables, the initial values of the timestamp columns remain NULL, which is considered by replication to be the earliest possible time.

Enabling user timestamp column maintenance

Enable user timestamp column maintenance on a table by using:

CHECK CONFLICTS BY ROW TIMESTAMP
  COLUMN ColumnName
  UPDATE BY USER

When you configure UPDATE BY USER, your application is responsible for maintaining timestamp values. The timestamp values used by your application can be arbitrary, but the time values cannot decrease. In cases where the user explicitly sets or updates the timestamp column, the application-provided value is used instead of the current time.

Replicated delete operations always carry a system-generated timestamp. If replication has been configured with UPDATE BY USER and an update/delete conflict occurs, the conflict is resolved by comparing the two timestamp values and the operation with the larger timestamp wins. If the basis for the user timestamp varies from that of the system-generated timestamp, the results may not be as expected. Therefore, if you expect delete conflicts to occur, use system-generated timestamps.

Reporting conflicts

TimesTen conflict checking may be configured to report conflicts to a human-readable plain text file, or to an XML file for use by user applications. This section includes the topics:

Reporting conflicts to a text file
Reporting conflicts to an XML file
Reporting uniqueness conflicts
Reporting update conflicts
Reporting delete/update conflicts
Suspending and resuming the reporting of conflicts

Reporting conflicts to a text file

To configure replication to report conflicts to a human-readable text file (the default), use:

CHECK CONFLICTS BY ROW TIMESTAMP
  COLUMN ColumnName
  ...
  REPORT TO 'FileName' FORMAT STANDARD

An entry is added to the report file FileName that describes each conflict. The phrase FORMAT STANDARD is optional and may be omitted, as the standard report format is the default.

Each failed operation logged in the report consists of an entry that starts with a header, followed by information specific to the conflicting operation. Each entry is separated by a number of blank lines in the report.

The header contains:

The time the conflict was discovered.
The databases that sent and received the conflicting update.
The table in which the conflict occurred.

The header has the following format:

Conflict detected at time on date
Datastore : subscriber_database
Transmitting name : master_database
Table : username.tablename

For example:

Conflict detected at 20:08:37 on 05-17-2004
Datastore : /tmp/subscriberds
Transmitting name : MASTERDS
Table : USER1.T1

Following the header is the information specific to the conflict. Data values are shown in ASCII format. Binary data is translated into hexadecimal before display, and floating-point values are shown with appropriate precision and scale.

For further description of the conflict report file, see "Reporting uniqueness conflicts", "Reporting update conflicts" and "Reporting delete/update conflicts".

Reporting conflicts to an XML file

To configure replication to report conflicts to an XML file, use:

CHECK CONFLICTS BY ROW TIMESTAMP
  COLUMN ColumnName
  ...
  REPORT TO 'FileName' FORMAT XML

Replication uses the base file name FileName to create two files. FileName.xml is a header file that contains the XML Document Type Definition for the conflict report structure, as well as the root element, defined as <ttrepconflictreport>. Inside the root element is an XML directive to include the file FileName.include, and it is to this file that all conflicts are written. Each conflict is written as a single element of type <conflict>.

For further description of the conflict report file XML elements, see "The conflict report XML Document Type Definition".

Note:

When performing log maintenance on an XML conflict report file, only the file FileName.include should be truncated or moved. For conflict reporting to continue to function correctly, the file FileName.xml should be left untouched.

Reporting uniqueness conflicts

A uniqueness conflict record is issued when a replicated insert fails because of a conflict.

A uniqueness conflict record in the report file contains:

The timestamp and values for the existing tuple, which is the tuple that the conflicting tuple is in conflict with.
The timestamp and values for the conflicting insert tuple, which is the tuple of the insert that failed.
The key column values used to identify the record.
The action that was taken when the conflict was detected (discard the single row insert or the entire transaction)

Note:
If the transaction was discarded, the contents of the entire transaction are logged in the report file.

The format of a uniqueness conflict record is:

Conflicting insert tuple timestamp : <timestamp in binary format>
Existing tuple timestamp : <timestamp in binary format>
The existing tuple :
<<column value> [,<column value>. ..]>
The conflicting tuple :
<<column value> [,<column value> ...]>
The key columns for the tuple:
<<key column name> : <key column value>>
Transaction containing this insert skipped
Failed transaction:
Insert into table <user>.<table> <<columnvalue> [,<columnvalue>...]>
End of failed transaction

Example 14-3 shows the output from a uniqueness conflict on the row identified by the primary key value, '2'. The older insert replicated from subscriberds conflicts with the newer insert in masterds, so the replicated insert is discarded.

Example 14-3 Output from uniqueness conflict

Conflict detected at 13:36:00 on 03-25-2002
Datastore : /tmp/masterds
Transmitting name : SUBSCRIBERDS
Table : TAB
Conflicting insert tuple timestamp : 3C9F983D00031128
Existing tuple timestamp : 3C9F983E000251C0
The existing tuple :
< 2, 2, 3C9F983E000251C0>
The conflicting tuple :
< 2, 100, 3C9F983D00031128>
The key columns for the tuple:
<COL1 : 2>
Transaction containing this insert skipped
Failed transaction:
Insert into table TAB < 2, 100, 3C9F983D00031128>
End of failed transaction

Reporting update conflicts

An update conflict record is issued when a replicated update fails because of a conflict. This record reports:

The timestamp and values for the existing tuple, which is the tuple that the conflicting tuple is in conflict with.
The timestamp and values for the conflicting update tuple, which is the tuple of the update that failed.
The old values, which are the original values of the conflicting tuple before the failed update.
The key column values used to identify the record.
The action that was taken when the conflict was detected (discard the single row update or the entire transaction).

Note:
If the transaction was discarded, the contents of the entire transaction are logged in the report file.

The format of an update conflict record is:

Conflicting update tuple timestamp : <timestamp in binary format>
Existing tuple timestamp : <timestamp in binary format>
The existing tuple :
<<column value> [,<column value>. ..]>
The conflicting update tuple :
TSTAMP :<timestamp> :<<column value> [,<column value>. ..]>
The old values in the conflicting update:
TSTAMP :<timestamp> :<<column value> [,<column value>. ..]>
The key columns for the tuple:
<<key column name> : <key column value>>
Transaction containing this update skipped
Failed transaction:
Update table <user>.<table> with keys:
<<key column name> : <key column value>>
New tuple value:
<TSTAMP :<timestamp> :<<column value> [,<column value>. ..]>
End of failed transaction

Example 14-4 shows the output from an update conflict on the col2 value in the row identified by the primary key value, '6'. The older update replicated from the masterds database conflicts with the newer update in subscriberds, so the replicated update is discarded.

Example 14-4 Output from an update conflict

Conflict detected at 15:03:18 on 03-25-2002
Datastore : /tmp/subscriberds
Transmitting name : MASTERDS
Table : TAB
Conflicting update tuple timestamp : 3C9FACB6000612B0
Existing tuple timestamp : 3C9FACB600085CA0
The existing tuple :
< 6, 99, 3C9FACB600085CA0>
The conflicting update tuple :
<TSTAMP :3C9FACB6000612B0, COL2 : 50>
The old values in the conflicting update:
<TSTAMP :3C9FAC85000E01F0, COL2 : 2>
The key columns for the tuple:
<COL1 : 6>
Transaction containing this update skipped
Failed transaction:
Update table TAB with keys:
<COL1 : 6>
New tuple value: <TSTAMP :3C9FACB6000612B0, COL2 : 50>
End of failed transaction

Reporting delete/update conflicts

A delete/update conflict record is issued when an update is attempted on a row that has more recently been deleted. This record reports:

The timestamp and values for the conflicting update tuple or conflicting delete tuple, whichever tuple failed.
If the delete tuple failed, the report also includes the timestamp and values for the existing tuple, which is the surviving update tuple with which the delete tuple was in conflict.
The key column values used to identify the record.
The action that was taken when the conflict was detected (discard the single row update or the entire transaction).

Note:
If the transaction was discarded, the contents of the entire transaction are logged in the report file. TimesTen cannot detect delete/insert conflicts.

The format of a record that indicates a delete conflict with a failed update is:

Conflicting update tuple timestamp : <timestamp in binary format>
The conflicting update tuple :
TSTAMP :<timestamp> :<<column value> [,<column value>. ..]>
This transaction skipped
The tuple does not exist
Transaction containing this update skipped
Update table <user>.<table> with keys:
<<key column name> : <key column value>>
New tuple value:
<TSTAMP :<timestamp> :<<column value> [,<column value>. ..]>
End of failed transaction

Example 14-5 shows the output from a delete/update conflict caused by an update on a row that has more recently been deleted. Because there is no row to update, the update from SUBSCRIBERDS is discarded.

Example 14-5 Output from a delete/update conflict: delete is more recent

Conflict detected at 15:27:05 on 03-25-2002
Datastore : /tmp/masterds
Transmitting name : SUBSCRIBERDS
Table : TAB
Conflicting update tuple timestamp : 3C9FB2460000AFC8
The conflicting update tuple :
<TSTAMP :3C9FB2460000AFC8, COL2 : 99>
The tuple does not exist
Transaction containing this update skipped
Failed transaction:
Update table TAB with keys:
<COL1 : 2>
New tuple value: <TSTAMP :3C9FB2460000AFC8,
COL2 : 99>
End of failed transaction

The format of a record that indicates an update conflict with a failed delete is:

Conflicting binary delete tuple timestamp : <timestamp in binary format>
Existing binary tuple timestamp : <timestamp in binary format>
The existing tuple :
<<column value> [,<column value>. ..]>
The key columns for the tuple:
<<key column name> : <key column value>>
Transaction containing this delete skipped
Failed transaction:
Delete table <user>.<table> with keys:
<<key column name> : <key column value>>
End of failed transaction

Example 14-6 shows the output from a delete/update conflict caused by a delete on a row that has more recently been updated. Because the row was updated more recently than the delete, the delete from masterds is discarded.

Example 14-6 Output from a delete/update conflict: update is more recent

Conflict detected at 15:27:20 on 03-25-2002
Datastore : /tmp/subscriberds
Transmitting name : MASTERDS
Table : TAB
Conflicting binary delete tuple timestamp : 3C9FB258000708C8
Existing binary tuple timestamp : 3C9FB25800086858
The existing tuple :
< 147, 99, 3C9FB25800086858>
The key columns for the tuple:
<COL1 : 147>
Transaction containing this delete skipped
Failed transaction:
Delete table TAB with keys:
<COL1 : 147>

Suspending and resuming the reporting of conflicts

Provided your applications are well-behaved, replication usually encounters and reports only sporadic conflicts. However, it is sometimes possible under heavy load to trigger a flurry of conflicts in a short amount of time, particularly when applications are in development and such errors are expected. This can potentially have a negative impact on the performance of the host because of excessive writes to the conflict report file and the large number of SNMP traps that can be generated.

To avoid overwhelming a host with replication conflicts, you can configure replication to suspend conflict reporting when the number of conflicts per second has exceeded a user-specified threshold. Conflict reporting may also be configured to resume once the conflicts per second have fallen below a user-specified threshold.

Conflict reporting suspension and resumption can be detected by an application by catching the SNMP traps ttRepConflictReportStoppingTrap and ttRepConflictReportStartingTrap, respectively. See "Diagnostics through SNMP Traps" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for more information.

To configure conflict reporting to be suspended and resumed based on the number of conflicts per second, use the CONFLICT REPORTING SUSPEND AT and CONFLICT REPORTING RESUME AT attributes for the STORE clause of a replication scheme.

If the replication agent is stopped while conflict reporting is suspended, conflict reporting is enabled when the replication agent is restarted. The SNMP trap ttRepConflictReportingStartingTrap is not sent if this occurs. This means that an application that monitors the conflict report suspension traps must also monitor the traps for replication agent stopping and starting.

If you set CONFLICT REPORTING RESUME AT to 0, reporting does not resume until the replication agent is restarted.

Example 14-7 demonstrates the configuration of a replication schemes where conflict reporting ceases when the number of conflicts exceeds 20 per second, and conflict reporting resumes when the number of conflicts drops below 10 per second.

Example 14-7 Configuring conflict reporting thresholds

CREATE REPLICATION r1
ELEMENT elem_accounts_1 TABLE accounts
      CHECK CONFLICTS BY ROW TIMESTAMP
        COLUMN tstamp
        UPDATE BY SYSTEM
        ON EXCEPTION ROLLBACK WORK
        REPORT TO 'conflicts' FORMAT XML
  MASTER westds ON "westcoast"
  SUBSCRIBER eastds ON "eastcoast"
ELEMENT elem_accounts_2 TABLE accounts
      CHECK CONFLICTS BY ROW TIMESTAMP
        COLUMN tstamp
        UPDATE BY SYSTEM
        ON EXCEPTION ROLLBACK WORK
        REPORT TO 'conflicts' FORMAT XML
  MASTER eastds ON "eastcoast"
  SUBSCRIBER westds ON "westcoast"
STORE westds ON "westcoast"
  CONFLICT REPORTING SUSPEND AT 20
  CONFLICT REPORTING RESUME AT 10
STORE eastds ON "eastcoast"
  CONFLICT REPORTING SUSPEND AT 20
  CONFLICT REPORTING RESUME AT 10;

The conflict report XML Document Type Definition

The TimesTen XML format conflict report is are based on the XML 1.0 specification (http://www.w3.org/TR/REC-xml). The XML Document Type Definition (DTD) for the replication conflict report is a set of markup declarations that describes the elements and structure of a valid XML file containing a log of replication conflicts. This DTD can be found in the XML header file, identified by the suffix .xml, that is created when replication is configured to report conflicts to an XML file. User applications which understand XML use the DTD to parse the rest of the XML replication conflict report. For more information on reading and understanding XML Document Type Definitions, see http://www.w3.org/TR/REC-xml.

<?xml version="1.0"?>
<!DOCTYPE ttreperrorlog [
    <!ELEMENT ttrepconflictreport(conflict*) >
    <!ELEMENT repconflict        (header, conflict, scope, failedtransaction) > 
    <!ELEMENT header             (time, datastore, transmitter, table) >
    <!ELEMENT time               (hour, min, sec, year, month, day) >
    <!ELEMENT hour               (#PCDATA) >
    <!ELEMENT min                (#PCDATA) >
    <!ELEMENT sec                (#PCDATA) >
    <!ELEMENT year               (#PCDATA) >
    <!ELEMENT month              (#PCDATA) >
    <!ELEMENT day                (#PCDATA) >
    <!ELEMENT datastore          (#PCDATA) >
    <!ELEMENT transmitter        (#PCDATA) >
    <!ELEMENT table              (tableowner, tablename) >
    <!ELEMENT tableowner          (#PCDATA) >
    <!ELEMENT tablename          (#PCDATA) >
    <!ELEMENT scope              (#PCDATA) >
    <!ELEMENT failedtransaction  ((insert | update | delete)+) >
    <!ELEMENT insert             (sql) >
    <!ELEMENT update             (sql, keyinfo, newtuple) >
    <!ELEMENT delete             (sql, keyinfo) >
    <!ELEMENT sql                (#PCDATA) >
    <!ELEMENT keyinfo            (column+) >
    <!ELEMENT newtuple           (column+) >
    <!ELEMENT column             (columnname, columntype, columnvalue) >
    <!ATTLIST column               
        pos CDATA #REQUIRED >
    <!ELEMENT columnname         (#PCDATA) >
    <!ELEMENT columnvalue        (#PCDATA) >
    <!ATTLIST columnvalue 
        isnull (true | false) "false">
    <!ELEMENT existingtuple       (column+) >
    <!ELEMENT conflictingtuple    (column+) >
    <!ELEMENT conflictingtimestamp(#PCDATA) >
    <!ELEMENT existingtimestamp   (#PCDATA) >
    <!ELEMENT oldtuple            (column+) >
    <!ELEMENT conflict            (conflictingtimestamp, existingtimestamp*,
                                   existingtuple*, conflictingtuple*, 
                                   oldtuple*, keyinfo*) > 
<!ATTLIST conflict
    type (insert | update | deletedupdate | updatedeleted) #REQUIRED>
<!ENTITY logFile                  SYSTEM "Filename.include">
]>
<ttrepconflictreport>
  &logFile;
</ttrepconflictreport>

The main body of the document

The .xml file for the XML replication conflict report is merely a header, containing the XML Document Type Definition that describes the report format and links to a file with the suffix .include. This include file is the main body of the report, containing each replication conflict as a separate element. There are three possible types of elements: insert, update and delete/update conflicts. Each conflict type requires a slightly different element structure.

The uniqueness conflict element

A uniqueness conflict occurs when a replicated insertion fails because a row with an identical key column was inserted more recently. See "Reporting uniqueness conflicts" for a description of the information that is written to the conflict report for a uniqueness conflict.

Example 14-8 illustrates the format of a uniqueness conflict XML element, using the values from Example 14-3.

Example 14-8 Uniqueness conflict element

<repconflict>
    <header>
     <time>
          <hour>13</hour>
          <min>36</min>
          <sec>00</sec>
          <year>2002</year> <month>03</month>
          <day>25</day>
      </time>
      <datastore>/tmp/masterds</datastore>
      <transmitter>SUBSCRIBERDS</transmitter>
      <table>
          <tableowner>REPL</tableowner>
          <tablename>TAB</tablename>
     </table>
   </header>
   <conflict type="insert">
     <conflictingtimestamp>3C9F983D00031128</conflictingtimestamp>
     <existingtimestamp>3C9F983E000251C0</existingtimestamp>
     <existingtuple>
         <column pos="1">
           <columnname>COL1</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>2</columnvalue>
         </column>
         <column pos="2">
           <columnname>COL2</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>2</columnvalue>
         </column>
           <columnname>TSTAMP</columnname>
           <columntype>BINARY(8)</columntype>
           <columnvalue>3C9F983E000251C0</columnvalue>
         </column>
      </existingtuple>
      <conflictingtuple>
         <column pos="1">
           <columnname>COL1</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>2</columnvalue>
        </column>
        <column pos="2">
           <columnname>COL2</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>100</columnvalue>
        </column>
        <column pos="3">
           <columname>TSTAMP</columnname>
           <columntype>BINARY(8)</columntype>
           <columnvalue>3C9F983D00031128</columnvalue>
        </column>
     </conflictingtuple>
     <keyinfo>
        <column pos="1">
          <columnname>COL1</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>2</columnvalue>
        </column>
    </keyinfo>
 </conflict>
 <scope>TRANSACTION</scope>
 <failedtransaction>
   <insert>
      <sql>Insert into table TAB </sql>
      <column pos="1">
         <columnname>COL1</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>2</columnvalue>
      </column>
      <column pos="2">
         <columnname>COL2</columnname>
         <columntype>NUMBER(38)</columntype>
        <columnvalue>100</columnvalue>
      </column>
      <column pos="3">
         <columnname>TSTAMP</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>3C9F983D00031128</columnvalue>
      </column>
    </insert>
  </failedtransaction>
</repconflict>

The update conflict element

An update conflict occurs when a replicated update fails because the row was updated more recently. See "Reporting update conflicts" for a description of the information that is written to the conflict report for an update conflict.

Example 14-9 illustrates the format of an update conflict XML element, using the values from Example 14-4.

Example 14-9 Update conflict element

<repconflict>
    <header>
       <time>
          <hour>15</hour>
          <min>03</min>
          <sec>18</sec>
          <year>2002</year>
          <month>03</month>
          <day>25</day>
      </time>
      <datastore>/tmp/subscriberds</datastore>
      <transmitter>MASTERDS</transmitter>
      <table>
         <tableowner>REPL</tableowner>
         <tablename>TAB</tablename>
      </table>
   </header>
   <conflict type="update">
      <conflictingtimestamp>
          3C9FACB6000612B0
      </conflictingtimestamp>
      <existingtimestamp>3C9FACB600085CA0</existingtimestamp>
      <existingtuple>
        <column pos="1">
          <columnname>COL1</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>6</columnvalue>
        </column>
        <column pos="2">
          <columnname>COL2</columname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>99</columnvalue>
        </column>
        <column pos="3">
          <columnname>TSTAMP</columnname>
          <columntype>BINARY(8)</columntype>
          <columnvalue>3C9FACB600085CA0></columnvalue>
        </column>
     </existingtuple>
     <conflictingtuple>
        <column pos="3">
          <columnname>TSTAMP</columnname>
          <columntype>BINARY(8)</columntype>
          <columnvalue>3C9FACB6000612B0</columnvalue>
        </column>
        <column pos="2">
          <columnname>COL2</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>50</columnvalue>
        </column>
    </conflictingtuple>
    <oldtuple>
        <column pos="3">
          <columnname>TSTAMP</columnname>
          <columntype>BINARY(8)</columntype>
          <columnvalue>3C9FAC85000E01F0</columnvalue>
       </column>
       <column pos="2">
          <columnname>COL2</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>2</columnvalue>
       </column>
   </oldtuple>
   <keyinfo>
       <column pos="1">
         <columnname>COL1</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>6</columnvalue>
       </column>
  </keyinfo>
</conflict>
<scope>TRANSACTION</scope>
<failedtransaction>
   <update>
      <<sql>Update table TAB</sql>
      <<keyinfo>
         <column pos="1">
           <columnname>COL1</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>6</columnvalue>
         </column>
      </keyinfo>
         <column pos="3">
           <columnname>TSTAMP</columnname>
           <columntype>BINARY(8)</columntype>
           <columnvalue>3C9FACB6000612B0</columnvalue>
         </column>
         <column pos="2">
           <columnname>COL2</columnname>
           <columntype>NUMBER(38)</columntype>
           <columnvalue>50</columnvalue>
         </column>
      </update>
   </failedtransaction>
</repconflict>

The delete/update conflict element

A delete/update conflict occurs when a replicated update fails because the row to be updated has already been deleted on the database receiving the update, or when a replicated deletion fails because the row has been updated more recently. See "Reporting delete/update conflicts" for a description of the information that is written to the conflict report for a delete/update conflict.

Example 14-10 illustrates the format of a delete/update conflict XML element in which an update fails because the row has been deleted more recently, using the values from Example 14-5.

Example 14-10 Delete/update conflict element: delete is more recent

<repconflict>
   <header>
       <time>
          <hour>15</hour>
          <min>27</min>
          <sec>05</sec>
          <year>2002</year>
          <month>03</month>
          <day>25</day>
       </time>
       <datastore>/tmp/masterds</datastore>
       <transmitter>SUBSCRIBERDS</transmitter>
       <table>
          <tableowner>REPL</tableowner>
          <tablename>TAB</tablename>
       </table>
   </header>
   <conflict type="update">
      <conflictingtimestamp>
          3C9FB2460000AFC8
      </conflictingtimestamp>
      <conflictingtuple>
        <column pos="3">
          <columnname>TSTAMP</columnname>
          <columntype>BINARY(8)</columntype>
          <columnvalue>3C9FB2460000AFC8</columnvalue>
        </column>
        <column pos="2">
          <columnname>COL2</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>99/columnvalue>
        </column>
     </conflictingtuple>
     <keyinfo>
        <column pos="1">
          <columnname>COL1</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>2</columnvalue>
        </column>
    </keyinfo>
  </conflict>
  <scope>TRANSACTION</scope>
  <failedtransaction>
     <update>
       <sql>Update table TAB</sql>
   <keyinfo>
       <column pos="1">
         <columnname>COL1</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>2</columnvalue>
       </column>
   </keyinfo>
       <column pos="3">
         <columnname>TSTAMP</columnname>
         <columntype>BINARY(8)</columntype>
         <columnvalue>3C9FB2460000AFC8</columnvalue>
       </column>
       <column pos="2">
         <columnname>COL2</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>99</columnvalue>
       </column>
    </update>
  </failedtransaction>
</repconflict>

Example 14-11 illustrates the format of a delete/update conflict XML element in which a deletion fails because the row has been updated more recently, using the values from Example 14-6.

Example 14-11 Delete/update conflict element: update is more recent

<repconflict>
   <header>
       <time>
          <hour>15</hour>
          <min>27</min>
          <sec>20</sec>
          <year>2002</year>
          <month>03</month>
          <day>25</day>
       </time>
       <datastore>/tmp/masterds</datastore>
       <transmitter>MASTERDS</transmitter>
       <table>
         <tableowner>REPL</tableowner>
         <tablename>TAB</tablename>
       </table>
   </header>
   <conflict type="delete">
       <conflictingtimestamp>
            3C9FB258000708C8
       </conflictingtimestamp>
       <existingtimestamp>3C9FB25800086858</existingtimestamp>
    <existingtuple>
       <column pos="1">
          <columnname>COL1</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>147</columnvalue>
       </column>
       <column pos="2">
          <columnname>COL2</columnname>
          <columntype>NUMBER(38)</columntype>
          <columnvalue>99</columnvalue>
       </column>
       <column pos="3">
          <columnname>TSTAMP</columnname>
          <columntype>BINARY(8)</columntype>
          <columnvalue>3C9FB25800086858</columnvalue>
       </column>
    </existingtuple>
    <keyinfo>
       <column pos="1">
         <columnname>COL1</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>147</columnvalue>
       </column>
    </keyinfo>
  </conflict>
  <scope>TRANSACTION</scope>
  <failedtransaction>
     <delete>
        <sql>Delete from table TAB</sql>
    <keyinfo>
       <column pos="1">
         <columnname>COL1</columnname>
         <columntype>NUMBER(38)</columntype>
         <columnvalue>147</columnvalue>
       </column>
      </keyinfo>
    </delete>
  </failedtransaction>
</repconflict>