Re: 2PC transaction id

Do the transaction id's used in 2PC need to be unique across all
sessions?

Do we provide a mechanism for this ?

If not shouldn't we provide a way to create a unique transaction id ?

Dave Cramer
davec(at)postgresintl(dot)com
www.postgresintl.com
ICQ #14675561
jabber davecramer(at)jabber(dot)org
ph (519 939 0336 )

On Thu, Jun 30, 2005 at 06:39:43PM -0400, Dave Cramer wrote:
> Do the transaction id's used in 2PC need to be unique across all
> sessions?

Yes.

> Do we provide a mechanism for this ?

Huh, the constraint is enforced by the server, but the ID is generated
by the client.

> If not shouldn't we provide a way to create a unique transaction id ?

Maybe a new function would suffice.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Use it up, wear it out, make it do, or do without"

In reality all it takes is a sequence, however if it were system
generated it would be simpler

Dave
On 30-Jun-05, at 6:46 PM, Alvaro Herrera wrote:

> On Thu, Jun 30, 2005 at 06:39:43PM -0400, Dave Cramer wrote:
>
>> Do the transaction id's used in 2PC need to be unique across all
>> sessions?
>>
>
> Yes.
>
>
>> Do we provide a mechanism for this ?
>>
>
> Huh, the constraint is enforced by the server, but the ID is generated
> by the client.
>
>
>> If not shouldn't we provide a way to create a unique transaction id ?
>>
>
> Maybe a new function would suffice.
>
> --
> Alvaro Herrera (<alvherre[a]surnet.cl>)
> "Use it up, wear it out, make it do, or do without"
>
>

Dave Cramer wrote:
> Do the transaction id's used in 2PC need to be unique across all sessions?

They are global IDs, yes.

> Do we provide a mechanism for this ?
>
> If not shouldn't we provide a way to create a unique transaction id ?

Well, in XA the XIDs are assigned by the TM, the individual resources
(e.g. a postgresql backend) just get *given* an XID to use.

-O

I'm thinking of the situation where one transaction occurs on more
than one backend, and there is
more than one transaction manager.

Dave
On 30-Jun-05, at 7:37 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> Do the transaction id's used in 2PC need to be unique across all
>> sessions?
>>
>
> They are global IDs, yes.
>
>
>> Do we provide a mechanism for this ?
>>
>> If not shouldn't we provide a way to create a unique transaction id ?
>>
>
> Well, in XA the XIDs are assigned by the TM, the individual resources
> (e.g. a postgresql backend) just get *given* an XID to use.
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>

Dave Cramer wrote:
> I'm thinking of the situation where one transaction occurs on more than
> one backend, and there is
> more than one transaction manager.

XA XIDs are *global* IDs, i.e. they are unique even with more than one
TM involved. It's the responsibility of the TM to generate a
globally-unique XID.

If you have two different databases involved in the same global
transaction, then yes, the two backends could be told to use the same
global XID. That's normal. (they don't *have* to be given the same XID
as they could be participating in two independent branches of the same
global transaction, and in that case the global XIDs will have different
branch qualifiers)

It's even possible for one resource to do two different independent
(local) transactions that are part of the same global transaction -- in
that case, the local transactions will be given different XIDs though.

But all of this allocation / management of XIDs is done by the TM, the
individual resources don't need to do anything beyond associating
particular transactions with client-supplied XIDs, which we already do
AFAIK.

-O

Oliver Jowett wrote:

> If you have two different databases involved in the same global
> transaction, then yes, the two backends could be told to use the same
> global XID. That's normal. (they don't *have* to be given the same XID
> as they could be participating in two independent branches of the same
> global transaction, and in that case the global XIDs will have different
> branch qualifiers)

Thinking about this some more -- it may be necessary for the same XID to
be associated with more than one backend transaction at once, possibly
even in the same database. This could happen if there are two clients
involved in the same global transaction with no branch qualifier change,
or if one client manages to get two separate resources that point at the
same database.

[... experiments ...]

Ok, so the second case is actually even more general, since
pg_prepared_xacts is scoped cluster-wide not database-wide. So any
global transaction that involves two databases on the same cluster could
be affected.

It seems that you can't PREPARE TRANSACTION more than once (per cluster)
with the same GID. That's a bit painful..

Can we make the GID-to-internal-xid mapping for prepared transactions
1:N rather than the current 1:1? COMMIT PREPARED and ROLLBACK PREPARED
would need either syntax or behaviour changes: either we need to
identify a particular transaction (perhaps via the xid from
pg_prepared_xacts.transaction), or they need to operate on *all*
transactions with the given GID.

I have no idea on how nasty it is to implement this though :)

