Re: Re: PostgreSql - access modified rows in prepare transaction command

On 20.02.2013 13:39, pierpaolo.cincilla wrote:
> Thank you Heikki for your reply. As you suggest, I will explain better what
> I'm trying to accomplish.
>
> What I'm writing a ditributed two-phase-commit termination protocol that
> work in this manner:
>
> 1) Each site has a replica of the database. A site A perform a transaction
> t1 and prepare it (PREPARE TRANSACTION 't1'). Then it atomic broadcast a
> certification request for the transaction t1 *along with its writeset*
> (values updated by t1) to other sites.
>
> 2) When a site receive the certification request for transaction t1 does the
> certification (check that there are no concurrent conflicting transactions).
> If the certification succeed then
> 2a) if the transaction is local (i.e. originated at that site) it commit the
> transaction (COMMMIT PREPARED 't1').
> 2b) If the transaction is remote (i.e. prepared at another site) *it apply
> locally the writeset of transaction t1* to reflect modifications to its
> local replica of the database (UPDATE command).

The usual way to keep two identical databases in sync using two-phase
commit is to just run all the statements in both databases. That assumes
that the statements always produce identical results in both databases,
though.

> The problem is that if I can't fetch the writeset of a transaction in phase
> 1 (before the commit request) then when I certify the transaction at another
> site I can't apply the updates performed by the remote transaction right
> away but I have to wait the originating site to commit the transaction and
> send back its writeset (now visible). This will be very bad because it adds
> an extra round to the algorithm.

You could fetch the "writeset" in the same connection just before
calling PREPARE TRANSACTION. While the transaction is still active, the
changes are visible to itself.

Aside from any extra round-trips, the bigger reason you can't commit
first and then fetch the writeset is that you can't roll back the
transaction anymore, if the writeset can't be applied on the other node.
If you could live with that, and the problem is just the latency, then
you don't need two-phase commit to begin with.

- Heikki