0

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.

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 my_column -> id value. Whenever a new entry 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.


Some more constraints:

  1. I need to get the resulting PK ID, regardless of whether the row was inserted or already exists. However I don't want to burn through auto-increment values, so I'm not going to use the UPDATE id = LAST_INSERT_ID(id) kludge. I'd rather just execute a second SQL statement to SELECT the id value, because I'm only inserting a row every ~5 minutes into a table with ~100k rows, so query performance is not a major concern.

  2. The reason why I'm not using the VARCHAR my_column column as the PRIMARY KEY column is that this lookup table has a FK reference from another much bigger (500M rows, 20 cols) table. I'm using this lookup table pattern to store reference data - e.g. failure codes ("eligibility.failure.not_enough_foobar_data") or time zones ("America/Los_Angeles") - so that the much larger main table can use a short FK id to reference that value (e.g. failure_code_id or time_zone_id), instead of repeating the long string.

1

Here's the best solution I've been able to come up with, using a write-read approach:

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

This has the following advantages:

  1. it doesn't require any transactionality or a different isolation level, because the insert-if-not-exists statement is already an atomic operation;
  2. it executes at most 2 SQL statements (insert-select) instead of 3 (select-insert-select), making the calling code simpler;
  3. 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.
1

If both threads will insert identical values, use INSERT IGNORE. If you need to get the resulting id, use the kludge of IODKU with ... UPDATE id = LAST_INSERT_ID(id). (It's in the manual.)

Keep in mind that both IODKU and INSERT IGNORE will 'burn' ids. See this for a way to avoid such.

Since you have a UNIQUE key, why not use it as the PRIMARY KEY? Provide your argument for having both so I can argue against both (or agree that both makes sense).

Addressing constraints

  1. In http://mysql.rjweb.org/doc.php/staging_table#normalization , I discuss how to mass insert new rows without buring ids, then get the ids. Yes, it takes 2 statements, but only 2 for any number of rows conditionally inserted.

The first statement is INSERT ... SELECT ... where the SELECT returns only the number of rows needed -- 0 or more (up to 1 in your case). That keeps theINSERTfrom wasting any ids. TheSELECTgets theids` for all rows (1 in your case). Yes, it assumes the desired data has already been put into a "staging" table, so that makes it possibly too messy for your case.

  1. If you are concerned about space, and my_column is something with a finite list of possibilities (eg, timezones), note that INT is 4 bytes. Consider TINYINT UNSIGNED (1 byte, values 0..255) or SMALLINT UNSIGNED (2 bytes, values 0..64K).
  • Good follow-up questions, I will update my question to answer them! – Steve K Feb 12 '18 at 16:17
  • I responded..... – Rick James Feb 12 '18 at 22:04
  • Regarding point #1, can you compare the solution you've suggested at that URL to the IODKU approach that I provided in my answer below? I don't have a staging table, so I'm having a hard time contrasting your suggested approach to my IODKU + SELECT answer. – Steve K Feb 13 '18 at 0:16
  • Regarding point #2, I completely agree. In my database, I actually have multiple lookup tables, all of which are designed and inserted in the same fashion as I described in this question. The time_zone table does in fact use a TINYINT UNSIGNED PK, whereas the failure_reason table uses a SMALLINT UNSIGNED, and even larger lookup tables use a MEDIUMINT UNSIGNED PK. – Steve K Feb 13 '18 at 0:21
  • OK, you are right about #1. – Rick James Feb 13 '18 at 1:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.