You need a persistent FIFO message queue where you insert all committed changes. The device consume from this queue in the same order as they were committed on the sending side.

Oracle has a great feature set around this…



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 01 September 2015 06:26 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Strategy to get a sequence in a strict ascending order for a log table





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?











[Non-text portions of this message have been removed]