What`s the best way to simulate Oracle Autonomous Transactions on SQLA?

I`m think publishing an webservice on database and create a stored funcion/procedure to consume it.

That`s the better option?

asked 10 Nov '09, 02:13

Zote's gravatar image

accept rate: 43%

edited 15 Nov '09, 10:49

Breck%20Carter's gravatar image

Breck Carter

In this case I think that my solution (publish and consume webservices in database) is the best option.

permanent link

answered 02 Dec '09, 11:31

Zote's gravatar image

accept rate: 43%

There is no direct way of doing an autonomous transaction in SQLAnywhere.

You can get close to the same thing by triggering an event that will then perform the transaction.


create table error_log(
         recorded timestamp default current timestamp,
         details  long varchar

create event ev_Log_Error
    insert into error_log( details )
      values( event_parameter( 'details' ) );

create procedure Log_an_Error( in @details long varchar )
    trigger event ev_Log_Error( "details" = @details );

Then you can add an error message to the log at any time by using:

call Log_an_Error( 'this is an error' );

Some things to note - the size of the parameters passed to the trigger is limited by the database page size. If you need to pass larger pieces of information, then it would be best to create a global temporary table non transactional shared by all and insert the data into this table and pass an "id" of the newly inserted row to the event - the event can then pull the data out of the temp table and do what it needs with it.

Your second requirement - waiting for the transaction to complete before proceeding is a bit more difficult. One method, but not really a decent one (for several reasons), would be to poll the above mentioned global temp table to wait for the row to be deleted and have the event delete the row once it has processed it.

A perhaps better method would be to use WAITFOR AFTER MESSAGE BREAK statement to cause the main request to pause, and then have the event send a message using MESSAGE ... TO CONNECTION n to the main request to continue. (You can easily pass the connection ID of the main request to the trigger as another event parameter) The issue to be aware of here is that without care, this could cause a deadlock situation if there is not enough server workers to process the event.

permanent link

answered 11 Nov '09, 03:14

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 11 Nov '09, 11:46

But with this solution, to get some result (id for example) I'll need to use some global temporary table right?! And now what's the best: your ou mine "workaround" ?

(11 Nov '09, 11:32) Zote

Yes, to get a result pass from the trigger to the main request thread, you would need to pass the information between the two threads by either using a global temp table shared by all or, if the amount of info is small (less than a page size of data), you can pass it as the content of the MESSAGE ... TO CONNECTION (if that is the method chosen to synchronize the two threads).

(11 Nov '09, 11:42) Mark Culp

To expand on what Mark said... when an event is executed (triggered), it gets a separate connection, which is why it can commit separately. It also means the event runs asynchronously, unlike a procedure call which runs synchronously. In other words, events run in "fire and forget" mode.

Like Stan Lee said, with great power there must also come — great responsibility! Events are wonderful things. Manually-executed events (as opposed to regularly scheduled ones) are even more wonderful.

The great power is "commits separately". The great responsibility is solving the "bit more difficult" problem of which Mark speaks. I can personally testify that the effort is worth it... I have developed a commercial multi-threaded application which runs entirely inside SQL Anywhere, based on both scheduled and manually-executed events: the Foxhound database monitor. (that's not intended as a pitch for Foxhound, but for SQL Anywhere, without which I would have been floundering in C ten years from now)

permanent link

answered 11 Nov '09, 09:04

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

If your autonomous transaction is independent from the calling transaction, a manually triggered event would suffice.

permanent link

answered 10 Nov '09, 14:54

Dmitri's gravatar image

accept rate: 11%

But, in this case, I need return an value...

(10 Nov '09, 16:33) Zote

Just to add a v17 feature to that real old question:

Based on Mark's excellent suggestion of a separate event connection, with v17 you could use a semaphore object to synchronize the current connection and the event connection, say something like the following in the current connection:

TRIGGER EVENT theSecondConnection;
WAITFOR SEMAPHORE SEM_WorkDone; -- that will block the connection

and within the event handler, you would finally call


That will then resume the waiting connection.

(Note, in contrast to a synchronization via WAITFOR AFTER MESSAGE BREAK / MESSAGE ... TO CONNECTION, you cannot exchange state information here, but you could also use a global temp table shared by all or the like to do so.)

permanent link

answered 23 Nov '15, 16:09

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

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



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:


question asked: 10 Nov '09, 02:13

question was seen: 3,261 times

last updated: 23 Nov '15, 16:09