Hi everyone, I think we have a problem related to the deletes transaction behavior:here is the problem:
Our delete starts by searching the tree to identify delete tuples based on the delete statement conditional clause. It follows that by inserting delete tuples in primary index, followed by updating secondary indexes, followed by a commit on the PK The problem happens if after searching the tree and identifying the records to be deleted, one of those records was updated. This will cause the record to be deleted in the primary index even though it might not meet the conditional clause. Moreover, the new entries in the secondary indexes will remain without their record in the primary index. In order to fix this, we need to do one of the following: 1. lock the records when we do the search to identify the records to be deleted OR 2. when performing the delete, we double check that the record we're deleting is the same as the record we find when we do the actual delete A better way would be to perform the delete as we do the search since there is no need to do the whole search, materialize then perform the delete. There is a change I got something wrong. Did I? Thoughts?
