Background: see my earlier posts Avoiding Lost Updates

and Avoiding Lost Updates #2: Updateable Views

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.

**Solution #1:**

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.

**Solution #2:**

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.

**Solution #3:**

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.

### Like this:

Like Loading...

*Related*