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]

A wild guess: You may be able to get a notification about a rolled back transaction by using an temporary transaction-scope mutex:

• The connection C "sending the message" to the event would also tell the name of a (connection-specific) transaction-scope temporary mutex M it has created, and will then lock that mutex in exclusive mode. Then it does its desired data modifications.

• The event E1 would trigger another event E2 that will have to work on the specified mutex M by trying to lock it.

• Before the connection C is about to commit, it drops the mutex M. Event E2 then will get an error (SQLCODE -1804 SQLE_MUTEX_DROPPED), and knows, the connection has been committed, and can validate the data modification.

• In case C's transaction is rolled back, the mutex M will be automatically released, and so E2 will be able to lock that mutex and can assume that C's transaction was rolled back, thereby also invalidating its derived modifications. E2 can undo the actions and commit (or rollback, whatever fits best). The mutex M will then be automatically released.

[VolkerBarth]

To add: There's more to do in case connection C does not only do a rollback but is disconnected before it does it commit: Then the temporary mutex is dropped automatically, so the 3rd point (handling -1804) should verify the connection does still exist, and otherwise treat this as a roll back.

[awitter43]

This will not work, for the connection should actively do something just before the commit. When that triggers an error, the commit will not be done. Even worse, the application doing a few updates within a transaction will just commit or rollback, without anything else, so we can't see the commit/rollback unless the app does something extra, that's not what we need.

[VolkerBarth]

When that triggers an error, the commit will not be done.

...unless there's error handling code handling that error. 🙂

But of course, if the connection cannot or should not be modified, this approach won't work. Then I guess it might be easier to use my approach from today with regular blocking.