Duplicate RowID's in Query

R

redwards1966

New Member
I'm exporting table rows along with their rowid for some tables that don't have primary unique keys and using that rowid as a means to do some quasi synchronization to another system.

On some tables I'm getting duplicate rowid's in the export but it's from a row already exported in the same query. I'm using NO-LOCK on the query because for some longer running exports I don't want to block other processes from altering data. Is it the NO-LOCK that is causing the same row to appear multiple times in the query? For instance if the row is updated during my export run. Is there any way around this or a better way to do it?
 
If the query involves a join then the record you get the error with is proably retrieved more then once.
It is impossible to get the same rowid with a query involving only one buffer.
NO-lock has got nothing to do with it. same applies for creating records during processing of the query. A rowid is a databasewide unique identifier of a record. This will always be unique unless the data is dumped en reloaded. (I assume you are using rowids of tables and not temp-tables :awink:).

Casper.
 
Hi Casper,

The query I'm using is a straight select from a single table. This is a rather large table, about 9 million rows and takes about 2 hrs to dump to a text file. I redid the query last night and got duplicates on about 4 rowid's.

The query is a simple 'FOR EACH tableName NO-LOCK' and it's using a single buffer, no temp tables.

Any ideas?
 
How do you store the rowids? Since rowid is a hex value so if you don't use any format phrase in the export try putting in format 'X(12)' for the rowid as a character representation. The default format is X(8) which means you probably don't have the entire hex value in the string, this can obviously casue 'duplicate' values.


HTH,

Casper.
 
redwards1966 said:
I'm exporting table rows along with their rowid for some tables that don't have primary unique keys and using that rowid as a means to do some quasi synchronization to another system.

On some tables I'm getting duplicate rowid's in the export but it's from a row already exported in the same query. I'm using NO-LOCK on the query because for some longer running exports I don't want to block other processes from altering data. Is it the NO-LOCK that is causing the same row to appear multiple times in the query? For instance if the row is updated during my export run. Is there any way around this or a better way to do it?
Click to expand...


I think the problem is what you have already mentioned. The rows are updated during the export. If someone, or your application allows that the fields of the primary key are updated then it can happen that these records will again be exported. If for instance the primary key is a number, and you update it to a higher number the for each will read it again. It does not matter if you use exclusive-lock or no-lock. There are a number of solutions to your problem.
1) Make sure the process runs single user on the database.
2) Dump all records in a temp-table first, and check if the rowid was already exported. If so, then overwrite the record or skip the record depending on your needs. At the end dump the temp-table to wherever you like.
3) Disallow updates to primary key fields (it is never a good idea to allow updates on such fields)
 
Ok, it's probably the updating of the primary key fields because on other tables we don't have this same problem. The system is one we obtained from a 3rd party and we're not able to make changes (for obvious reasons) which is why we are doing this synchronization in the first place. For the table in question they have a primary key with 11 fields in it including things like zone, rack, row and bin which for an inventory table could be updated quite frequently when stock is being moved around. I come from a MS SQL background so having a primary index with this many fields in it strikes me as odd but perhaps it's warranted in this case. Is this something common with Progress? In the case of another stock table they have 13 fields in the primary index.
 
In old applications it is common to have primary keys with large numbers of fields. It was a way to determine sort order. With more modern applications one tends to get short or single field indexes and sort order determined with BY clauses.

So, common yes, modern no.
 
You must log in or register to reply here.
Back
Top