Re: 2-phase commit

On Fri, 10 Oct 2003, Heikki Linnakangas wrote:

> On Thu, 9 Oct 2003, Bruce Momjian wrote:
>
> > Agreed.  Let's get it into 7.5 and see it in action.  If we need to
> > adjust it, we can, but right now, we need something for distributed
> > transactions, and this seems like the logical direction.
>
> I've started working on two-phase commits last week, and the very
> basic stuff is now working. Still a lot of bugs though.

I have done more work on my 2PC commit patch. I still need to work out
notifications and CREATE statements, but otherwise I'm quite happy with it
now. I received no feedback on the first version, so I'll try to clarify
how it works a bit.

The patch is against the current cvs tip. I'll post it to the
patches-list, and you can also grab it from here:
http://www.hut.fi/~hlinnaka/twophase2.diff

The patch introduces three new commands, PREPCOMMIT, COMMITPREPARED and
ABORTPREPARED.

PREPCOMMIT is called in place of COMMIT, to put the active transaction
block into prepared state. PREPCOMMIT takes a string argument that
becomes the Global Transaction Identifier (GID) for the transaction. The
GID is used as a handle to COMMITPREPARED/ABORTPREPARED commands to finish
the 2nd phase commit. After the PREPCOMMIT command finishes, the
transaction is no longer associated with any specific backend.

COMMITPREPARED/ABORTPREPARED commands are used to finish the prepared
transaction. They can be issued from any backend.

There's also a new system view, pg_prepared_xacts that show all prepared
transactions.

Here's a little step-by-step tutorial to trying out the patch:
---------
1. apply patch, patch -p0 < twophase2.diff
2. compile
3. create a new database system with initdb.
4. run postmaster
5. psql template1
6. CREATE TABLE foobar (a integer);
7. INSERT INTO foobar values (1);

8. BEGIN; UPDATE foobar SET a = 2 WHERE a = 1;
9. SELECT * FROM foobar;
10. PREPCOMMIT 'foobar_update1';

The transaction is now in prepared state, and it's no longer associated
with this backend, as you can see by issuing:

11. SELECT * FROM foobar;
12. SELECT * FROM pg_prepared_xacts;

Let's commit it then.

13. COMMITPREPARED 'foobar_update1';
14. SELECT * FROM pg_prepared_xacts;
15. SELECT * FROM foobar;

Next repeat steps 8-15 but try killing postmaster somewhere after step 9,
and observe that the transaction is not lost. Also try doing another
update with a different backend, and see that the locks held by the
prepared transaction survive the crash.
--------

I also took a look at Satoshis patches. The main difference is that
his implementation made modifications to the BE/FE protocol, while my
implementation works at the statement level. His patches don't handle
shutdowns or broken connections yet, but that was on his TODO list.

When I started working on 2PC, I didn't know about Satoshis patches,
otherwise I probably would have took them as a starting point.

The next step is going to be writing 2PC support to the JDBC driver using
the new backend commands. XA interface would be very nice too, but I'm
personally not that interested in that. Any volunteers?

Please comment! I'd like to know what you guys think about this. Am I
heading into the right direction?

Some people have expressed concerns about performance issues with 2PC in
general. Please note that this patch doesn't change the traditional
commit routines, so it won't affect you performance if you don't use 2PC.

- Heikki