Thinking about it, there's probably no easy way to avoid race conditions
(in a true transactional DB at least) when inserting into a table with
unique constraints. The REPLACE syntax will definitely not do it,
because I can't imagine what it should do when 2 threads try to REPLACE
the same key in concurrent transactions. Both will see the key as
missing, and try to insert it, so back we are at the same problem INSERT
has.
Cheers,
Csaba.
On Fri, 2003-09-05 at 12:06, Peter Childs wrote:
> REPLACE INTO throws away data with out the user knowing that they
> have ditched the data. This means it has side effects. Standard
> Programming Theory says that functions should not have unintended side
> REPLACE INTO is in-fact a
>
> DELETE followed by a INSERT
>
> Its also unclear what replace should do with missing fields
> 1. Fill them in with the defaults.
> 2. Leave them alone.
>
> If 1 its very dangerous and if 2 you should know what you are
> changing anyway and the very least it should return "UPDATE" or "INSERT"
> depending on what "REPLACE" actually did!
>
> On the other hand Nesting are a good idea. There is one problem
> however. When do you impose referential integrity for deferred checks. at
> the last commit. or do you need a check references command.
>
> Its not the point that a UPDATE on most databases infers a COPY,
> CHANGE COPY, DELETE steps so you can roll back if nessessary.
>
> Replace also needs to know the table stucture to work Update,
> Insert and Delete don't they only need to check the constraints.
>
> As I'm sure I've said before SQL has huge holes and inconsistencies
> and needs a complete re-write. its like VHS-Video not the best just whats
> been sold to everyone.
>
> Peter Childs