System configuration: MS SQL Server 2012 on one computer and SQLAnywhere server on another. SQLAnywhere server linked to MS SQL via Microsoft OLE DB provider for ODBC drivers (Remote server link).
Test SQL commands that involved in distributed transaction Query (code made simple as it possible):
Problem representation: distributed transaction between this two servers works absolutely fine when its finished with COMMIT (see MSDTC coordinator trace below. Time on this computers is not synchronized precisely, but it's not difficult to understand how transaction propagation and commitment proceed).
But if I change COMMIT on ROLLBACK (after any exception in actual SQL procedure) aborting of transaction is hanged on SQLAnywhere side. This "hanged" transaction may lay on MSDTC many hours until SAW service restarted.
All is looks like MSDTC on SAW server side successfully call ITransactionResourceAsync::AbortRequest(...) - I can see RM_ISSUED_ABORT in trace log. But RM_ACKNOWLEDGED_ABORT is absent in log (it's present on MS SQL side). I may suppose that this problem originate from wrong synchronization between thread that process AbortRequest and thread that process ODBC connection commands.
Dependencies: this problem may be represented on any version of Windows (on 7 or Server 2012R2), on any version of SQLAnywhere (11 or 16, 32 or 64 bit) and not dialed with general configuration problem (COMMIT is fine, general SQLAnywhere sample database was used to eliminate DB settings affection, transaction isolation level was default - zero). SAW tracing don't give any results - it's showing only commands from ODBC connection and don't represent affects from MSDTC.
Question: is anybody know any workarounds to avoid this problem or may give any advice how to obtain more detailed debug/tracking information that may help to understand how work rollback that initiated by MSDTC?
asked 16 Sep '15, 07:20
You may be measuring a bug in the MS bridge driver (Microsoft OLE DB provider for ODBC drivers) ... SQL Anywhere provides a native OleDB driver and I would recommend running with that instead.
Try your test again with the SQL Anywhere provided Ole/DB driver.
Working with OLEDB provider connection I obtain another interesting result during profiling on SAW 17 side. Command
from MS SQL Server side result to command on SAW 17 side
that result to error ‘Cannot commit or rollback while enlisted in transaction’. Full listing is:
This listing shows auto_commit options switching to OFF state. May be that
command may be suppressed on MS SQL side if set auto_commit connection property to OFF at beginning. Is any way to do this?
P.S. Commands like
is more preferable than
as they allows to construct updates from selects (instead of using cursors).
answered 17 Sep '15, 12:26