July 21, 2008 at 9:37 am
Hi there,
I am issuing a DELETE followed by an INSERT..SELECT statement within a transaction which affects over 1,000,000 rows at the publisher. I do not want to see any of these rows at the subscriber until the last one has been written to the database. Is there any way to achieve this?
I have set SNAPSHOT ISOLATION at the subscriber which prevents locking of the subscriber tables, but when the transaction gets committed at the publisher it takes 10-15 minutes for the transactions to dribble into the subscriber. During this time the data is "inconsistent" because the data was affected as a set.
For example if I query the table at the publisher I will either be blocked until the transaction has committed or I get to see all the data that has been changed. At the subscriber it's a different story. I can see part of the INSERT..SELECT statement as rows are being applied at the subscriber as they are applied as singletons. How can I stop this? I still want optimistic concurrency with the SNAPSHOT ISOLATION but I don't want to see partial data sets until the last row has made it to the subscriber for that transaction.
I hope that's clear! 🙂
July 21, 2008 at 1:44 pm
What modifications to the default replication have you done? I assume you are using transactional replication.
By default, transactional replication will, as you have said, translate an update to 1000 rows into 1000 individual updates (or even inserts and updates) on the subscriber. However, by default replication will do all of these updates in a single transaction. So, you should not be able to see any of them until the final update is committed. There is some configuration available for these options, so you may have inadvertantly made a change to them.
July 22, 2008 at 1:52 am
Hi Michael, thanks for your response.
I set up the transactional publication using stored procedures like this (I declared some variables higher in the script - this is a snippet):
-- create an entry in sysservers for the distributor
print 'Adding distributor'
exec sp_adddistributor
@distributor = @publisherInstName,
@password = N''
-- create a new distribution database if one does not exist
print 'Creating new distribution database'
exec sp_adddistributiondb
@database = N'distribution',
@data_file_size = 300,
@log_file_size = 150,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
print 'Configuring publisher to point to distribution database'
exec sp_adddistpublisher
@publisher = @publisherInstName,
@distribution_db = N'distribution',
@security_mode = 1,
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
-- Configure the database for replication
print 'Setting up database for replication'
exec sp_replicationdboption
@dbname = @publisherDbName,
@optname = N'publish',
@value = N'true'
I then set up the articles like this:
exec sp_addarticle @publication = @publicationName,
@article = N'' '',
@source_owner = N''reporting'',
@source_object = N'' '',
@type = N''logbased'',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N''drop'',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N''none'',
@destination_table = N'' '',
@destination_owner = N''reporting'',
@status = 24,
@vertical_partition = N''false'',
@ins_cmd = N''CALL [sp_MSins_reporting ]'',
@del_cmd = N''CALL [sp_MSdel_reporting ]'',
@upd_cmd = N''SCALL [sp_MSupd_reporting ]''
The subscribers are set up like this (at the publisher as they're push subscriptions):
exec sp_addsubscription
@publication = ''MyTransactionalPublication'',
@subscriber = @subscriberServer,
@destination_db = @subscriberDb,
@subscription_type = N''Push'',
@sync_type = N''replication support only'',
@article = N''all'',
@update_mode = N''read only'',
@subscriber_type = 0
exec sp_addpushsubscription_agent
@publication = ''MyTransactionalPublication'',
@subscriber = @subscriberServer,
@subscriber_db = @subscriberDb,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@enabled_for_syncmgr = N''False''
This morning I read through BOL looking for an option that would lock the rows being written at the subscriber the same way they were locked at the publisher but I didn't see anything. Can you remember the option? Maybe this isn't even possible?
Thanks for reading.
July 23, 2008 at 12:17 pm
keymoo (7/22/2008)
Hi Michael, thanks for your response.I set up the transactional publication using stored procedures like this (I declared some variables higher in the script - this is a snippet):
-- create an entry in sysservers for the distributor
print 'Adding distributor'
exec sp_adddistributor
@distributor = @publisherInstName,
@password = N''
-- create a new distribution database if one does not exist
print 'Creating new distribution database'
exec sp_adddistributiondb
@database = N'distribution',
@data_file_size = 300,
@log_file_size = 150,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
print 'Configuring publisher to point to distribution database'
exec sp_adddistpublisher
@publisher = @publisherInstName,
@distribution_db = N'distribution',
@security_mode = 1,
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
-- Configure the database for replication
print 'Setting up database for replication'
exec sp_replicationdboption
@dbname = @publisherDbName,
@optname = N'publish',
@value = N'true'
I then set up the articles like this:
exec sp_addarticle @publication = @publicationName,
@article = N'' '',
@source_owner = N''reporting'',
@source_object = N'' '',
@type = N''logbased'',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N''drop'',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N''none'',
@destination_table = N'' '',
@destination_owner = N''reporting'',
@status = 24,
@vertical_partition = N''false'',
@ins_cmd = N''CALL [sp_MSins_reporting ]'',
@del_cmd = N''CALL [sp_MSdel_reporting ]'',
@upd_cmd = N''SCALL [sp_MSupd_reporting ]''
The subscribers are set up like this (at the publisher as they're push subscriptions):
exec sp_addsubscription
@publication = ''MyTransactionalPublication'',
@subscriber = @subscriberServer,
@destination_db = @subscriberDb,
@subscription_type = N''Push'',
@sync_type = N''replication support only'',
@article = N''all'',
@update_mode = N''read only'',
@subscriber_type = 0
exec sp_addpushsubscription_agent
@publication = ''MyTransactionalPublication'',
@subscriber = @subscriberServer,
@subscriber_db = @subscriberDb,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@enabled_for_syncmgr = N''False''
This morning I read through BOL looking for an option that would lock the rows being written at the subscriber the same way they were locked at the publisher but I didn't see anything. Can you remember the option? Maybe this isn't even possible?
Thanks for reading.
The parameter that you are looking for is SET in the "LogReader" which can split the transactions using -MaxCmdInTran
* Noel
July 23, 2008 at 2:08 pm
keymoo (7/21/2008)
I am issuing a DELETE followed by an INSERT..SELECT statement within a transaction which affects over 1,000,000 rows at the publisher. I do not want to see any of these rows at the subscriber until the last one has been written to the database. Is there any way to achieve this?
Well, Snapshot Replication could probably do it. That might be a bit too brutal though.
You might also want to look at the "Replicate Procs" option...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 9, 2008 at 8:27 am
noeld (7/23/2008)The parameter that you are looking for is SET in the "LogReader" which can split the transactions using -MaxCmdInTran
Thanks but from BOL it says the following:
-MaxCmdsInTran number_of_commands
Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.
Take note of the section in bold. I have left this setting as the default but I am seeing rows applied at the subscriber before the transaction has fully committed. Maybe there's a gap in my understanding, and I thought I had understood it well - could someone explain why I am not seeing the expected behaviour?
September 9, 2008 at 2:12 pm
keymoo (9/9/2008)
noeld (7/23/2008)The parameter that you are looking for is SET in the "LogReader" which can split the transactions using -MaxCmdInTran
Thanks but from BOL it says the following:
-MaxCmdsInTran number_of_commands
Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.
Take note of the section in bold. I have left this setting as the default but I am seeing rows applied at the subscriber before the transaction has fully committed. Maybe there's a gap in my understanding, and I thought I had understood it well - could someone explain why I am not seeing the expected behaviour?
Are you 100% sure that those DELETE + INSERT are happening in ONE transaction ?
* Noel
September 9, 2008 at 5:53 pm
keymoo (9/9/2008)
...but I am seeing rows applied at the subscriber before the transaction has fully committed.
Hmm, I note that "seeing rows applied at the subcriber" is not necessarily in conflict with "preserving transactional boundaries", as long as the Commits/Rollbacks occur at the same places.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2008 at 7:41 am
rbarryyoung (9/9/2008)
keymoo (9/9/2008)
...but I am seeing rows applied at the subscriber before the transaction has fully committed.Hmm, I note that "seeing rows applied at the subcriber" is not necessarily in conflict with "preserving transactional boundaries", as long as the Commits/Rollbacks occur at the same places.
Good Point. The isolation level of the transactions used to "read" the data are the ones that dictate what you can or can not "see".
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply