Hello Jack,

> By deleting and re-inserting all after #204, all requested records are now being returned. Now there is a gap in primary key values between 204 and 217, which I can live with but will be curious to see if that continues.

The gap will remain; that's as designed. You could have prevented it by resetting the generator to 204 after deleting the records but before reinserting them.

Barring human interference, generators will never issue the same number twice (unless they wrap around), no matter how many records are deleted (something that the generator is oblivious of, btw). And that's a good thing. Generated PKs are guaranteed to be unique, not contiguous.

(A little while ago I had a nasty experience with AUTO_INCREMENT values in MySQL, which turns back the generator value if the most recent records are deleted. In this case, the deleted records went to an archive table. Before long, I got a PK violation in that table because two records with the same PK value had been deleted from the main table.)

Sorry, I don't know what caused your original problem. I probably would have looked if the transactions involved had been committed, and what the transaction isolation of the selecting process was (the one that didn't 'see' the records).


Kind regards,
Paul Vinkenoog