- Replicating Non-Table Articles
- Setup
- Synchronizing Replicated Code Modules
- Stored Procedure Execution Options
- Executing Procedures Inside Serializable Transactions
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Executing Procedures Inside Serializable Transactions
Recall that you can replicate each execution of the stored procedure or only those executions that occur within a serializable transaction. Serializable transactions require (not surprisingly) that the transaction isolation level be set to SERIALIZABLE.
NOTE
Please refer to my article called "SQL Server: Details of Locking" to learn about various transaction isolation levels supported by SQL Server.
If we replicate each execution of the stored procedure, SQL Server will attempt replicating even those executions that error out on the publisher. For example, suppose that I execute the populate_discounts procedure with an incorrect store id:
EXEC populate_discounts 'special special discount', 134567, 100, 1000, 25
Results:
Server: Msg 547, Level 16, State 1, Procedure populate_discounts, Line 10 INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__discounts__stor___0F975522'. The conflict occurred in database 'pubs', table 'stores', column 'stor_id'. Server: Msg 50000, Level 16, State 1, Procedure populate_discounts, Line 24 did not work, please try again The statement has been terminated.
Even if the execution of the procedure has failed on the publisher, SQL Server still attempts to execute it on the subscriber. The sp_browsereplcmds procedure executed against the distribution database reports the following:
{call "dbo"."populate_discounts" ('special special discount',134567,100,1000,25)}
Incidentally, this stored procedure call also fails on the subscriber server because store id = 134567 does not exist on the subscribed database, either.
Now let's see what happens if we choose to replicate only those executions of the procedure that are enclosed within a SERIALIZABLE transaction. Keep in mind that you'll have to drop the subscription as well as the populate_discounts article from the publication before you can configure this procedure to replicate only when executed within a serializable transaction. After you have configured replication, let's attempt to execute the following:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRAN
EXEC populate_discounts 'fabulous customer special', 114251, 50, 1000, 25
IF @@ERROR <>0
BEGIN
ROLLBACK
END
ELSE
COMMIT
This execution fails because store id = 114251 does not exist. If you check the distribution database, this command is never considered for replication. The log reader agent simply reports that no replicated transactions are available. Now let's execute the same procedure with a proper store id:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRAN
EXEC populate_discounts 'customer appreciation special', 7066, 50, 1000, 25
IF @@ERROR <>0
BEGIN
ROLLBACK
END
ELSE
COMMIT
This execution works on the publisher and is replicated to the subscriber. So replicating the execution of procedures only within a serializable transaction guarantees that they will be executed on the subscriber only if they succeed on the publisher. Keep in mind, however, that the SERIALIZABLE isolation level is the most restrictive locking mode and can have a negative performance impact on your application.