- Code Module Replication Setup
- Replicating Schema Changes
- Replicating Indexed Views as Tables
- Replicating Execution of Stored Procedures
- Replicating Execution of Stored Procedures within a Serializable Transaction
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replicating Execution of Stored Procedures within a Serializable Transaction
You can configure stored procedure articles to replicate only if they’re executed within a serializable transaction; this requires two conditions to be met before the execution of the procedure is propagated to the subscriber:
- Transaction isolation level of the connection executing the stored procedure must be set to SERIALIZABLE.
- You must enclose the execution of the stored procedure within an explicit transaction using BEGIN TRANSACTION / COMMIT TRANSACTION statements.
If either of those criteria is not met, the execution of the stored procedure is not replicated. In addition to satisfying the above criteria, you should also use the SET XACT_ABORT ON option. This option ensures that the transaction encapsulating the execution of the stored procedure is automatically rolled back if any run-time errors are encountered.
Replicating the execution of a stored procedure within a serializable transaction is a recommended option if you need to ensure data consistency on publisher and subscribers. Why? Each stored procedure could include multiple implicit or explicit transactions; you might encounter situations when some transactions within the stored procedure succeed and others fail. If you advise SQL Server to replicate every execution of the stored procedure, even those executions in which transactions failed will be forwarded to the subscriber. Serializable isolation, which is the most restrictive isolation level, ensures that locks are held on all tables affected by the stored procedure until you commit the transaction. Therefore, replicating only the executions within a serializable transaction enables you to guarantee that a procedure completes its work successfully on the publisher before it is sent to subscribers.
Let’s use the update_factFinance procedure again to demonstrate how you can replicate its execution within a serializable transaction. The following execution will be replicated:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET XACT_ABORT ON BEGIN TRAN EXEC update_factFinance 1.10, 3, 32 COMMIT
However, the following command will not be replicated because it is not enclosed in an explicit transaction:
EXEC update_factFinance 1.10, 3, 32
Now we intentionally break the execution of the procedure to demonstrate the significance of the XACT_ABORT setting. I change the amount column of the factFinance table to have the SMALLINT data type instead of INT by executing the following statement:
ALTER TABLE factFinance ALTER COLUMN amount SMALLINT
The largest value supported by the SMALLINT data type is 32768; multiplying the maximum value of the amount column by 1.15 will result in a value greater than 32768, so the following execution of the update_factFinance stored procedure will generate an error:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE /* CORRECT setting*/ SET XACT_ABORT ON BEGIN TRAN EXEC update_factFinance 1.15, 3, 32 COMMIT
The result is the following:
Msg 8115, Level 16, State 2, Procedure update_factFinance, Line 10 Arithmetic overflow error converting expression to data type smallint.
The transaction is rolled back, and this execution of the stored procedure is not sent to the subscriber.
Next, I execute the same set of commands, except with an incorrect setting of XACT_ABORT:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE /* INCORRECT setting! For demonstration purposes ONLY! */ SET XACT_ABORT OFF BEGIN TRAN EXEC update_factFinance 1.15, 3, 32 COMMIT
The execution of the procedure fails, generating the same error as before. However if I check the distribution database now, I will find the following command sent to the subscriber:
{call "dbo"."update_factFinance" (1.15,3,32)}
The execution of the procedure is replicated to the subscriber even though its execution failed on the publisher. Doing so could cause the Distribution agent to fail. More importantly, it could cause inconsistent data values on publisher and subscriber. So always use SET XACT_ABORT ON when replicating the execution of stored procedures.