Event for rollback?

[awitter]

I know there is no event for when a certain transaction has a rollback, but it really would be helpful for us to have one:

A connection sends a message from within a transaction to a running event to update a certain table (yes, both outside the transaction and only updates by that single thread) When the transaction rollsback we would like to know so we can mark those changes as invalid. When the transaction is committed the changes are correct and stay 'active'.

This is a better way of using one table instead of using materialized views while having multiple connections using such a materialized view: - impossible to refresh the mat.view when in use - takes a long time to refresh the materialized view, even after just one change

Question: How can we see that within a connection a transaction has been rolled back? (And what level) Best way could be:

Create event TransactionRolledback
type Rollback
handler
begin
  update tabChanges
  set ChangeIsValid = 0
  where ChangeConnId = event_parameter('ConnId')
   and ChangeLevel = event_parameter('transaction_level')
end;

Of cause the real code we would use is different, but this example should be enough.

[VolkerBarth]

BTW, what do you mean with "transaction level"?

AFAIK, SQL Anywhere does not have several transaction levels but uses savepoints as "sub transactions".

This does also mean that operations within a savepoint can be rolled back whereas the whole transaction is still committed. That's probably difficult to diagnose...

[awitter]

Thanks for all the input. What I have come up with now is the following:

• the transaction writes a record in a help table
• the transaction sends a message to an existing event, including ref to the written record and changes to be done
• the event captures the message, make the changes (and commit, because dirty-reads must continue while the transaction is processing) and remembers the changes in another record and commit
• the event has a loop in which the original record is tested if it's still locked, removed or available
• when locked: skip the record
• when removed: transaction has been rolled back, so revert changes (and commit)
• when can be read: remove changes-record and the freed record and commit

This works, however, the delete by the event might have effect on the table used by the transactions. So the delete might be blocked by transactions. This can cause a dead lock.

I haven't encountered this yet, but when it does, it will not be seen (for it's a deadlock between messages and record lock) therefor I might try this with semaphores, however, I need a new semaphore for each message, making use of execute immediate to create the semaphore within transaction. I don't know if this will do an auto commit.

[VolkerBarth]

You can also use indirect identifiers to create semaphores with "variable names" without using dynamic SQL. As to automatic commit: The creation of a permanent semaphore does so (and therefore cannot be used within an "open transaction"), temporary semaphores are created without an auto commit, comparable to my first suggestion with temporary mutexes...