Re: unload creating duplicate versions of edited records

> Not necessarily, it would also happen if the query were using an index and > that index key was being modified by some users so that the row shows up > again as the unload follows the btree. > Oh yeah, that. Which implies COMMITTED READ or CURSOR STABILITY may not be enough... Really, the safest way is to unload on a quiet db. REPEATABLE READ isn't much short of LOCK TABLE, and you can guess the effect of that on other people. At least LOCK TABLE will guarantee a consistent unload. With REPEATABLE READ, you can toss a coin as to whether your unload process wins or loses in a conflict with one other process. If there are many updaters out there, the chances of everyone suffering rises accordingly. Since you are on a 7.3 engine, the following won't help you much; I believe the 11 engines have a mode where you can pretend you are not seeing any updates, similar to Oracle's slant on this. However I'm not familiar with this mode. Probably someone else can comment.

Andrew Clarke wrote: >> Not necessarily, it would also happen if the query were using an index and >> that index key was being modified by some users so that the row shows up >> again as the unload follows the btree. >> > > Oh yeah, that. Which implies COMMITTED READ or CURSOR STABILITY may not be > enough... > > Really, the safest way is to unload on a quiet db. REPEATABLE READ isn't much > short of LOCK TABLE, and you can guess the effect of that on other people. > > At least LOCK TABLE will guarantee a consistent unload. With REPEATABLE READ, > you can toss a coin as to whether your unload process wins or loses in a > conflict with one other process. If there are many updaters out there, the > chances of everyone suffering rises accordingly. > > Since you are on a 7.3 engine, the following won't help you much; I believe > the 11 engines have a mode where you can pretend you are not seeing any > updates, similar to Oracle's slant on this. However I'm not familiar with this > mode. Probably someone else can comment. > > > That is COMMITTED READ LAST COMMITTED and it would probably not help here. This is a known situation. It happens because we're browsing through live data and not looking at a snapshot of data. Possible solutions: 1- shared lock on the table 2- use REPEATABLE READ (probably has the same disadvantages as above, and it will consume more locks) 3- Access the table using an index (check notes) 4- run the query on an RSS server with delayed apply (not possible with V7) 5- Put a timestamp on the records that save the last update timestamp. And include that on your WHERE condition 6- Avoid concurrent deletes/inserts (check notes) 7- Do the select into temp table and eliminate the duplicates (you need to define the criteria for elimination... check 3) and 5) ) Notes: . Unless you have a table with pending in-place alters (and even so I doubt it), you're probably having concurrent delete/insert of records. This situation would be difficult, if not impossible, to reproduce with an update. In this scenario, if you're able to control delete/insert while doing the unload you should be fine... Regards.