Ivan D wrote:
Thank you for your extensive post.
> Here's a practical example.
>
> You have tables with auto incrementing ids, these ids could also be foreign keys to other tables.
>
> As records get deleted you get gaps in the ids.
> They can potentially be recycled.
> You could copy over the data and re-assign the ids, but then records from other tables which
> relied on that id would be messed up, or you'd have to change those too (which could take lots of
> time)
I consider recycling PK's a very bad thing.
If you have to depend on referential integrity, then enforce it e.g. by using InnoDB and stating your relationships explicitely in the DDL.
> Unfortunately when reusing a previously deleted ID you are blindly creating the records, you can't
> reliably retrieve the first reusable id and then update it because someone else might be updating
> it right after you retrieve it, and then you would overwrite their data.
Now this could equally well happen when you send your statements, separated by ';', in one call to the database (if that would be possible). Sending them in one call *does not* mean they are handled atomically: you need transactions for that, (either simulated by LOCK TABLES ..., or real by using a transaction-safe storage engine).
> Another example.
>
> SELECT user,pass,logged FROM admin WHERE user='$user';
> UPDATE admin SET logged=1 WHERE user='$user';
>
> If you can process both of those at once you would ensure that the admin can only log once at a
> time.
Again: you are *not* processing them at once. There still can be a second login from another connection between the execution of the first and the second statement (even if you could send them as one), unless you take other precautions.
> Maybe there's simpler solutions to all these problems, but I havn't discovered them yet.
One of them would be not to recycle primary keys.
--
felix
Please use
BBCode to format your messages in this forum.