Transaction handling in federated tables via stored proc

[GOPIKANTH GOVINDARAJ]

I am using Stored Procedure to archive some data from few tables in master database to remote back up database. The back up tables are existing as federated tables in master tables.
However when i move the data from master to back up tables one by one, transaction is not working as expected as I see records are committed in remote back up database irrespective of issuing a rollback in the end of store proc on interpreting the error.

Eg: Say Tables A, B, C exist in master and back up database.
When records are moved from Master A, B, C to back up A, B, C, if some exception happens while processing Table C, i am issuing a rollback in the end, which doesn't rollback A,B tables.
I use START TRANSACTION, SET AUTOCOMMIT=0 in the beginning of store proc and issuing a COMMIT / ROLLBACK in the end based on processing status.

Please advice how to handle this scenario.