February 17, 2010 at 9:04 am
Our setup is SQL 2005 running Transactional replication continously
I want to make sure I understand this correctly...
By default replication preserves transactional boundries from the publisher. So, any modifications wrapped into a BEGIN TRANSACTION will be applied on the subscriber in that fashion unless I modify the setting on the LOG READER to break this up.
By default the distribution agent will aply 100 transactions in one sync before a COMMIT is issued. Unless I modify the -CommitBatchSize property of the Distribution Agent?
Assume the follwing happens on the Publisher.
PUBLISHER:
1. Transaction 1 updates TABLE 1
(COMMIT)
2. Transaction 2 updates TABLE 2
(COMMIT)
3. Transaction 3 updates TABLE 3
(COMMIT)
4. Log reader agent pushes these to the distribtution db
5. Distribution agent picks up the changes.
6. On the Subsriber because of the -CommitBatchSize of 100 all three transactions will be applied before a COMMIT is issued correct?
In that situation you could have long locks being held right? So until all transactions are completed locks will remain in place?
February 17, 2010 at 9:37 am
It depends on the activity, check this out.
To answer your question, yes it will hold lock on the objects commit is issued.
http://www.sql-server-performance.com/tips/transactional_replication_p2.aspx
EnjoY!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply