I am trying to create a log table. This table has a "version" column, it is an
incremental number.
In each transaction (ie a purchase, sale,...) , I add a record in this log
table. The "version" column is assigned in the insert trigger using the value
of a sequence.
This version number is used to update mobile devices. They ask something like
"send me all changes until version x".
My problem is I need that the log table shows the records in a strict
ascending order. It is not important if there are gaps or not.
Example of the problem:
1-Star transaction A
2-Changes
3-Insert LOG record, version=1 (get it from sequence)
4-More changes
5-Commit A
Now, between the 3 and 5 of A transaction, a concurrent connection do a
smaller transaction:
1-Star transaction B
2-Changes
3-Insert LOG record, version=2 (get it from sequence)
4-Commit B
And the commit B is done before the transaction A finishes. Then, in the log
table, for an instant, I have a record with the "2" as value. The table will
not have the "1" until transaction A finishes.
If before the A finishes, a mobile device asks for any changes, the server
sends only "2" ("1" is not visible yet). The device updates its internal state
and now it is updated to "2". The "1" log will never be applied.
When transaction A finished, the "1" is added in the log, but now it is too
late because the mobile device has been updated to "2" version.
Which strategy can I use to solve this problem?
