Configuring Timestamp Comparison
You can set up your replication scheme for 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 eachTABLE
element in theCREATE 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. This example shows that the
rep.tab
table contains a column
named tstamp
of type
BINARY(8)
to hold the timestamp
value.
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.
For more details on the syntax, see CREATE REPLICATION statement and CHECK CONFLICTS
clause in the Oracle TimesTen In-Memory Database SQL
Reference. The following example shows how CHECK
CONFLICTS
might be used when configuring your replication scheme.
In this example, we establish automatic timestamp comparison for the bidirectional replication scheme. 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 in a Classic Replication Scheme
and apply to each replicated database.
Enabling System Timestamp Column Maintenance
You can enable system timestamp comparison.
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
You can enable user timestamp column maintenance on a table.
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.