Would using SELECT FOR UPDATE and a subsequent INSERT guarantee uniqueness in text column? I have a table with a text column that I need to be unique. Because I can't put a unique constraint on the column in MySQL, I want to know if the following approach would guarantee uniqueness in the column:


Create a varchar column for holding the hash of the unique text column. Put a plain INDEX on it. 
Start transaction
Use SELECT FOR UPDATE to see if hash exists
a) Use a subsequent INSERT if hash doesn't exist

b) If hash exists, SELECT by unique text to see if it exists. If unique text does not exist, do a subsequent INSERT, otherwise do 'UPDATE'
End transaction


If I use the above approach for all inserts in that table, can that guarantee uniqueness?
Yes, that should work.  However, consider either of these one-liners:

INSERT IGNORE ...

INSERT ... ON DUPLICATE KEY UPDATE ...


If you are using AUTO_INCREMENT, be cautious about 'burning' ids.

Links:


https://stackoverflow.com/questions/24616315/how-to-use-insert-on-duplicate/24617186
https://stackoverflow.com/documentation/mysql/866/insert