Why you don't use IN AUTONOMOUS TRANSACTION DO? So, when the sequence get its value you insert the row in the log table.
Greetings. Walter. On Tue, Sep 1, 2015 at 12:26 PM, [email protected] [firebird-support] < [email protected]> 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? > > > > > > >