Heikki, any thoughts?

-O

PS: noticed in passing: psql's help doesn't seem to know about the 2PC
command syntax yet.

On 30-Jun-05, at 8:00 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> I'm thinking of the situation where one transaction occurs on
>> more than
>> one backend, and there is
>> more than one transaction manager.
>>
>
> XA XIDs are *global* IDs, i.e. they are unique even with more than one
> TM involved. It's the responsibility of the TM to generate a
> globally-unique XID.
>
> If you have two different databases involved in the same global
> transaction, then yes, the two backends could be told to use the same
> global XID. That's normal. (they don't *have* to be given the same XID
> as they could be participating in two independent branches of the same
> global transaction, and in that case the global XIDs will have
> different
> branch qualifiers)
I understand this
>
> It's even possible for one resource to do two different independent
> (local) transactions that are part of the same global transaction
> -- in
> that case, the local transactions will be given different XIDs though.
>
> But all of this allocation / management of XIDs is done by the TM, the
> individual resources don't need to do anything beyond associating
> particular transactions with client-supplied XIDs, which we already do
> AFAIK.
I was actually thinking more of the people without the advantage of
an application server
Also for the case where some lowlevel replicator wanted to use 2PC.
Probably a moot point
as anyone capable of writing one is also capable of getting a unique XID
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> Do the transaction id's used in 2PC need to be unique across all
> sessions?
> Do we provide a mechanism for this ?
> If not shouldn't we provide a way to create a unique transaction id ?

I see no value in that at all. The point of 2PC is to synchronize with
other databases running other transactions; an ID assigned by one
database that can only be guaranteed unique with respect to that
database is really pretty useless. In practice the IDs will be assigned
by the transaction manager module according to its own needs.

regards, tom lane

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Can we make the GID-to-internal-xid mapping for prepared transactions
> 1:N rather than the current 1:1?

No.

regards, tom lane

Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>Can we make the GID-to-internal-xid mapping for prepared transactions
>>1:N rather than the current 1:1?
>
>
> No.

Ok, so how do we get XA working when a single global transaction
involves two databases on the same cluster?

The scenario is:

- there are two independent resource managers participating in a single
global transaction
- each resource manager has a connection to the database it is managing,
and a SQL-level transaction running against that database
- the global TM tells both resource managers to prepare their part of
the global transaction, passing the same XID to both
- the resource manager translates the xa_prepare() call to a PREPARE
TRANSACTION query, using the passed XID as the GID.

Currently, one of the PREPARE TRANSACTIONs is going to fail if the two
databases happen to be running under the same postmaster.

For this particular case we could embed the database name in the GID,
but unfortunately that doesn't work in the more general case where you
could have two RMs (perhaps in different processes) talking to the same
database.

Perhaps the second and subsequent RM to prepare could detect the
duplicate GID and add a sequence number or something similar to the end
-- and reverse this process on commit/rollback/recovery -- but I don't
see how you'd do this atomically with the PREPARE TRANSACTION.

-O

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Ok, so how do we get XA working when a single global transaction
> involves two databases on the same cluster?

It's the TM's responsibility to deal with that. I would expect it to
hand out transaction IDs that consist of a common prefix and a
per-database suffix, if it does not know which resources it's dealing
with might share a common GID namespace.

There's a reason that the XA spec has such a ridiculously large
requirement for the length of a GID name, and it is that this is the
TM's problem not ours.

regards, tom lane

Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>Ok, so how do we get XA working when a single global transaction
>>involves two databases on the same cluster?
>
>
> It's the TM's responsibility to deal with that. I would expect it to
> hand out transaction IDs that consist of a common prefix and a
> per-database suffix, if it does not know which resources it's dealing
> with might share a common GID namespace.

Hm, that's not how I read the spec :( Throughout the API is the
implication that you can have more than one RM associated with a
transaction branch.

For example, 3.3.1 says:

> 3.3.1 Registration of Resource Managers
> Normally, a TM involves all associated RMs in a transaction branch. (The TM’s set of
> RM switches, described in Section 4.3 on page 21 tells the TM which RMs are
> associated with it.) The TM calls all these RMs with xa_start(), xa_end(), and
> xa_prepare (), although an RM that is not active in a branch need not participate further
> (see Section 2.3.2 on page 8). A technique to reduce overhead for infrequently-used
> RMs is discussed below.

I don't know if we can reasonably expect TMs not to hand out an
identical XID to different RMs in the same global transaction.

(anyone with experience with how existing TMs behave want to chime in?)

-O

