SAP SQL Anywhere

Question

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

set xact_abort on;

begin distributed transaction
update pmdb.dbo.CURRTYPE set curr_type = curr_type + '!!!' where curr_id =1;
update sa11bin64..groupo.contacts set surname = surname + '???' where id = 1;

select curr_type from pmdb.dbo.CURRTYPE where curr_id =1
exec ('select surname from groupo.contacts where id = 1;') at sa11bin64;                 
rollback;
--commit;

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

Commit

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.

Rollback

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?

Answer 1

2

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.

permanent link

answered 16 Sep '15, 18:44

Nick Elson S...
7.3k●35●107
accept rate: 32%

converted 17 Sep '15, 10:10

Volker Barth
40.2k●361●550●822

2

Thank you for your advice. I already try to use native OLE DB provider some years ago, but without any progress. If I placed commands like

-- MS SQL Server side

begin distributed transaction

update demo17.demo17.groupo.contacts set surname = surname + '???' where id = 1;

commit;

it’s always results to errors like 'The OLE DB provider "SAOLEDB.17" for linked server "demo17" reported an error committing the current transaction' for such updates commands. All attempts to switch off this ‘autocommit mode’ behavior inside BEGIN TRANSACTION/ROLLBACK/COMMIT statements was unsuccessful and I decides that this is dialed with Sybase OLE DB provider. But today I tried to use

exec ('update groupo.contacts set surname = surname + ''???'' where id = 1;') at demo17;

statement and all is works excellent! Its means that source of this errors lay in MS SQL code that process native T-SQL update statements into statements that should be sent to SAW. In any case, all remaining questions should be asked to MS.

(17 Sep '15, 09:24) Oleg45ver2

flat view

Answer 2

Working with OLEDB provider connection I obtain another interesting result during profiling on SAW 17 side. Command

 update demo17.demo17.groupo.contacts set surname = surname + '???' where id = 1;

from MS SQL Server side result to command on SAW 17 side

while @@trancount >= 1 loop commit work end loop

that result to error ‘Cannot commit or rollback while enlisted in transaction’. Full listing is:

select @@version
,if 'A' <> 'a' then 1 else 0 endif
,"isnull"("property"('IsIQ'),'NO')
,"isnull"("connection_property"('odbc_distinguish_char_and_varchar'),'Off')
,"isnull"("connection_property"('odbc_describe_binary_as_varbinary'),'Off'),
"connection_property"('charset'),
"db_property"('charset'),
(select    "count"() 
 from      "SYS"."SYSOPTION" as "o",
           "SYS"."SYSUSER" as "u" 
 where     "o"."user_id" = "u"."user_id" 
       and "o"."option" = 'timestamp_with_time_zone_format' 
       and "u"."user_name" = 'PUBLIC')
,if "connection_property"('auto_commit') is null then 0 else 1 endif

  set temporary option "time_format" = 'hh:nn:ss.ssssss';
  set temporary option "timestamp_format" = 'yyyy-mm-dd hh:nn:ss.ssssss';
  set temporary option "date_format" = 'yyyy-mm-dd';
  set temporary option "date_order" = 'ymd';
  set temporary option "isolation_level" = '0';
  set temporary option "timestamp_with_time_zone_format" = 'yyyy-mm-dd hh:nn:ss.ssssss +hh:nn';
  set temporary option "auto_commit" = 'ON'
select current user
select "db_name"()
select @@SERVERNAME
select current user
set temporary option "auto_commit" = 'ON'
set temporary option "auto_commit" = 'OFF'
  begin transaction
  select @@trancount
set temporary option "auto_commit" = 'OFF'
select * from "demo17"."groupo"."contacts" for update
  while @@trancount >= 1 loop commit work
  end loop
set temporary option "auto_commit" = 'ON'

This listing shows auto_commit options switching to OFF state. May be that

while @@trancount >= 1 loop commit work end loop

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

update demo17.demo17.groupo.contacts set surname = surname + '???' where id = 1;

is more preferable than

exec ('update groupo.contacts set surname = surname + ''???'' where id = 1;') at demo17;

as they allows to construct updates from selects (instead of using cursors).

SAP SQL Anywhere

ROLLBACK of distributed transaction

Follow this question

Related questions