October 12, 2012 at 8:14 am
In a scenario where a transactional replication is configured (with no updateable subscriptions) and not for replication is used for every constraints (triggers, check constraints, foreign keys, identity)
If on the publisher I have the following transaction:
BEGIN TRANSACTION
Insert Table 1 VALUES (x,y,z, etc.)
Insert Table 2 VALUES (x,y,z, etc.)
COMMIT
Will the transaction still be atomic all the way up to the subscriber side? Even with a crash whatever where it occurs (publisher, distributor, network, subscriber etc)
Either Table 1 and Table 2 is there at the same time or none of them (just like a normal transaction atomicity) on the subscriber?
Ty
October 12, 2012 at 8:27 am
This is what I've found so far:
http://msdn.microsoft.com/en-us/library/ms151762%28v=sql.105%29.aspx
By default, transactional replication propagates changes according to transaction boundaries. If transactions are smaller, it is less likely that the Distribution Agent will have to resend a transaction due to network issues. If the agent is required to resend a transaction, the amount of data sent is smaller.
Which leave me the idea that atomicity is respected.
October 12, 2012 at 8:32 am
Megistal (10/12/2012)
This is what I've found so far:http://msdn.microsoft.com/en-us/library/ms151762%28v=sql.105%29.aspx
By default, transactional replication propagates changes according to transaction boundaries. If transactions are smaller, it is less likely that the Distribution Agent will have to resend a transaction due to network issues. If the agent is required to resend a transaction, the amount of data sent is smaller.
Which leave me the idea that atomicity is respected.
You are correct. Provided the articles updated are replicated by the same publication (and log reader) the transactions will be delivered in transactional order.
In this example if the second statement fails to be delivered at the subscriber, the whole transaction will be rolled back and retried on next delivery. All subsequent transactions will wait until this statement succeeds effectively halting replication
October 12, 2012 at 8:39 am
Thank you MysteryJimbo
October 12, 2012 at 9:18 am
Its worth pointing out that if there are more transactions to be delivered they will be delivered up to the maximum batchsize/commands setting on the distribution agent. If any of the transactions in the batch fail, the whole batch will be rolled back and retried.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply