[DB-SIG] Two-phase commit API proposal (was Re: Any standard for two phase commit APIs?)

[James Henstridge]

On 23/01/2008, Dieter Maurer <dieter at handshake.de> wrote:
> M.-A. Lemburg wrote at 2008-1-22 20:31 +0100:
> > ...
> >Branch ids are used for e.g. multiple connections of the same RM
> >engaging in a global transaction. Each of those connections gets
> >its own branch id.
>
> But using multiple connections to the same RM seems to
> be an error in the first place.
>
>   Assume that a resource "R" is locked via connection "C1".
>   Assume than that "R" is requested via connection "C2".
>
>   If "C1 == C2", then the RM can see that the resource is already
>   assigned to the connection and there is no blocking.
>
>   Otherwise, the RM has not chance to recognize this and
>   the request will be blocked until the transaction is commited
>   or rolled back. There is quite a high chance, that since the
>   "R" request is blocked, there will be no commit/roll back....

Here is a concrete example:

1. create two databases on a single PostgreSQL install.
2. write an application that connects to each database (which implies
two connections).
3. try to prepare transactions on each connection using the same
transaction identifier.

One of the transactions will fail with a "transaction identifier is
already in use" error.  While each connection is accessing independent
resources, the transaction ID namespace is shared by all databases in
the cluster.

Now if you include a branch qualifier in the transaction IDs the
problem is avoided.  The MySQL documentation leads me to believe it
behaves similarly.

James.