On Tue, Sep 1, 2015 at 12:26 PM, kokok_kokok@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

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?