With a Read Committed ODBC connection I'm getting the odd record lock when reading from OpenEdge's Auditing tables (Database user table CRUD operations)...
ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB._aud-audit-data.
I understand isolation levels and know that this can be resolved by changing to read uncommitted.
Obviously the main danger in doing this is that dirty reads become a possibility. Dirty reads would definitely cause my solution issues so would be a no-go. But...
Does the very nature of OpenEdge Auditing prevent possible dirty reads of the source records being CRUDed?
here's my thinking behind the question... With my configuration, OpenEdge audit records are created upon completion of CRUD operations against the user tables. I want to make sure that I only read audit records where the user table CRUD operation has committed to the database. I suspect that this is always the case and that the audit records are only created after committal of the user table CRUD transaction. If this is the case then it would only be under exceptional circumstance that the audit records transactions would be rolled back and therefore dirty reads of the audit records are not really a possibility...
Is that feasible? Can anyone clarify the life-cycle of a user transaction followed by an audit transaction in OpenEdge? I assume it is unlikely but possible for the audit transaction to fail, in this case what happens to the original, audited, CRUD operation?
it is possible to read an audit record for a transaction that hasn't been committed because audit records are created before the transaction is committed. If the transaction is rolled back, the audit record is removed.
My understanding is that a record update audit is like a write trigger, so it fires at the end of record scope or transaction, whichever occurs first. Therefore, audit records keep locks until committed, and can be undone with the transaction.
Your application will have to wait forever, or readpast N number of minutes or come back in another pass.
it is possible to read an audit record for a transaction that hasn't been committed because audit records are created before the transaction is committed. If the transaction is rolled back, the audit record is removed.
My understanding is that a record update audit is like a write trigger, so it fires at the end of record scope or transaction, whichever occurs first. Therefore, audit records keep locks until committed, and can be undone with the transaction.
Your application will have to wait forever, or readpast N number of minutes or come back in another pass.
Thanks both, that answers my question. I've configured the app to retry 3 times and then report failure on fourth.
I would go for the read N minutes back but that leaves the possibility of not processing some audits (the app processes audit records)
(this website is really screwed on a mac - safari - not sure what combination exactly but pressing a f button starts a search)
> this website is really screwed on a mac - safari - not sure what combination exactly but pressing a f button starts a search
The same issue on Chrome. Very annoying feature. Brrr...
This can be bad if the transaction scope is larger than the record scope, but a record scope change (find statement) fires the trigger in a sub-transaction (an iteration of a repeat statement), then you could roll back only the audit piece, yet your changes are committed. This is one reason why trigger-based replication or code in triggers in general are bad. I am surprised OE Auditing works like triggers, there might be differences.