The scenario

Let’s take “Table A” as an example, this table has five columns “A, B, C, D, E”. Table A is replicated to a subscriber via an updatable subscription, allowing for bidirectional data changes – in this case, the type is queued updating.

Inside a single transaction on the subscriber, the following actions occur:

1. UPDATE TableA SET A = '', B = '' WHERE PK = 1
2. An AFTER UPDATE trigger executes:
   • UPDATE TableA SET C = 'I''m a trigger' WHERE PK = 1
3. UPDATE TableA SET D = '', E = '' WHERE PK = 1

In total, the same row is successfully updated three times.

The ordering oddity comes when the queue worker thread is applying those commands on the publisher.

A profiler trace of the queue worker shows command 2 (originating from the trigger) being attempted first under @execution_mode “QFirstPass”. An update conflict is detected due to command 2 having the “old_msrepl_tran_version” of command 1, not the value of the current row on the publisher and this not matching. As a result, subsequent profiler rows shows the three commands being ran with @execution_mode “QPubWins”.

Confusion ensues..

Opus 4.8 suggests the following queue ordering is taking place on the subscriber. This is such that the nested UPDATE of C is queued before the update of columns A & B.

S1: UPDATE A,B                      -- version stamped V0→V1 as part of the statement
  └─ T_user fires (NFR)
    └─ UPDATE C (S2)                -- version stamped V1→V2
      └─ T_sync(S2)           ENQUEUE C (old=V1) ← written to queue FIRST
  └─ T_sync(S1)               ENQUEUE A,B (old=V0) ← written SECOND
S3: UPDATE D,E                      -- version stamped V2→V3
  └─ T_sync(S3)               ENQUEUE D,E (old=V2) ← written THIRD

The execution of T_sync for S1 coming after the nested user trigger wouldn’t conform with Microsoft’s documentation of replication triggers which must be set as the first in ordering and are so by default.

Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger is the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table is included in a subscription, sp_settriggerorder returns an error.

The above suggests that the command Is queued in the incorrect order to begin with but what about a scenario where they’re queued in the correct order, but the queue worker thread is obtaining them in the wrong order for processing due to ordering ambiguity?