MateX

I am trying to use MS SQL and other MS resource managers in a transaction coordinated by IBM Websphere MQ (which is XA compliant). To do this, I need an xa_switch structure for all RMs.
According to the article http://www.microsoft.com/technet/archive/interopmigration/network/usrscman.mspx
"MS DTC [...] implements the XA_Switch and makes it available through a static library."
But I was unable to find which library provides this switch structure.

Anybody can help





Re: Transactions Programming Ms DTC working as RM in XA transaction

T.J. Goltermann

The GetXaSwitch(DWORD XaSwitchFlags, xa_switch_t** ppXaSwitch); is described at http://mdsn2.microsoft.com/en-us/library/ms686104.aspx.

The xa_switch_t struct is defined in txdtc.h.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

Thank you for the reply.

I have already tried this one, without success.

First of all, where this function is defined The file (mtxoci.dll) mentioned in your link is RM proxy for Oracle DB (as stated in its description), not for DTC. When I call GetXaSwitch from this dll I get E_FAILED result and NULL pointer.

In the article I have mentioned in the first post it is said that XaSwitch structure is available through static library, so I would rather look for .lib file, not .dll.

So the problem is still unsolved.





Re: Transactions Programming Ms DTC working as RM in XA transaction

Jim Carley

The xaswitch.lib that you need to link to is in the Platform SDK\Lib directory.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

Thanks, that seems to work. (I have just found it too and implemented RM proxy I need).

At least it links and MQM starts with the new proxy. I will test it tomorrow, I hope it will work as I wish.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

As I wrote yesterday, the switch file is working now. But I have another problem: xa_open() call returns XAER_RMERR (-3) "a resource manager error occurred in the transaction branch"
I guess that I am providing bad xa open string. What should it be to connect to SQL Server I tried forms like
"DSN=some_connection_name"
"Data Source=host;Initial Catalog=db_name;Integrated Security=True"
"Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;"

Nothing worked, error was the same everytime.

Any ideas





Re: Transactions Programming Ms DTC working as RM in XA transaction

Jim Carley

One possibility is that you have not enabled XA transactions for MSDTC. For security reasons, XA transactions are disabled by default in MSDTC.

This is accomplished by running the Component Services MMC snap-in (typically under Administrative Tools), traversing to "My Computer", right-clicking on My Computer and selecting "Properties".

Go to the MSDTC tab and click the "Security Configuration..." button.

If the "Enable XA Transactions" checkbox is not checked, check it and click "OK". You will get a dialog saying that the MSDTC service needs to be restarted. That's fine.Say "OK". You will get back to the "My Computer Properties" dialog and you can just click "OK" there.

If XA Transactions were disabled, this might fix your problem, so try again.

If XA Transactions were already enabled, or the above steps didn't resolve your problem, we need to move on to the contents of the open string itself.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

XA transactions were already enabled in MSDTC, so the problem is somewhere else.
I too think it is in the xa open string. I can't find any specification which defines format of this string for MSDTC.




Re: Transactions Programming Ms DTC working as RM in XA transaction

Jim Carley

Sorry it took so long to get back to you.

For the first parameter to xa_open, the string you specify should have the following:

TM=<string>,RmRecoveryGuid=<guid>,Timeout=<timeout in milliseconds>

where

<string> is a string that will be used in the transaction descriptions for the MSDTC transactions. This is limited to 39 characters.

<guid> is a string form of a GUID, for example 37db998b-bc35-4a32-92c3-b6ea7131e858. This value is used to find unresolved transactions for recovery. You should use the same value each time you call xa_open for a given resource manager instance. The TM chooses the value for the first connect, but must give the same value for subsequent connects in order to facilitate recovery.

<timeout in milliseconds> is a number that specifies the number of milliseconds before the transaction times out inside MSDTC. If the Timeout token is not specified, the MSDTC transaction will have no timeout.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

Thanks for your help :-)

XA communication between MQ as TM and DTC as RM is now established. Now I need to tell SQL Server to participate in the transaction. I am not sure how to do it, but hope it is only matter of time now. I guess that I have to obtain xid associated with the thread and CTransaction for it using one of the DTC interfaces, but I have no time to do it now. I will post when it is done and working.





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