Oliver Jowett wrote:
> Tom Lane wrote:
>
>>It's the TM's responsibility to deal with that. I would expect it to
>>hand out transaction IDs that consist of a common prefix and a
>>per-database suffix, if it does not know which resources it's dealing
>>with might share a common GID namespace.

> I don't know if we can reasonably expect TMs not to hand out an
> identical XID to different RMs in the same global transaction.

Hm, I suppose we *can* assume that a TM won't hand out the same XID to
the same RM twice (except for the special case of TMJOIN), so we could
append a per-database suffix in the RM itself (e.g. JDBC driver) to
avoid conflicts within a database cluster.

-O

On Fri, 1 Jul 2005, Oliver Jowett wrote:

> Ok, so how do we get XA working when a single global transaction
> involves two databases on the same cluster?
>
> The scenario is:
>
> - there are two independent resource managers participating in a single
> global transaction
> - each resource manager has a connection to the database it is managing,
> and a SQL-level transaction running against that database
> - the global TM tells both resource managers to prepare their part of
> the global transaction, passing the same XID to both

If the TM does that, it's broken.

The XID consists of three parts:

format id: a constant. Not interesting.

gtrid: Global Transaction Identifier. This identifies the global
transaction in the TM. All XIDs that have the same gtrid should be
completed atomically by the TM

branch id: Branch Identifier. Every RM involved in the global transaction
is given a *different* branch id.

In the above case, the TM would give the two resource managers XIDs that
have the same gtrid but different branch ids.

From the RM point of view, those fields have no significance and the XID
as whole is used to identify the transaction.

So the RM should never see the same XID twice, except when the TM
specifically uses the TMJOIN or the TMSUSPEND/TMRESUME flags. If the TM
uses those flags, it'll only issue one prepare.

- Heikki

On Fri, 1 Jul 2005, Oliver Jowett wrote:

> Heikki Linnakangas wrote:
>
>> branch id: Branch Identifier. Every RM involved in the global
>> transaction is given a *different* branch id.
>
> Hm, I am confused then -- the XA spec definitely talks about enlisting
> multiple RMs in a single transaction branch.
>
> Can you explain?

I oversimplified a bit. The TM *can* enlist multiple threads of control (=
connection in JTA) to the same transaction branch. That's called
"tightly-coupled threads", and they should then be treated as one
local transaction in the RM. The calls will look like this:

conn1.start(xid1, TMNOFLAGS);
...
conn2.start(xid1, TMJOIN);
...
conn1.end(xid1, TMSUCCESS);
...
conn2.end(xid1, TMSUCCESS);

connX.prepare(xid1);
connX.commit(xid1, false);

conn1 and conn2 must share locks and see each others changes. They
mustn't deadlock each other. The JDBC driver can implement this in a very
straight-forward way by using the same physical connection for both conn1
and conn2. Note that there's only one prepare, and it can be issued using
any connection.

The other possibility is called "loosely-coupled threads". In this case
the calls look like this:

conn1.start(xid1, TMNOFLAGS);
...
conn2.start(xid2, TMNOFLAGS);
...
conn1.end(xid1, TMSUCCESS);
...
conn2.end(xid2, TMSUCCESS);
...
connX.prepare(xid1);
connX.prepare(xid2);
connX.commit(xid1, false);
connX.commit(xid2, false);

xid1 and xid2 can belong to the same global transaction, but different
branches. The RM doesn't need to care both branches belong to the same
global transactions, xid1 and xid2 can be treated just like any random
two transactions. They can deadlock each other, and they won't see each
others changes before commit. This can be implemented in the JDBC driver
by using two physical connections.

So the example given earlier in this thread, with one transaction branch
but two prepare-calls makes no sense. The RM should throw an exception
if the TM calls start twice with the same XID, and TMJOIN flag is not
given. One transaction branch maps in 1:1 fashion to one RM local
transaction.

I hope this helps...

- Heikki

Heikki Linnakangas wrote:
> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>
>> Heikki Linnakangas wrote:
>>
>>> branch id: Branch Identifier. Every RM involved in the global
>>> transaction is given a *different* branch id.
>>
>>
>> Hm, I am confused then -- the XA spec definitely talks about enlisting
>> multiple RMs in a single transaction branch.
>>
>> Can you explain?
>
> I oversimplified a bit. The TM *can* enlist multiple threads of control
> (= connection in JTA) to the same transaction branch. That's called
> "tightly-coupled threads", and they should then be treated as one local
> transaction in the RM.

Ok, I understand that case.

What I'm confused about is, for example, 3.3.1 in the DTP:XA spec:

