Ensuring uniquness of a MySQL key

Question

Hi,

I have a MySQL DB where a key (a string) is read from a third pary source. However, these are not always guaranteed unique (they're movie titles). So I need to check for key uniqueness, and if not unique, amend it to make it unique - by adding an incremental count at the end of the key for example.

What's the best pattern to do this? I currently have a table which stores every key received from 3rd party and stores a count, so I do:

INSERT INTO MYTABLE(KEY) VALUES(KEY_VAlUE) ON DUPLICATE KEY UPDATE KEY_COUNT = KEY_COUNT + 1

I am using jdbcTemplate and a keyHolder to retrieve the ID of the new (or updated) row. The problem is I also need to get the count back. I can of course now do a second query (SELECT) to fetch the record with that ID. However the issue here is with concurrency. It is possible (although unlikely), another INSERT with the same third-party key occurs just before I do my SELECT. In this case the count would be incremented a second time and I'll get the wrong count back.

Any ideas?

Thanks Richard.

Answer 1

If you are allowing the system to create arbitrary unique key values for ANY rows (the ones that are not submitted as unique to start with), then, instead of modifying it to be unique, why not create your own internal surrogate key, using auto-incrementing feature (MySQL has that right?), and store the submitted key as it was submitted in a non-unique column attribute.

use the internal surrogate key for all relational integrity constraints, and only use the submitted key for other non-unique requirements...

Answer 2

The best pattern to do this is not to. Keying your table by a string like this, especially where you know ahead of time that there will be collisions, will cause you nothing but grief in the long run. Just use an otherwise-meaningless autoincrement key. You can still index by title if you want, and you don't have to munge your data.

Incidentally, I think that when it comes to movie title collisions, the convention is to append the year of the movie in parentheses, e.g. "The Producers (1968)" versus "The Producers (2005)"

Answer 3

Transactions. You are using InnoDB, right? You should be.

However, a movie title sounds like a poor primary key to me. Have you considered adding a synthetic primary key, such as an autoincrement integer?

Answer 4

I don't know if there's a "pattern" to do this, but why not simply use a transaction?

START TRANSACTION;

INSERT INTO MYTABLE(KEY) VALUES(KEY_VAlUE) ON DUPLICATE KEY UPDATE KEY_COUNT = KEY_COUNT + 1;
SELECT KEY_COUNT FROM MYTABLE WHERE KEY=KEY_VALUE;

COMMIT;