Re: Replaceing records

On Thu, 2003-09-04 at 15:52, Greg Stark wrote:
>
> Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
>
> > This problem would be easily solved if the current transaction would not
> > be automatically rolled back on the failed insert. Given this, it would
> > be as easy as trying the insert, and if fails, do the update.
>
> Yeah, that would be nested transactions, it's on the TODO list :)

Very good :) The sooner implemented the better ;)

>
> Fwiw, even if you took that approach you would still need to handle retrying
> if the record was deleted between the attempted insert and the attempted
> update. Unless you know nothing is deleting these records.

In this case there's a burst of insert/updates and no deletion for sure.
In any case it would be acceptable is sometimes the transaction fails,
but only if it happens with a very low probability.
These records are deleted only after a considerable time after all
updating is finished.

> Is there any possibility of moving this insert outside the transaction into a
> transaction of its own? If the rest of the transaction commits but the
> insert/update hasn't been committed yet is the database in an invalid state?
> If not you could try postponing the insert/update until after the main
> transaction commits and then performing it in its own transaction.
>
> A more complex, also flawed, approach would be to do the insert/update in a
> separate connection. This would mean it would commit first before the rest of
> the transaction was committed.

Any of these is out of question. Or all should succede, or nothing. The
problem here is that "success" from a logical point of view can happen
also when some individual queries fail. This is where nested
transactions can come handy, or the possibility of by default continuing
the in-process transaction instead of failing it (as Oracle does).

> Out of curiosity, what does the mysql syntax look like? How would you handle
> something where the insert and update were quite different like:
>
> INSERT INFO foo (pk,value,count,date_ins) values (?,?,1,now())
> OR UPDATE foo set value=?, count=count+1, date_upd=now() where pk = ?

No idea, I'm not using mysql. Just the problem was the same.

Cheers,
Csaba.