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.2k284662
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
31.5k318461676
accept rate: 33%

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
27.0k444614889
accept rate: 21%

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:

×48
×6

question asked: 04 Apr '14, 04:26

question was seen: 652 times

last updated: 04 Apr '14, 11:13