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 or Linux 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.

TimesTen uses the time value returned by the system at the time the transaction applies each record 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 a transaction received from a master, the replication agent at the subscriber database performs timestamp resolution for insert, update, and delete operations as follows:

• When applying replicated INSERT operations:

  • If the timestamp of the transaction record to be applied is newer than the timestamp of the existing row, the existing row is overwritten.

  • If the timestamp of the transaction record and the stored record are equal, the insert operation is discarded.

  • If the timestamp of the transaction record is older than the timestamp of the stored record, the insert operation from the transaction is discarded.

  • If the stored row has been deleted, then no timestamp is available for comparison. A replicated insert operation on a previously deleted row is applied as an insert.

• When applying replicated UPDATE operations:

  • If the timestamp of the transaction record to be applied is newer than the timestamp of the stored record, TimesTen applies the update operation to the row.

  • If the timestamp of the transaction record and the stored record are equal, the update operation is discarded.

  • If the timestamp of the transaction record is older than the timestamp of the stored record, the update operation from the transaction is discarded.

  • If the stored row has been deleted, then no timestamp is available for comparison. Any replicated update operations designated for the deleted row are discarded.

  • An update operation that cannot find the updated row is considered a delete conflict, which is reported but cannot be resolved.

• When applying replicated DELETE operations:

  • If the timestamp of a replicated delete operation is newer than the timestamp of the existing row, the existing row is deleted.

  • If the timestamp of a replicated delete operation is older than the timestamp of the stored record (the row has been modified recently), the delete operation is rejected.

  Note:

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

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.