Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Why does "passthrough for remote_user;" cause the following error (ASA 11.0.1.3113)?

COMMIT/ROLLBACK not allowed within atomic operation  
SQLCODE=-267, ODBC 3 State="42000"

Here's the simplified situation:

create table table1  
(  id integer not null default autoincrement,  
   column1 integer,  
   primary key (id));

create table table2  
(  id integer not null default autoincrement,  
   column1 integer,  
   primary key (id));

Only table2 is part of a SQL Remote subscription

create trigger "trigger1" after update of column1 
order 1 on table1
referencing new as new_val
for each row
begin
  passthrough for remote_user;

  execute immediate 'update table2 set column1 = ' || new_val.column1 || ' where id = ' || new_val.id;

  passthrough stop;
end;

If I now execute "update table1 set column1 = 10 where id = 2" I get the error -267.

If the trigger only does the execute immediate, it works just fine. If the trigger does a "passthrough ONLY for remote_user" it returns the same error. So it almost looks like the passthrough call is causing this error!?

asked 21 Mar '14, 06:11

Christian%20Hamers's gravatar image

Christian Ha...
697131833
accept rate: 42%

edited 21 Mar '14, 10:42

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

I get the same behavior in 9.0.2.3951... I could upgrade my SQL Remote test setup to version 11 but that would not change the fact that I'm stumped :)

alt text

(21 Mar '14, 10:47) Breck Carter

Is this construction (passthrough mode within a trigger) something you have used successfully before? (In other words: I do not know whether it should work at all, i.e. I'm stumped, too:))

(21 Mar '14, 11:04) Volker Barth

Haven't tried this before. There are some limitations listed in the help

Maybe the limit on batch statements could explain this behaviour:
Batch statements (a group of statements surrounded with a BEGIN and END) are not replicated in passthrough mode. If you try to use batch statements in passthrough mode, an error occurs

(21 Mar '14, 11:34) Christian Ha...
Comment Text Removed

Passthrough operations, in this respect, are very similar to DDL (which are also not permitted inside of atomic operations) and are probably internally classified that way.

If I were to classify the PASSTHROUGH statement, I would have classified it as DCL "Data Control Language" in that it affects the SQL Remote "stream" in ways that are not classic DDL or DML. As such they should not be permitted inside of triggers. Either way ... this is not unexpected.

So be 'a shortened tree' no more.

There are other 'non-transactional' aspects to the use of PassThrough statements as well which can affect other scenarios. [there is a reason there are lots of known caveats affixed to this feature ... and why you always test before deploying]

permanent link

answered 21 Mar '14, 11:31

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

Comment Text Removed
Comment Text Removed
Comment Text Removed

The Help should say "Side effects - Automatic commit" for the SQL Remote PASSTHROUGH statement. Here is a reproducible that proves the point:

BEGIN
UPDATE parent SET non_key_1 = 123 WHERE key_1 = 1; 
COMMIT;
SELECT @@VERSION, 'First SELECT', non_key_1 FROM parent WHERE key_1 = 1;
UPDATE parent SET non_key_1 = 456 WHERE key_1 = 1; 
PASSTHROUGH FOR site0001;
PASSTHROUGH STOP;
ROLLBACK;
SELECT @@VERSION, 'Second SELECT', non_key_1 FROM parent WHERE key_1 = 1;
END;

@@VERSION,'First SELECT',non_key_1
'9.0.2.3951','First SELECT','123'

@@VERSION,'Second SELECT',non_key_1
'9.0.2.3951','Second SELECT','456'
permanent link

answered 21 Mar '14, 14:56

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

1

The docs should be updated. The PASSTHROUGH command has had a COMMIT as a side effect since September 20th, 1995 at 5:25pm (Eastern Time).

(24 Mar '14, 13:43) Reg Domaratzki
Replies hidden
1

...no doubt it was in celebration of the 10th anniversary of the capital gains tax being introduced in Australia :)

(24 Mar '14, 15:32) Breck Carter

In case PASSTHROUGH mode is not allowed within a trigger (as Nick has stated), you may use other approaches to force indirect DML updates on replicated tables, say

  • Triggering an event from within your trigger that would run in its own transaction context and would execute the passthrough statement (though that might be difficult to handle in case the original operation is rollbacked).
  • Use the trigger to do the local update on table2 and additionally mark the updated row somehow and use a SQL Remote hook procedure like "sp_hook_dbremote_begin" to catch up on these marked rows later by making the update via PASSTHROUGH ONLY there.
  • Get rid of the PASSTHROUGH mode altogether here - I guess (but that's a wild guess) an UPDATE PUBLICATION statement should influence the remotes as well - I guess what you are trying to achieve is similar to the requirements of "territory realignment", in the sense that you need to have changes written to the log to be replicated that are not directly handled that way...
permanent link

answered 21 Mar '14, 12:39

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

edited 21 Mar '14, 12:44

By the way: Are you sure the UPDATE within the trigger is not replicated by default? If so, does this change when running DBREMOTE -t?

(21 Mar '14, 12:48) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103
×95
×23

question asked: 21 Mar '14, 06:11

question was seen: 6,655 times

last updated: 24 Mar '14, 15:33