|
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. |
|
A wild guess: You may be able to get a notification about a rolled back transaction by using an temporary transaction-scope mutex:
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.
(23 Jan, 09:28)
Volker Barth
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.
(24 Jan, 08:32)
ArcoW
Replies hidden
...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.
(24 Jan, 11:05)
Volker Barth
|
|
FWIW, there are also connection properties LastCommitRedoPos and Rlbk you can check, also for different connections with sufficient privlege:
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;
|
|
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:
|
|
Thanks for all the input. What I have come up with now is the following:
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. 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...
(20 Feb, 03:53)
Volker Barth
|
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...