Hi Lukas, Thanks for the quick response.
I totally understand your view on those 'solutions', and was really just hoping to provoke an ingenious real solution from someone. I can't imagine anyone wanting to introduce races or hammers into a library that's used in any production environment, even if it does present warnings/disclaimers regarding the less than ideal behaviour. Failing such an ingenious proposal, I'll keep our workaround application specific. However, it would be nice if we could keep the application code concise by overriding the implementation of mergeInto for our scenario. Do you have any suggestion on where we could look for some ideas on how to do that? I would prefer to be able to just override this particular operation rather than maintain a fork of jOOQ with a patched MySQL-specific mergeInto. Having trawled again for elegant, efficient solutions, I'll be very pleasantly surprised if any surface after this many years of DBMSs being around. Cheers, Anthony On Wednesday, December 12, 2012 10:55:31 AM UTC, Anthony Harris wrote: > > Hi Lukas and all, > > I'm considering implementing simulated support for mergeInto for MySQL, > but before I do, I thought it'd be a good to run the idea past you to see > if it's already been tried before and if so, what issues held it back. > > First, I should explain why the onDuplicateKeyUpdate feature isn't > sufficient for our needs. Very simple: the unique key that we need to use > isn't the primary key. > > Without this feature, I'll be taking a less robust but quite > straightforward approach to our problem. > > * Try to insert > * when unique key constraint violation, try to update > * Raise/throw exception if no rows updated (another transaction deleted > the record between insert and update) > > For our use, that behaviour is acceptable, since the only scenario > involves a user who can retry the operation (also very low chance of > occurrence). Of course, the obvious alternative - reversing the order - > has a similar lack of robustness, in that a conflicting record could be > inserted between the update and the insert. > > I believe that the only locking that could completely avoid the race > conditions would be a table lock, which I expect wouldn't be acceptable in > the MySQL implementation of mergeInto. > > What are your thoughts on whether I should proceed with trying to > integrate some support for mergeInto for MySQL into jOOQ, or just leave it > as an application-specific operation? > > Cheers, > Anthony > > P.S. amazing project - not just the library, but also your responsiveness, > website, docs, and everything else > >
