ASA & ML 9.0.2.3207

Hi,

In our application Mobilink'd between a consolidated (London) and remote
(New York), I'm having a problem with automatic sequence numbers that are
used as a part of primary key in one table. Unfortunately, due to DB
complexity, it was impossible to use UUIDs as primary key, so it is simply a
sequence of numbers, with a prefix 'US' or 'GB', depending which site you
inserted a row from.

The latest used sequential number is stored in the table COUNTERS. For
storing new transactions, that number is retrieved, incremented by 1, used
as a primary key for a new transaction, and updated to the COUNTERS table.
So, for every new transaction record, what happens is:

SELECT sequence_no FROM COUNTERS INTO :li_seq_no WHERE CODE=...;
li_seq_no = li_seq_no + 1
INSERT INTO some_table VALUES (li_seq_no +'US'/'GB', ........);
UPDATE COUNTERS SET sequence_no = li_seq_no WHERE CODE=...;

However, every now and then, the remote DB writes back to the consolodated
DB a sequence number that has been used already, sometimes even a number
smaller by 3 or 4. Syncs are done every 3 minutes, so it simply can't be the
case that between syncs, 4 records are inserted in consolidated, and then,
just before the sync, someone at remote inserts just 1, which could
overwrite the consolidated with a lower number. I looked through translated
transaction log, but it's a nightmare to try to make sense of thousands of
transactions, but it was clear that that was not a scenario.

My upload_update is the usual "UPDATE COUNTERS
SET ... sequence_no = ? ... WHERE CODE = ?"

Is there some way that I can define new/modify this script that will stop
the sequence number being updated to consolidated DB if a number that it's
trying to update (from remote) is lower that the current value? And at the
same time, instruct the Remote to update its own sequence_no to the value
from consolidated?


Thanks

Just a follow-up, this is the sequence of transactions, filtered from
transaction log, column "sequence_no2" in question:

1.
UPDATE DBA.counters
SET sequence_no2=9501316,
last_modified='2006-07-07 10:34:12.515001'
WHERE CODE='05';
---
2.
UPDATE DBA.counters
SET sequence_no2=9501318,
last_modified='2006-07-11 15:12:08.734001'
WHERE CODE='05';
---
3.
UPDATE DBA.counters
SET sequence_no2=9501319,
last_modified='2006-07-11 15:15:10.671001'
WHERE CODE='05'
---
4.
UPDATE DBA.counters
SET sequence_no2=9501320,
last_modified='2006-07-14 11:42:08.125'
WHERE CODE='05'
---
and then, the setting back from Remote...
---
5.
UPDATE DBA.counters
SET sequence_no1=4500776,
sequence_no2=9501316, <----(remote setting it back by 4 13 days
later!!!)
last_modified='2006-07-20 13:25:11.046001'
WHERE CODE='05';

---

I know, by looking through full transaction log, that first 4 transactions
were made in consolidated, and only 5th in remote DB.

In this fifth transaction, it updates both sequence_no1 (which was
incremented at that transaction), AND ALSO sequence_no2 which was not
changed at all. I guess that upload_update script takes both columns from
the remote, even if only "sequence_no1" was changed, and it overwrites
"sequence_no2" in Consolidated with number already used.

Which still begs the question why is "sequence_no2" in remote DB so out of
date, when consolidated has a default download_cursor script
"SELECT code, sequence_no1, sequence_no2, ... FROM COUNTERS WHERE
last_modified >= ?"

And it doesn't happen ALWAYS. Just sometimes, twice a week or so. And
sometimes it runs for 1-2 months OK.

We probably need to see some actual code for the schema, plus the
download_cursor and upload_update scripts.

Remember that the upload happens first, then the download, and the
entire row is uploaded if anything changes. So, it is possible for old
data on the remote to clobber newer data on the consolidated if you
don't have conflict resolution scripts (this might be a conflict
situation).

If this is a true representation of how the counter is incremented...

....then if you may run the risk of two users calculating the same
value li_seq_no. A alternative approach is to UPDATE COUNTERS to
increment sequence_no first, then retrieve and use the incremented
value; that way, a second user will be blocked until the first user
commits, rather than getting the same value.

However, I can't see that having anything to do with the symptom you
are having.

Breck

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

Thank you very much for even looking at it. I realise it's not down to
Mobilink as such, but rather because of a DB architecture that is not the
best suited for Mobilink.

guess I didn't get the sequence of events right. It is in fact exactly as
you said:
1) Retrieved first
2) Incremented
3) Updated to database
4) Use incremented value

But anyway, as you said yourself, that would not cause the problem that
we're getting.

DB schema I presented yesterday was modified, basically stripped of all
other fields that are not necessary for this process. There is a table that
stores two latest numbers for two automatic sequences, per code provided

table dba.counters(
code char(4) not null,
sequence_no1 integer,
sequence_no2 integer,
last_modified datetime default timestamp,
primary key(code)
);

Download cursor:
'SELECT CODE, SEQUENCE_NO1, SEQUENCE_NO2 FROM counters WHERE last_modified
Upload Update:
'UPDATE counters SET SEQUENCE_NO1 = ?, SEQUENCE_NO2 = ? WHERE CODE = ?');

script that will overwrite the "sequence_no2" value only if the new value is
larger.

The most puzzling issue is that this did not happen between two syncs, which
would make sense. If value is 9501315, consolidated inserts 5 new records,
but remote only one before the next sync, then 9501320 in consolidated COULD
get overwritten with 9501316 from remote. But from my second post yesterday,
it can be seen that "guilty" update from the remote DB, setting the value to
9501316, came 13 DAYS after 9501316 was written down first in consolidated
DB.

---7th of July---
UPDATE DBA.counters
SET sequence_no2=9501316,
last_modified='2006-07-07 10:34:12.515001'
WHERE CODE='05';
....
....
....
---14th of July---
UPDATE DBA.counters
SET sequence_no2=9501320,
last_modified='2006-07-14 11:42:08.125'
WHERE CODE='05'

---20th of July---
UPDATE DBA.counters
SET sequence_no1=4500776,
sequence_no2=9501316,
last_modified='2006-07-20 13:25:11.046001'
WHERE CODE='05';


???... Now, how could that happen, how can remote have the value of 9501316
for 13 days in between there have been countless syncs and quite a few
records going in between?