How to coordinate two sessions writing the same row to a MySQL table
Here's the best solution I've been able to come up with, using a write-read approach:


INSERT using ON DUPLICATE KEY UPDATE; (without first checking to see if the row exists)
if the value returned != 0, then that's the PK of the new row;
else a row must already exist with that value, so SELECT it;
if the SELECT can't find the row, then throw an exception because this is unrecoverable.


This has the following advantages:


it doesn't require any transactionality or a different isolation level, because the insert-if-not-exists statement is already an atomic operation;
it executes at most 2 SQL statements (insert-select) instead of 3 (select-insert-select), making the calling code simpler;
since we're not using INSERT IGNORE, if the INSERT statement throws an exception it will be for a legit reason - e.g. auto_increment has reached the max value of id column - and thus we don't have to worry about needlessly logging duplicate key exceptions or ignoring actual database problems.