I am using MySQL 5.6 and have an append-only “lookup” table that’s used to store reference data:

CREATE TABLE my_table (   id INT UNSIGNED NOT NULL AUTO_INCREMENT,   my_column VARCHAR(32) NOT NULL,    PRIMARY KEY (id),   UNIQUE KEY my_column_uk (my_column) ) ENGINE=INNODB DEFAULT CHARSET=utf8; 

Rows are inserted, but never updated or deleted. The table is small (thousands of rows at most), so query performance is not a concern.

In my code, I have 2 different processes (running identical code) which insert rows into this table. Each process has an in-memory write-through cache of the myColumn -> PK id value. Whenever a new value is put into the cache that doesn’t already exist, it’s synchronously inserted into the database table, then added to the cache.

How do I handle the scenario where 2 (or more) processes try to insert the same my_column value at the same time?

Since the table has a unique key constraint on my_column, my thought is to have both processes try to insert the new row and get the new PK id value. One of them will succeed, and can add the new entry to its cache. The other one will fail, but then it can fall back to querying for the id of the row that the other process inserted. I call this the “write-read” approach.

Is there a better way of handling this contention between the two processes? Would a “read-write” approach be better, potentially with a different transaction isolation level (“read uncommitted” e.g.)?

The one potential downside I see with the “write-read” approach is that it involves catching a duplicate key exception, and using that as an indicator to query for the existing row. This is perhaps a misuse of an exception, because this scenario is expected to occasionally happen, and is not really exceptional.

The problem with the “read-write” approach is that I’m not sure there’s a good way to have process 2 “see” the row that process 1 added, until it finishes its transaction. So this might need to be a “read-write-read”, in which case I’m better off just starting with the INSERT like in the “write-read” approach.