Distributed transactions between MySQL and MSSQL

问题:

I've tried for nearly a week now to get distributed transactions working. I've some procedures on MSSQL which try to select data from MySQL. My need is to do this in one(!) transaction. At the time I've set up ODBC connection on MSSQL with Single-Tier MySQL driver from OpenLink, which states me, that XA transactions work successfully (there is a test button integrated after configuring ODBC connection). Then I've set up a linked server in MSSQL via MSDASQL to this ODBC connection, but when doing

begin distributed transaction
    select * from optin..lu_source_proc
    select * from openquery(optinxa, 'SELECT * FROM tbl_source_proc')
commit transaction

I get the error, that no further transaction could be started inside the actual transaction. (Der OLE DB-Anbieter "MSDASQL" für den Verbindungsserver "optinxa" hat die Meldung "Es können keine weiteren Transaktionen in dieser Sitzung gestartet werden." zurückgeben.)

Another test:

set transaction isolation level serializable
begin transaction
    select * from optin..lu_source_proc
    select * from openquery(optinxa, 'SELECT * FROM tbl_source_proc')
commit transaction

Results in Der OLE DB-Anbieter "MSDASQL" für den Verbindungsserver "optinxa" hat die Meldung "[OpenLink][ODBC][Driver]Driver does not support this function" zurückgeben. But why states the ODBC driver on configuring, that XA transactions do work?

回答1:

There are two important aspects here --

1) It seems that OLE DB does have support for distributed transactions - so I would assume that the Microsoft OLE DB Provider for ODBC Data Sources should too...

I would also assume that if MSDASQL did support distributed transactions then it would handle that functionality directly rather than delegating it to the ODBC Driver...

Distributed transactions are turned on in the ODBC driver by calling --

SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC)

So, enabling OpenLink driver logging via the DSN creation dialogs should help determine whether SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC) is called.

2) We would need to see additional tracing in order to see the ODBC activity immediately leading up to the "Driver does not support this function" error...

You can log a support case with OpenLink Software to take this further...

回答2:

The following steps are required if you want to use XA data sources together with Microsoft Distributed Transaction Coordinator (MS DTC) for handling distributed transactions:

On Windows XP and Windows Server 2003:

1. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.

2. Expand Component Services, Computers and right-click My Computer, and then select Properties.

3. Click the MSDTC tab, and then click Security Configuration.

4. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

5. Click OK again to close the Properties dialog box, and then close Component Services.

6. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

On Windows Vista and Windows Server 2008:

1. Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also type %windir%\system32\comexp.msc in the Start Search box to open Component Services.

2. Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.

3. Right-click Local DTC and then select Properties.

4. Click the Security tab on the Local DTC Properties dialog box.

5. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

6. Click OK again to close the Properties dialog box, and then close Component Services.

7. Stop and then restart SQL Server to make sure that it syncs up with the MS DTC changes.

See also Registry Entries Are Required for XA Transaction Support and validate that the third party driver XA DLL creates these registry entries.