MySQL和MSSQL之间的分布式事务 [英] Distributed transactions between MySQL and MSSQL

问题描述

我已经尝试了近一个星期，以使分布式事务正常运行.我在MSSQL上有一些程序尝试从MySQL选择数据.我需要在one(！)事务中执行此操作.当时，我已经使用OpenLink的单层MySQL驱动程序在MSSQL上建立了ODBC连接，这表明我XA事务成功运行(在配置ODBC连接后集成了一个测试按钮).然后，我通过MSDASQL在MSSQL中设置了到此ODBC连接的链接服务器，但是在这样做时

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

我得到一个错误，那就是在实际交易中无法再进行任何交易. (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.)

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.)

另一项测试:

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

结果为Der OLE DB-Anbieter "MSDASQL" für den Verbindungsserver "optinxa" hat die Meldung "[OpenLink][ODBC][Driver]Driver does not support this function" zurückgeben.，但是为什么在配置时指出ODBC驱动程序可以XA事务正常工作呢?

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)似乎OLE DB 确实支持分布式事务-因此，我认为ODBC数据源的Microsoft OLE DB提供程序也应该...

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...

我还要假设，如果MSDASQL确实支持分布式事务，那么它将直接处理该功能，而不是将其委托给ODBC驱动程序...

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...

通过调用-

SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC)

SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC)

因此，通过DSN创建对话框启用OpenLink驱动程序日志记录应有助于确定是否调用了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)我们将需要查看其他跟踪，才能看到ODBC活动立即导致驱动程序不支持此功能"错误...

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...

您可以通过记录支持案例 //www.openlinksw.com"rel =" nofollow> OpenLink软件进一步说明...

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

这篇关于MySQL和MSSQL之间的分布式事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！