Hi Thomas, > Perhaps your read process couldn't delete the record but mark it with a > flag and a cleanup process scheduled e.g. in the night deletes the > marked records followed by initiating a manual SELECT COUNT(*) on that > table in case of co-operative garbage collection.
Instead of marking rows we use timestamps to find the old records that we can delete, but the idea was exactly the same: to delete records and then a manual SELECT COUNT(1) to force the garbage collection (actually followed by a re-computation of indices to ensure that the queries we need are tuned). All of this with two problems: * There is no "night" for us: the customer works 24/7/365, and the work load is equally distributed around the clock. What we thought is that, instead of deleting 1 million rows at once, we can delete a few hundreds/thousands, do the select count(), wait a bit, and continue deleting. As long as the rate of deletion is bigger than the inserts, we would be safe. * We are concerned that, during the "select count(1)" phase (or during the recomputing of the indices), the database would be irresponsible and the new inserts would be blocked or partially blocked because of that. > Btw, in respect to client transaction management. Are you using hard > commits or soft commits aka commit retaining? Hard (aka "normal" :-)) commits. Would it make any improvement if we had used retaining commits? Regards, Fernando
