Thread: Two Phase commits
How does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?
In other words, how do you tell the database to pre-commit???
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Sunday, March 24, 2002 1:33 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Two Phase commitsHow does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?
David,
Can you help me out here, I'm not familiar with two phase commits, why would you want to, and what would you expect the behaviour to be?
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Monday, March 25, 2002 1:40 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Two Phase commitsIn other words, how do you tell the database to pre-commit???-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Sunday, March 24, 2002 1:33 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Two Phase commitsHow does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?
I'm not that versed in two phase commits (2pc) either... my understanding is that there is a "precommit" phase and a "commit" phase. The precommit tells the database that you are going to commit the transaction. If the precommit succeeds, then the database is telling you that it guarantees that the commit will succeed.
2pc is used in distributed transactions where more than one transactional resource (i.e. 2 or more databases) are participating in a global transaction, where all commits to all resources must all succeed or fail together. This is coordinated by a "transaction manager" which manages the precommit/commit/rollback protocols among the resources enlisted in the global transaction. In this scenario, the TM calls precommit on all resources, and if all succeed, then it commits each resource.
I'm trying to implement this global transaction concept in my system, where I have one or more PostgreSQL databases and possibly other transactional resources such as JMS queues. From what I understand, other databases such as Oracle amd SQL Server support 2pcs, but I haven't worked with them at that level. So my question is "How do I use PostgreSQL as a 2-phase resource?"
Clear as mud? ;-)
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Monday, March 25, 2002 1:55 PM
To: 'David Hooker'; pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] Two Phase commitsDavid,Can you help me out here, I'm not familiar with two phase commits, why would you want to, and what would you expect the behaviour to be?Dave-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Monday, March 25, 2002 1:40 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Two Phase commitsIn other words, how do you tell the database to pre-commit???-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Sunday, March 24, 2002 1:33 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Two Phase commitsHow does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?
from the hackers list
Dave
-----Original Message-----
From: David Hooker [mailto:dhooker@a4networks.com]
Sent: Monday, March 25, 2002 3:21 PM
To: pgsql-jdbc@postgresql.org; 'Dave Cramer'
Subject: RE: [JDBC] Two Phase commitsI'm not that versed in two phase commits (2pc) either... my understanding is that there is a "precommit" phase and a "commit" phase. The precommit tells the database that you are going to commit the transaction. If the precommit succeeds, then the database is telling you that it guarantees that the commit will succeed.2pc is used in distributed transactions where more than one transactional resource (i.e. 2 or more databases) are participating in a global transaction, where all commits to all resources must all succeed or fail together. This is coordinated by a "transaction manager" which manages the precommit/commit/rollback protocols among the resources enlisted in the global transaction. In this scenario, the TM calls precommit on all resources, and if all succeed, then it commits each resource.I'm trying to implement this global transaction concept in my system, where I have one or more PostgreSQL databases and possibly other transactional resources such as JMS queues. From what I understand, other databases such as Oracle amd SQL Server support 2pcs, but I haven't worked with them at that level. So my question is "How do I use PostgreSQL as a 2-phase resource?"Clear as mud? ;-)-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Monday, March 25, 2002 1:55 PM
To: 'David Hooker'; pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] Two Phase commitsDavid,Can you help me out here, I'm not familiar with two phase commits, why would you want to, and what would you expect the behaviour to be?Dave-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Monday, March 25, 2002 1:40 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Two Phase commitsIn other words, how do you tell the database to pre-commit???-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of David Hooker
Sent: Sunday, March 24, 2002 1:33 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Two Phase commitsHow does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?
PostrgreSQL does not support distributed transactions, so 2pc is impossible. Check the mail list archives. Even on the JDBC list, there were some exchanges of thoughts and opinions about XA and 2pc back a few months ago. Peter -----Original Message----- From: David Hooker [mailto:dhooker@a4networks.com] Sent: Sunday, March 24, 2002 8:33 AM To: pgsql-jdbc@postgresql.org Subject: [JDBC] Two Phase commits How does one perform a 2-phase commit against PostgreSQL? In reading the JDBC driver code, the driver itself doesn't implement a 2pc protocol, so I'm wondering if it's even possible to do. Any thoughts?