> 3.3.1 Registration of Resource Managers
> Normally, a TM involves all associated RMs in a transaction branch. (The TM’s set of
> RM switches, described in Section 4.3 on page 21 tells the TM which RMs are
> associated with it.) The TM calls all these RMs with xa_start(), xa_end(), and
> xa_prepare (), although an RM that is not active in a branch need not participate further
> (see Section 2.3.2 on page 8). A technique to reduce overhead for infrequently-used
> RMs is discussed below.

That implies it's valid (in fact, normal!) to enlist many different RMs
in the same transaction branch. Am I interpreting that correctly?

-O

On Fri, 1 Jul 2005, Oliver Jowett wrote:

> What I'm confused about is, for example, 3.3.1 in the DTP:XA spec:
>
>> 3.3.1 Registration of Resource Managers
>> Normally, a TM involves all associated RMs in a transaction branch. (The TMs set of
>> RM switches, described in Section 4.3 on page 21 tells the TM which RMs are
>> associated with it.) The TM calls all these RMs with xa_start(), xa_end(), and
>> xa_prepare (), although an RM that is not active in a branch need not participate further
>> (see Section 2.3.2 on page 8). A technique to reduce overhead for infrequently-used
>> RMs is discussed below.
>
> That implies it's valid (in fact, normal!) to enlist many different RMs
> in the same transaction branch. Am I interpreting that correctly?

I see. No, I don't think that's the correct interpretation, though now
that you point it out, that paragraph is a bit confusing.

What it means, is that the TM always calls xa_start(), xa_end() and
xa_prepare() for a RM, even if the transaction doesn't actually have any
work to do for the RM. It has to be like that in the XA world, because the
TM doesn't know which RMs the application really uses in the transaction.

Chapter 3.3.1 talks about dynamic registration. In that scheme, the RM
registers itself to the TM when the application calls the RM for the first
time in the transaction. That's an optimization to avoid the overhead of
the start/end/prepare cycle for RMs that aren't really involved.

JTA works differently from XA on this matter. In JTA, the application
server tells the TM which RMs are involved in the transaction, so the
XA dynamic registration has not been included in JTA.

Disclaimer: I've never used an XA implementation, and I have only little
experience with JTA.

- Heikki

On Fri, 1 Jul 2005, Oliver Jowett wrote:

> PS: noticed in passing: psql's help doesn't seem to know about the 2PC
> command syntax yet.

True.

Should we add support for it? 2PC is not something you normally do
interactively...

- Heikki

Heikki Linnakangas wrote:
> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>
> > PS: noticed in passing: psql's help doesn't seem to know about the 2PC
> > command syntax yet.
>
> True.
>
> Should we add support for it? 2PC is not something you normally do
> interactively...

Yes, we should add psql support for it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

It certainly helps if you need to debug a process.

Ken

On Fri, Jul 01, 2005 at 09:06:03PM +0300, Heikki Linnakangas wrote:
> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>
> >PS: noticed in passing: psql's help doesn't seem to know about the 2PC
> >command syntax yet.
>
> True.
>
> Should we add support for it? 2PC is not something you normally do
> interactively...
>
> - Heikki
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

On Fri, 1 Jul 2005, Bruce Momjian wrote:

> Heikki Linnakangas wrote:
>> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>>
>>> PS: noticed in passing: psql's help doesn't seem to know about the 2PC
>>> command syntax yet.
>>
>> True.
>>
>> Should we add support for it? 2PC is not something you normally do
>> interactively...
>
> Yes, we should add psql support for it.

Ok, here's a patch.

There's no tab-completion for "PREPARE TRANSACTION 'xxx'" since that would
be ambigous with "PREPARE xxx AS xx".

- Heikki

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>> PS: noticed in passing: psql's help doesn't seem to know about the 2PC
>> command syntax yet.

> True.

Really?

regression=# \h commit prepared
Command: COMMIT PREPARED
Description: commit a transaction that was earlier prepared for two-phase commit

Syntax:
COMMIT PREPARED transaction_id

regards, tom lane

Oh, just realized after Tom's reply that you were talking about help, not
tab-completion...

On Fri, 1 Jul 2005, Heikki Linnakangas wrote:

> On Fri, 1 Jul 2005, Bruce Momjian wrote:
>
>> Heikki Linnakangas wrote:
>>> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>>>
>>>> PS: noticed in passing: psql's help doesn't seem to know about the 2PC
>>>> command syntax yet.
>>>
>>> True.
>>>
>>> Should we add support for it? 2PC is not something you normally do
>>> interactively...
>>
>> Yes, we should add psql support for it.
>
> Ok, here's a patch.
>
> There's no tab-completion for "PREPARE TRANSACTION 'xxx'" since that would be
> ambigous with "PREPARE xxx AS xx".
>
> - Heikki

