Ensuring that operations are applied in the correct order

To ensure that the replicated statements are applied in the correct order, the Guaranteed Message Delivery System uses the transaction log offsets of the publisher and subscriber databases. Each COMMIT is marked in the transaction log by a well-defined offset. The order of transactions can be determined by comparing their transaction log offset values.

Each message includes the following transaction log offsets:

The transaction log offset of the last COMMIT in the previous message. If a transaction spans several messages, there is a sequence number within the transaction to order the messages correctly. Ensuring that operations are applied in the correct order.
The transaction log offset of the last COMMIT in the message.

Message ordering

Sending messages

The log_sent column in the SYSREMOTEUSER system table holds the local transaction log offset for the last message sent to the subscriber.

The following describes how the SYSREMOTEUSER system tables are updated when messages are sent.

When the publisher SQL Remote Message Agent (dbremote) sends a message to a subscriber, it also sets the log_sent value to the transaction log offset value of the last COMMIT in the sent message.

For example, the publisher sends the following message to user1.
(0-0000923200-0000923357-0)
In the publisher's SYSREMOTEUSER system table, the publisher sets the log_sent value to 0000923357 for user1.
When the message is received and applied at the subscriber database, a confirmation is sent to the publisher. The confirmation includes the last transaction log offset that was applied by the subscriber database.

For example, the message confirms that user1 applied all of the transactions up to and including the transaction log offset 0000923357.
When the publisher SQL Remote Message Agent (dbremote) receives the confirmation, it sets the confirm_sent column to the value of the confirmation offset for the user in the SYSREMOTEUSER system table.

For example, the publisher sets the confirm_sent column to 0000923357 for user1 in the publisher's SYSREMOTEUSER system table.

Both the log_sent and confirm_sent values contain transaction log offsets of the publisher's transaction log. The confirm_sent value cannot be a later offset than log_sent value.

Receiving messages

The following describes how the SYSREMOTEUSER system tables are updated when messages are received.

When the SQL Remote Message Agent (dbremote) at a subscriber database receives and applies a replication update, it updates the log_received column in the SYSREMOTEUSER system table with the offset of the last COMMIT in the message.

For example, when the subscriber receives and applies the following message, the log_received value in the SYSREMOTEUSER system table is set to 0000923357.
(0-0000923200-0000923357-0)
The log_received column at any subscriber database contains a transaction log offset that exists in the publisher database transaction log.
When the operations are received and applied, the subscriber SQL Remote Message Agent (dbremote) sets the confirm_received value in its SYSREMOTEUSER system table, and then sends confirmation to the publisher database.