Event for rollback?

[VolkerBarth]

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...

[VolkerBarth]

After considering the fact that via savepoints, operations can be rolled back whereas the containing transaction is still committed, I don't think there's a way for a monitor to "know" whether a different connection has committed all its operations or not.

I think a better approach would be to rely on common locking schemes:

• Instead of "messaging" the event that it should do something, the connection could insert or update a row (say, a bit column which it sets to 1) in a help table showing it is up to change some data, and message that row's ID to the event.

• The event would then try to read that row with a isolation level preventing dirty reads - but would be blocked until the original connection has done its commit or rollback.

• When the lock is released, the event continues to run and can check the row's column's value - if it's set to 1, the separate connection was successfully committed, and the further (expensive) updates can take place. If the row is missing (in case it has been inserted by the original connection) or the column is set to 0, the original transaction (or the part of a savepoint that hat modified the row) has been rolled back, and no further (expensive) updates need to be done.