Microsoft KB Archive/240025

= INF: Rollback Transaction in Triggers at Subscriber Can Break Transactional Integrity =

Article ID: 240025

Article Last Modified on 11/26/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q240025



SUMMARY
Triggers are sometimes added at subscriber to enforce additional business rules that are not enforced on the publisher. This addition may break transactional integrity with replication if the triggers cause a ROLLBACK, because the distribution task uses batching of transactions to enhance performance. SQL Server Documentation says that the entire batch is cancelled when a ROLLBACK TRANSACTION is encountered in a trigger. See SQL Server Version 7.0 Books Online under the topic "Rollbacks in Stored Procedures and Triggers," or SQL Server 6.5 Books Online under the topic "Triggers and Rollback Transaction" for more details.



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.

Keywords: kbinfo KB240025

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.