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.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!