Avoiding Lost Updates #3: The Solution
I’ve discussed this problem with my colleagues and have come up with a number of potential solutions, one of which we believe is bulletproof.
The desired property of such a solution is to reduce the probability that an update of a view will leave the row with the same “version” number, to as close to zero as possible. A bulletproof solution would have a probability of exactly zero.
Instead of taking the SUM of all the versions, shift the versions from the outer-joined tables (e.g. by multiplying by 10000, 100000, etc).
This reduces the likelihood of getting a false negative; but it doesn’t reduce it to zero, since it would still be theoretically possible to get an update that would cause compensating deletes and updates on the outer-joined tables.
Use ORA_ROWSCN instead of version.
This also reduces the likelihood of a false negative; in our view, astronomically so; but with a bit of mental gymnastics we can think of a contrived scenario in which the total of ORA_ROWSCN stays the same.
Cause an update on the view to always do at least one update of one of the non-outer-joined tables (even if it is updating a column to the same value); and don’t include versions from outer-joined tables in the summed version.
This means that version will now always increase whenever an update occurs; therefore, the probability is zero, and the solution is bullet-proof!
Thanks to Howard and Maciej for their help.