Let's say, we have a procedure which is called in transaction, the transaction begins and ends (commits or rollbacks) outside of the procedure. A row is inserted into a logging table everytime the procedure is called.

We need to leave this inserted row permanent even when transaction is rolled back. Is it somehow possible in SA11, SA12 or SA16 to achieve this result without reorganizing the whole code?

The last pick would be to make an event and issue a TRIGGER EVENT statement with parameters to write into logging table instead of doing it in the same transaction as event would do the job in a separate connection. Are there better approaches possible currently?

NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal. :)

asked 04 Apr '14, 04:26

Arthoor's gravatar image

Arthoor
1.1k274056
accept rate: 0%


I guess the answer is still "You can use events for that, as they run in a separate connection (and therefore in a separate transaction)" - cf. these FAQs:

When you are using application code to call the procedure, you might simply use a second connection to store the logging information (that's a method we've used for logging some kind of errors).


NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal. :)

That sounds compelling, however, I would not dare to ask what that would mean for the ACID principles... - Another approach might be to log into a temporary not transactional table and move that contents later (whenever that would be) to a permanent table. That could be done

  • for the current connection when you expect it to finish in a controlled fashion, so it can do its house-keeping itself or
  • (when you use a global temporary table with "not transactional share by all") by a "DISCONNECT" event that would move the "log contents" of (even unexpectedly) disconnected connections to a permanent table.

However, both approaches are not fail-safe, apparently, as the logging is delayed, and the database could crash in-between. So a second parallel transaction seems more appropriate IMHO.

permanent link

answered 04 Apr '14, 05:04

Volker%20Barth's gravatar image

Volker Barth
30.3k300452660
accept rate: 32%

edited 04 Apr '14, 06:34

> NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal.

That suggestion filled me with dread at first, but that's just because (a) I'm so used to the way transactions and connections work together in SQL Anywhere and (b) I'm so used to using events to create independent transactions for just such the requirement you have.

Recognizing that NOT TRANSACTIONAL on a permanent base table would be a, well, permanent thing, and would affect all users, all transactions, all connections for all time... I can't see why not.

Presumably each single operation (INSERT, UPDATE, DELETE) would be atomic (it either works, or it doesn't, all or nothing)... like it is for temporary tables, even if a single operation affects many rows.

Implementation may or may not be easy... since the table is permanent, the data does get written to the database file, and there is checkpointing to consider (a vast impenetrable swamp of implementation details :)

But, logically speaking, no objections here. Does anyone besides Arthoor want it?

permanent link

answered 04 Apr '14, 08:01

Breck%20Carter's gravatar image

Breck Carter
25.7k427586845
accept rate: 20%

edited 04 Apr '14, 08:05

But, logically speaking, no objections here. Does anyone besides Arthoor want it?

I don't know - however, if so, that would better be asked as a new "product suggestion" question, methinks...

(04 Apr '14, 11:13) 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:

×47
×6

question asked: 04 Apr '14, 04:26

question was seen: 530 times

last updated: 04 Apr '14, 11:13