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

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

[VolkerBarth]

FWIW, there are also connection properties LastCommitRedoPos and Rlbk you can check, also for different connections with sufficient privlege:

LastCommitRedoPos: The redo log position after the last COMMIT operation was written to the transaction log by the connection.
Rlbk: The number of rollback requests that have been handled.

A transaction doing some committed DML statements should have an increased value compared to the transaction's begin. When also comparing the number of rollbacks, you might (or might not) know whether an expected transaction has been committed.

select sci.number, sci.name, connection_property('LastCommitRedoPos', sci.number) as LastCommittedLogOffset, connection_property('Rlbk', sci.number) as NumberOfRollbacks
from sa_conn_info() sci;

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

[awitter]

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.