Python (PostgreSQL) wrap local and remote calls in one transaction

Question

I am doing synchronization between two databases in Odoo. If it goes without any issues on remote, then it synchronizes on both sides. But if something goes wrong on remote, then local database changes are committed, but remote is not. In other words, databases go out of sync.

Is there a way to make changes in local database and if something goes wrong trying to synchronize remote database, rollback local database to previous state.

There is this method:

@api.one
def order_process_now(self):
    servers = self._synchro_before_sale()
    # Process local order
    inv_id = self.action_invoice_create()
    if inv_id:
        inv = self.env['account.invoice'].search([('id', '=', inv_id)])
        inv.signal_workflow('invoice_open')
    for picking in self.picking_ids:
        picking.force_assign()
        picking.action_done()
    # Process remote orders
    self._remote_order_action('order_process_now', servers)

As you can see it is divided into two parts. First it makes changes to local database, then makes changes on remote (using xmlrpclib with erppeek wrapper). How can I make this method as one transaction, so if anything goes wrong executing method, any changes to databases would rollback?

score 1 · Answer 1 · answered Jan 19 '15 at 09:31

What you need for this is two-phase commit.

The general idea is:

Begin your local and remote changes
Do the required work on each
On the remote side PREPARE TRANSACTION and take note of the returned ID in persistent storage
On the local side COMMIT the changes
On the remote side COMMIT PREPARED with the returned ID, or if the local commit failed for some reason, ROLLBACK PREPARED instead.

If your app restarts it must look at its record of of prepared-but-not-committed remote transactions and:

if the local transaction was committed) issue a COMMIT PREPARED; or
if the local transaction was NOT committed issue a ROLLBACK PREPARED

This is not simple to get right. The naïve approach that fails to record the local commit ID doesn't really fix anything, it just replaces inconsistent database state with leaked prepared transactions. You must actually keep a record of the prepared transactions and resolve them after a crash or restart. Remember that the ROLLBACK PREPARED or COMMIT PREPARED can fail due to connectivity issues, DB restarts, etc.

For this reason many people use a separate transaction manager that takes care of this part for them. MSDTC is an option on Windows systems. For Java you can supposedly use JTC. On C/UNIX systems you could use XA. Unfortunately distributed transaction managers appear to attract horrible, baroque and ill-defined API design (can you say javax.transaction.HeuristicMixedException?)

score 0 · Answer 2 · answered Jan 19 '15 at 09:29

You'll need to look at two phase commits. Basically this lets you do a trial commit on each separate system and then only if both succeed do a final "real" commit.

You still need to deal with the case where e.g. the client crashes. Then you'll have prepared commits hanging about and you'll want to roll them back and start again.

Python (PostgreSQL) wrap local and remote calls in one transaction

2 Answers2