SQL & PL/SQL

Dear all

I need some advise on using DBMS_XA from PL/SQL with tightly coupled multiple branches under one global transaction. Basically, I've successfully written some PL/SQL code that in 3 different sessions attaches to 3 different branches of one global transaction and before ending each branch they can see each others uncommitted data. So far so good.

However, I'm not sure I completely understand how each branch must call xa_end, xa_prepare and xa_commit correctly using two phase commit and my calls result in errors like:

ORA-24767: transaction branch prepare returns read-only (XA error code 3 = Transaction was read-only and has been committed)
ORA-24756: transaction does not exist (XA error code -4 = XID is not valid)
ORA-02051: another session or branch in same transaction failed or finalized

This is the structure of my programs (3 SQL*Plus sessions):

main: Uses xid 123|0 (branch 0 of global transaction 123). This should be the coordinator that commits using two phase commit across the 3 branches

m1.xa_start tmnoflags
m2.DML
m3.Wait for thread A + B to manually be started and run xa_end
m4.xa_end tmsuccess
m5.xa_prepare
m6.xa_commit false

thread A: Uses xid 123|A (branch A of global transaction 123)

a1.xa_start tmnoflags
a2.DML -- thread A can see main and thread B's data
a3.xa_end tmsuccess
a4.xa_prepare -- required?
a5.Should we also call xa_commit false?

thread B: Uses xid 123|B (branch B of global transaction 123)

b1.xa_start tmnoflags
b2.DML -- thread B can see main and thread A's data
b3.xa_end tmsuccess
b4.xa_prepare -- required?
b5.Should we also call xa_commit false?

The failing steps are:

m5
m6
a4
a5
b4
b5

Before starting calling xa_end I see 3 rows in V$GLOBAL_TRANSACTION, eg (hex 7B = decimal 123):

FORMATID GLOBALID BRANCHID BRANCHES REFCOUNT PREPARECOUNT STATE FLAGS COUPLING
--------- -------- -------------------------------- -------- -------- ------------ ------ ----- ---------------
203348753 0000007B 00000000000000000000000000000000 3 3 0 ACTIVE 0 TIGHTLY COUPLED
203348753 0000007B 0000000000000000000000000000000A 3 3 0 ACTIVE 0 TIGHTLY COUPLED
203348753 0000007B 0000000000000000000000000000000B 3 3 0 ACTIVE 0 TIGHTLY COUPLED

Thanks a lot in advance.

Cheers

Finn