MORE INFORMATION
SQL Server replication makes use of batching of transactions for reducing network roundtrips, thereby enhancing performance. If triggers that can result in
ROLLBACK are added at the subscriber, batches of transactions are cancelled. This is not an error condition and the distribution agent does not fail when this occurs. Because a batch may contain commands from multiple transactions or part of a large transaction at the publisher, it can result in compromising transactional integrity. The following sections provide details on each of the preceding scenarios.
Multiple Transactions Applied in a Single Batch
Consider the case where multiple transactions at the publisher get applied as a single transaction at the subscriber. This can result in a batch consisting of commands from more than one job. In such cases, when a
ROLLBACK TRANSACTION command is encountered in a trigger on any statement in the batch, the entire transaction is rolled back. This can result in some other transactions that were not part of the offending transaction getting rolled back due to batching. Because the entire batch is cancelled, the other transactions in the batch following the command that caused the
ROLLBACK are not executed either. This may be perceived as missing records or transactions at the subscriber. This is illustrated by the following example:
At the Publisher, transactions T1, T2 and T3 were committed.
Logreader inserted these three transactions into the Distribution
database.
Distribution batches these three jobs into a single batch and
assuming the command in T2 caused a ROLLBACK, the transaction
that began the batch would be rolled back causing T1 to be rolled
back. Since the ROLLBACK command results in the rest of the batch
being cancelled, T3 is also lost.
Subscriber is out of sync with the Publisher.
Single Transaction Applied in Multiple Batches at Subscriber
On the other hand, a batch may only contain a part of the transaction if it has a large number of commands. If a
ROLLBACK TRANSACTION is encountered, this batch is cancelled and the transaction is rolled back. Note that the distribution agent uses implicit transactions and, therefore, the next statement starts a new transaction. The remaining commands in the job get executed in the next batch as a part of this transaction and may commit later. This behavior can result in a partial transaction being committed at the subscriber, thereby breaking transactional integrity. This is explained in the following example:
At the Publisher, a single transaction T1 consisting of 20 commands was
committed. Since this is a single transaction, all 20 commands
should be committed in total at the Subscriber, to preserve
transactional integrity.
Logreader inserted this transaction and inserts into the Distribution
database the 20 commands for this transaction.
Distribution processes this transaction and assuming 10 commands fit
into a single batch, sends the first 10 commands. Assuming the 5th
command in this transaction caused a ROLLBACK, the transaction
that began the batch would be rolled back causing commands 1 thru 5
to be rolled back. Since the ROLLBACK command results in the rest of
the batch being cancelled, commands 6-10 are also lost. When the
Distribution agent continues, it sends commands 11-20 in the next
batch and since the autocommit option is OFF, this begins a new
transaction and continues to completion, causing commands 11-20 to
be committed, resulting in partial transaction to be committed at
the subscriber.
Subscriber is out of sync with the Publisher.
The distribution process proceeds with the remaining commands if there is no error returned by the server. Because the
ROLLBACK command is not an error condition, unless the trigger explicitly raises an error using a
RAISERROR statement, the distribution task proceeds with the remaining jobs in the distribution database. If a
RAISERROR is encountered, the distribution task fails with the raised error. If you have to use a
ROLLBACK statement in triggers at the subscriber, it should be followed by a
RAISERROR statement with the proper severity level for the distribution task to fail. Also, after the
RAISERROR, you should add a
RETURN to ensure that SQL Server does not process other statements in the trigger. For help on using
RAISERROR, see SQL Server Books Online under the topic "RAISERROR."
Note that this is not a problem with replication. It is the handling of a
ROLLBACK command inside a trigger that affects the replication process.
Microsoft recommends that you do not use a
ROLLBACK TRANSACTION in triggers at the subscriber. Instead, use Custom Stored Procedures to enforce additional business rules at the subscriber. Custom Stored Procedures can handle more complex logic and not apply the transaction at all, instead of rolling it back.