So I finally found some time to research the subject more.
For single threaded application everything works fine. The problem appears when I start to parallelize work. Here is my piece of code:

Code Snippet

//declarations, initialization

...

MQHCONN hConn; //connection handle for MQ

MQHOBJ mqInQ; //input queue handle for MQ

MQHOBJ mqOutQ;// output queue handle for MQ

//handles for MS SQL:

HENV hEnv = SQL_NULL_HENV;

HDBC hDbc;

HSTMT hStmt;

...

BOOL ok;

...

//connect to Websphere MQ, open queues

//connect to MS SQL

//alloc statement hStmt on connection hDbc


ok=TRUE;


while(ok)

{

ITransaction *pTrans=NULL;

IXATransLookup *pXATransLookup=NULL;

MQBEGIN(hConn, ...); //begin transaction

DtcGetTransactionManagerEx(NULL, NULL, IID_IXATransLookup, 0, NULL, (void**)&pXATransLookup);

pXATransLookup->Lookup(&pTrans);

rc=SQLSetConnectOption(hDbc, SQL_COPT_SS_ENLIST_IN_DTC, (SQLINTEGER)pTrans);

...

MQGET(hConn, mqInQ, ...); //get the message in transaction

... //process the message

SQLExecDirect(hStmt, "INSERT ...", SQL_NTS); //insert message contents into queue - it should be done in transaction too

... //more processing. For some types of messages set ok=FALSE;

MQPUT(hConn, mqOutQ, ...); //put reply message

...

if (ok) MQCMIT(); //Commit changes if everything went ok

else MQBACK(); //Rollback changes otherwise

}

...

//cleanup, close connections etc.




I have two kinds of messages - the 'correct' ones, which are inserted into database and to reply queue, and the 'wrong' ones, which cause rollback and termination of the thread. (Message should stay in input queue, no changes should be made to DB and output queue)

When I put eg. 10 correct messages followed by 1 wrong one in the input queue and then run the above code in one thread, then after program termination there are 10 messages in output queue (correct ones), 10 new records in the DB and 1 erong message in input queue. (So everything ok).

When I run the code in eg. 4 threads in parallel (with the same messages as in previous case), then I get 10 correct messages in output queue, 1 wrong message in input queue, but 11-12 new records in the table (10 for correct messages and 1 or 2 copies of the wrong message).
So it seems that rollbacks works fine on the Websphere MQ side, but on the MS SQL and MS DTC side some interferention between the threads takes place.

Any ideas what am I doing wrong

Every call to pXATransLookup->Lookup() returns different pointer, so I hope transactions are not merged in any way, but then it seems like the last commit (for the last correct message) commits also inserts for the wrong message taking place in other thread...

I have also made some research of what happens between WMQ and MS DTC (XA connection) - for some rollbacks MS DTC (acting as resource manager proxy between MS SQL and WMQ) returns -7 (xa_rollback function), and then -6 on xa_close function.

From xa.h:
#define XAER_PROTO (-6) /* routine invoked in an improper context */
#define XAER_RMFAIL (-7) /* resource manager unavailable */





Re: Transactions Programming Ms DTC working as RM in XA transaction

MateX

Some more experiments and I think I got the solution. The problem seems to be a bug (feature ) of WMQ 5.3. When the first thread closes its connection to WMQ, xa connection to all RM-s (including connection to MS DTC) is also closed - even if other threads are in the middle of the transaction. And for some reason MS DTC commits subsequent SQL inserts after connection to XA TM is broken...
I have added delay(5000) just before each threads MQDISC() and now evertyhing works ok.

EDIT:
Now I have even better solution. The problem was that for each application thread MQ opens new XA connection to each RM (calls xa_open), and then closes each of them separately (calling xa_close), while MSDTC opens one connection at the first call to xa_open, ignores subsequent calls to xa_open (returns 0 as if everything is ok), but closes this connection at the first xa_close call (so other threads loose xa connection to MSSQL, even if they are in the middle of a transaction).
Since I had to develop switch file (library that acts as proxy between MQ as TM and DTC as RM), I could solve this by adding a connection counter which I increment with every xa_open call, and decrement on every xa_close call from MQ. Then I call xa_close on MSDTC side only when the counter reaches 0. It needs more testing, but it works now without adding unnecessary delay.