Re: Replaceing records

Richard Ellis <rellis9(at)yahoo(dot)com> writes:

> True, if the row does not already exist. But in that situation,
> because of the unique constraint premise in the original quote, there
> is always going to be at least one failed transaction. So the battle
> is already lost before it's even begun.

Well, no, that was the point. Ideally he wants to try to catch the duplicate
without producing an exception because he can't do nested transactions.

There's no parent record to this record in another table? You could lock the
parent record with SELECT FOR UPDATE, then do a SELECT count(*) on this table,
and do the insert or update as appropriate, then release the lock on the
parent record.

That's not great if you're doing lots of inserts on the same parent record, or
if the parent record is being updated frequently, but it's way better than
doing a table lock.

FWIW:

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:

> Defining a column UNIQUE is a last line of defense, and aborted actions
> because of constraint violation should be the exception, not the normal mode
> of operation.

Well that's one approach. I don't agree. The database is a tool, unique key
constraints are a tool, they're good at doing certain things, like ensuring
atomic semantics for cases just like this. Why try to reinvent the wheel using
inferior tools in the application. You're doomed to fail and introduce race
conditions.

In fact in this situation I usually prefer to try the insert and handle
exceptions over any of the other approaches. It's cleaner, clearer, faster in
the normal case, and has the least likelihood of race conditions (none if the
table never has deletes).

> Wherever one is using this "REPLACE INTO" language violation, the client
> application or even something in front of it is generating ID's but it's not
> sure if it is sending down a new or existing one. The real question is "why is
> this piece of garbage unable to tell the ID is newly created or has to exist
> already?"

Well, because that's the database's job. If the application tried to do that
it would have to solve all the same concurrency and atomicity issues that the
database already solves it. That's why I'm using a database in the first
place.

--
greg