- Heikki

Tom Lane wrote:

> regression=# \h commit prepared
> Command: COMMIT PREPARED
> Description: commit a transaction that was earlier prepared for two-phase commit
>
> Syntax:
> COMMIT PREPARED transaction_id

Ah, I was looking under '\h commit', '\h prepare' etc.

-O

Heikki Linnakangas wrote:
> On Fri, 1 Jul 2005, Oliver Jowett wrote:
>>
>> That implies it's valid (in fact, normal!) to enlist many different RMs
>> in the same transaction branch. Am I interpreting that correctly?
>
>
> I see. No, I don't think that's the correct interpretation, though now
> that you point it out, that paragraph is a bit confusing.
>
> What it means, is that the TM always calls xa_start(), xa_end() and
> xa_prepare() for a RM, even if the transaction doesn't actually have any
> work to do for the RM. It has to be like that in the XA world, because
> the TM doesn't know which RMs the application really uses in the
> transaction.

Sorry to keep beating on this, but I still don't see where the spec says
that you must have only one RM per transaction branch.

2.2.6 says:

> 2.2.6 Transaction Branches
> A global transaction has one or more transaction branches (or branches). A branch is a
> part of the work in support of a global transaction for which the TM and the RM
> engage in a separate but coordinated transaction commitment protocol (see Section 2.3
> on page 8). Each of the RM’s internal units of work in support of a global transaction is
> part of exactly one branch.

> A global transaction might have more than one branch when, for example, the AP uses
> multiple processes or is involved in the same global transaction by multiple remote
> APs.

So it seems to me that branches are intended to allow independent
processes / APs to each have an independent set of "tightly coupled
threads" (as all work on a particular branch is tightly-coupled).
There's no mention of having only one RM per branch, which I'd expect to
see here if it was a requirement.

One implication of the second paragraph is that a single-threaded AP can
use a single transaction branch for all the work it does.

> Disclaimer: I've never used an XA implementation, and I have only little
> experience with JTA.

Mostly the same here. It'd be useful to get input from someone who's
actually written XA code..

-O

On Sat, 2 Jul 2005, Oliver Jowett wrote:

> Sorry to keep beating on this, but I still don't see where the spec says
> that you must have only one RM per transaction branch.

Sure, it's important to get this right.

> 2.2.6 says:
>
>> 2.2.6 Transaction Branches
>> A global transaction has one or more transaction branches (or branches). A branch is a
>> part of the work in support of a global transaction for which the TM and the RM
>> engage in a separate but coordinated transaction commitment protocol (see Section 2.3
>> on page 8). Each of the RMs internal units of work in support of a global transaction is
>> part of exactly one branch.
>
>> A global transaction might have more than one branch when, for example, the AP uses
>> multiple processes or is involved in the same global transaction by multiple remote
>> APs.
>
> So it seems to me that branches are intended to allow independent
> processes / APs to each have an independent set of "tightly coupled
> threads" (as all work on a particular branch is tightly-coupled).
> There's no mention of having only one RM per branch, which I'd expect to
> see here if it was a requirement.

They should have been explicit about it, I agree.

The key is the clause "A branch is a part of the work in support of a
global transaction for which the TM and the RM engage in a separate but
coordinated transaction commitment protocol". That means that for each
branch, there's exactly one call to prepare and commit. If you have two
RMs, you need two prepare/commit calls, so you need two branches.

> One implication of the second paragraph is that a single-threaded AP can
> use a single transaction branch for all the work it does.

No, that paragraph says "for example". The third example is the case where
you have more than one RM :).

As further, non-authoritative, evidence, see this DevX article on JTA:

http://archive.devx.com/java/free/articles/dd_jta/jta-2.asp

Search for "branch" in that article. It says explicitly "Requests to three
different RDBMSs, therefore, require three transaction branches."

- Heikki

Patch applied. Thanks.

---------------------------------------------------------------------------

Heikki Linnakangas wrote:
> On Fri, 1 Jul 2005, Bruce Momjian wrote:
>
> > Heikki Linnakangas wrote:
> >> On Fri, 1 Jul 2005, Oliver Jowett wrote:
> >>
> >>> PS: noticed in passing: psql's help doesn't seem to know about the 2PC
> >>> command syntax yet.
> >>
> >> True.
> >>
> >> Should we add support for it? 2PC is not something you normally do
> >> interactively...
> >
> > Yes, we should add psql support for it.
>
> Ok, here's a patch.
>
> There's no tab-completion for "PREPARE TRANSACTION 'xxx'" since that would
> be ambigous with "PREPARE xxx AS xx".
>
> - Heikki

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073