Kbase P124644: ASSIGN NO-ERROR causing unique index violation can corrupt temp-table
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/6/2009 |
|
Status: Verified
SYMPTOM(s):
ASSIGN NO-ERROR causing unique index violation can corrupt temp-table
Temp-table is NO-UNDO
Updating temp-table using ASSIGN NO-ERROR statement
Assigning duplicate value to field(s) in unique index
ERROR-STATUS:GET-MESSAGE() method reports following error:
** <file-name> already exists with <field/value...>. (132)
Updated record can no longer be found using unique index
Record shows assigned values when found using other index or ROWID
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
This is expected behavior.
It is a side effect of a number of design decisions in favor of providing the best possible performance.
Unique index violations are not caught until the after actual writes to the table take place. Checking before the write would introduce a performance overhead that in most cases is avoidable because the application logic itself is designed to avoid these errors. But as a result, when these errors are caught the only way to recover is to perform a roll-back operation.
For this roll-back to be possible the changes to the table must take place within a transaction block. The mechanics involved in making the roll-back possible (keeping before-image notes and such) have a substantial overhead.
For database tables, the 4GL will always ensure the transaction is in place - they are typically more permanent storage and thus their integrity is considered more important than getting the maximum performance.
Temp-tables in contrast are meant to be temporary and are usually expendable as a result. So for performance reasons, by default no transaction blocks are started for them.
And if they are defined to be no-undo they are kept outside of the transaction handling even if a transaction is active. This is common practice, again to get the best performance for most use cases.
Because of this combination of decisions, a temp-table that is kept outside of a transaction can not recover from unique index violations and similar error conditions.
FIX:
If the application logic requires this error to be handled, ensure the updates to the temp-table are handled within a transaction block.