March 4, 2009 at 3:03 pm
I've been running profiler, and reading to determine if my understanding is correct, hopefully someone can confirm or correct me.
Say a 3 different client application do the following independent transactions
CLIENT1 -- INSERT INTO CUSTOMERS
CLIENT 2 --DELETE FROM ITEMS
CLIENT 3 --UPDATE PRODUCTS
Then replication Log Reader Agent reads the T-Log and transfers these changes to the distribution database as one chunk.
Now the Distribution agent picks up these three independent changes and goes to apply them to the subscriber database and does the following
BEGIN TRANSACTION
exec sp_msIns_Customers ....
exec sp_msDel_Items ...
exec sp_msUpd_Products...
IF @@Tran count > 0 COMMIT
then the connection issues another BEGIN TRANSACTION for the next sync
OK, so this is how I seem to see the process working. I'm wanting to know if locks on all of those stored procedures above are held until the COMMIT is issued. Meaing is there a risk that locks would be held for a long time if the batch size of changes is very large and a reader might have to wait for a shared lock on the customer table or index while the product table /indexes are updated?. I found that the distribution agent property -CommitBatchSize controls the amount of transactions before a commit is issued.
March 5, 2009 at 10:30 am
SQL Dude (3/4/2009)
I've been running profiler, and reading to determine if my understanding is correct, hopefully someone can confirm or correct me.Say a 3 different client application do the following independent transactions
CLIENT1 -- INSERT INTO CUSTOMERS
CLIENT 2 --DELETE FROM ITEMS
CLIENT 3 --UPDATE PRODUCTS
Then replication Log Reader Agent reads the T-Log and transfers these changes to the distribution database as one chunk.
Now the Distribution agent picks up these three independent changes and goes to apply them to the subscriber database and does the following
BEGIN TRANSACTION
exec sp_msIns_Customers ....
exec sp_msDel_Items ...
exec sp_msUpd_Products...
IF @@Tran count > 0 COMMIT
then the connection issues another BEGIN TRANSACTION for the next sync
OK, so this is how I seem to see the process working. I'm wanting to know if locks on all of those stored procedures above are held until the COMMIT is issued. Meaing is there a risk that locks would be held for a long time if the batch size of changes is very large and a reader might have to wait for a shared lock on the customer table or index while the product table /indexes are updated?. I found that the distribution agent property -CommitBatchSize controls the amount of transactions before a commit is issued.
If the transactions are "independent" on the primary they must be independent on the replica ( without getting into special configuration changes )
They are all applied by lsn number order ( once again without getting into special config changes)
If you have three clients to will get three separated transactions ( without special connection management sp_bindtoken etc ...)
Now if you wrap all of those in a SINGLE transaction you will get that on the replica.
The commitBatchSize is usefull when *MANY* rows are changed because then each UPD, DEL, INS is transformed into multiple calls to each repl proc on the replica and are applied using that commitBatchSize limit.
Hope it helps
* Noel
March 5, 2009 at 10:43 am
I appreciate the post. I thought I had seen only one commit per sync in the profiler trace making me think that each sync was appling all the unique transations in one sync from the publisher. maybe I'm mistaken I guess I need to look a little deeper into this.
March 5, 2009 at 12:47 pm
Note that if it all happened in one transaction you will see one commit!
There are thresholds SEPARATED for "commands" and "transactions"
From BOL:
-CommitBatchSize commit_batch_size
Is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.
-CommitBatchThreshold commit_batch_threshold
Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.
* Noel
March 5, 2009 at 12:56 pm
There is a case in which you get the behavior you described.
1. If there are *no* EXPLICIT calls to BEGIN TRAN
2. AND you are using autocommit mode
3. AND the calls affect a number of rows less than the limit on these parameters (like one row at a time)
Then they *DO* get GROUPED at the thresholds specified in these parameters.
* Noel
March 5, 2009 at 12:59 pm
Interesting, so I may not be crazy! So, where is the autocommit mode configured, replication is configured with all the default settings except a few agent properties to poll a little more frquently.
March 5, 2009 at 1:01 pm
Replication honors transactional boundries, and the values for commitbatchsize and commitbatchthreshold dictate the number of transactions that are delivered to a subscriber.
How it works is NOT very well documented in BOL. My blog on the subject should clear things up:
http://kendalvandyke.blogspot.com/2008/11/how-commitbatchsize-and.html
March 5, 2009 at 1:09 pm
Kendal Van Dyke (3/5/2009)
Replication honors transactional boundries, and the values for commitbatchsize and commitbatchthreshold dictate the number of transactions that are delivered to a subscriber.How it works is NOT very well documented in BOL. My blog on the subject should clear things up:
http://kendalvandyke.blogspot.com/2008/11/how-commitbatchsize-and.html
Kendal,
NICE BLOG!
* Noel
March 5, 2009 at 1:13 pm
VERY NICE!
So according to your 3rd point below on your BLOG if those three update statements are seperate clients on seperate tables not related to each other they could be grouped into a joint transaction depending upon the batch thresholds therefore holding locks during the transaction on objects.
What happens if I issue three update statements: the first affecting 600 rows, the second affecting 500 rows, and the third affecting 400 rows?
Answer: Because CommitBatchThreshold is set to 1,000 we’ll see 2 transactions at the subscriber. The first transaction will contain 1,100 statements and the second transaction will contain 400 statements.
March 5, 2009 at 1:19 pm
March 5, 2009 at 1:23 pm
Perfect, they are. That makes things more clear now. I was tracking down why replication was causing some blocking on index objects that were not related to one of the three data changes on the subscriber. It looked like they were being applied as a batch so until all the indexes in the batch (transaction) were updated locks were being held blocking readers. If the changes were applied in three seperate transactions then I don't see those blocking issues or deadlocks.
March 5, 2009 at 1:46 pm
Note that there is also one exception that is related with the "SubscriptionStreams".
Which introduces more uncertainty on the predictability of the order of the transactions... I believe this was done mainly to reduce latency on high transaction systems.
* Noel
March 5, 2009 at 1:48 pm
SQL Dude (3/5/2009)
Perfect, they are. That makes things more clear now. I was tracking down why replication was causing some blocking on index objects that were not related to one of the three data changes on the subscriber. It looked like they were being applied as a batch so until all the indexes in the batch (transaction) were updated locks were being held blocking readers. If the changes were applied in three seperate transactions then I don't see those blocking issues or deadlocks.
You can set RCSI to "ON" on the Subscriber Database to workaround most deadlock issues.
* Noel
March 5, 2009 at 1:51 pm
Thanks to you both!
RSCI? I'm not mentally processing the abbreviation
March 5, 2009 at 2:30 pm
SQL Dude (3/5/2009)
Thanks to you both!RSCI? I'm not mentally processing the abbreviation
OOps sorry!
RCSI = Read Committed Snapshot Isolation
You set it with:
ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;
Note: You will need exclusive access to do it!
* Noel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply