Hello Anthony, First off...
> P.S. amazing project - not just the library, but also your responsiveness, > website, docs, and everything else Thanks a lot for the encouragement > 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. Yes, such ideas have been around on the long-term roadmap. For instance: Simulating the SQL Standard MERGE https://github.com/jOOQ/jOOQ/issues/1067 Simulating MySQL's INSERT .. ON DUPLICATE KEY UPDATE: https://github.com/jOOQ/jOOQ/issues/1066 (both tickets contain only ideas, not actual solution proposals) The trouble is always the same. The potential for race-conditions cannot be completely avoided, neither with an INSERT .. ON EXCEPTION UPDATE, nor with an UPDATE .. ON NO_ROWS_AFFECTED INSERT scheme. Also, both schemes have corner-cases, if jOOQ doesn't operate on a transactional connection (i.e. if auto-commit is set to true). Nevertheless, I'm very open to implementation suggestions for the three currently supported statements: - INSERT .. ON DUPLICATE KEY UPDATE (MySQL) - MERGE .. KEY (H2) - MERGE .. USING .. ON .. WHEN MATCHED .. WHEN NOT MATCHED .. (SQL standard) So let's discuss > * 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) Step 3 can only happen in an auto-commit environment. If the JDBC connection is "transactional", no other transaction will be able to see the results of the INSERT. However, it may well be that two transactions attempt to insert the same record. T1 commits, successfully, T2 commits, fails, but doesn't execute the 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). For jOOQ, "low" is still high enough to carefully re-consider the options. If jOOQ implements something that can lead to undesired race conditions, it has to be at least documented very thoroughly in a Javadoc warning section... > 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. Yes, that seems like a sledge-hammer approach :-) Are there any other options, anyone on the group? Anthony, have you considered asking a Stack Overflow question (although I imagine this one already exists...) Cheers Lukas
