Csaba Nagy <nagy@ecircle-ag.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 :)
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.
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.
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 = ?
--
greg