MySQL atomic SELECT+INSERT -- transactions, locks or what?
Posted: Tue Mar 10, 2009 9:31 am
I have just hit the limits of my MySQL knowledge with this baby:
1. SELECT id WHERE something
2. if no such id, INSERT something and use the newly inserted id
3. else, just use the selected id
4. do stuff with the id
It is basically a search-by-name-and-add-if-not-existent scenario.
The problem is that if two clients (A and B) do this in the same time for the same name they may do it like this (numbers are the steps described above):
A1 - finds nothing named "jane"
B1 - finds nothing named "jane"
A2 - insert "jane"
B2 - insert "jane"
A4 and B4 will do stuff with different ids, there will be two "jane" records in the database.
Instead I want the SELECT and the next eventual INSERT to be atomic, so we would have:
<atom>
A1 - no "jane"
A2 - insert "jane"]]
</atom>
A4 - do stuff with the new "jane"
<atom>
B1 - find A's "jane"
</atom>
B4 - do stuff with the same "jane"
How can we solve this in an efficient manner?
Transactions and SELECT ... LOCK IN SHARE MODE and SELECT ... FOR UPDATE don't seem to be the thing as we don't have rows that can be locked.
Locking the entire table with LOCK TABLE WRITE looks possible, but will affect all other accesses to the table.
What am I missing, how should I tackle this?
1. SELECT id WHERE something
2. if no such id, INSERT something and use the newly inserted id
3. else, just use the selected id
4. do stuff with the id
It is basically a search-by-name-and-add-if-not-existent scenario.
The problem is that if two clients (A and B) do this in the same time for the same name they may do it like this (numbers are the steps described above):
A1 - finds nothing named "jane"
B1 - finds nothing named "jane"
A2 - insert "jane"
B2 - insert "jane"
A4 and B4 will do stuff with different ids, there will be two "jane" records in the database.
Instead I want the SELECT and the next eventual INSERT to be atomic, so we would have:
<atom>
A1 - no "jane"
A2 - insert "jane"]]
</atom>
A4 - do stuff with the new "jane"
<atom>
B1 - find A's "jane"
</atom>
B4 - do stuff with the same "jane"
How can we solve this in an efficient manner?
Transactions and SELECT ... LOCK IN SHARE MODE and SELECT ... FOR UPDATE don't seem to be the thing as we don't have rows that can be locked.
Locking the entire table with LOCK TABLE WRITE looks possible, but will affect all other accesses to the table.
What am I missing, how should I tackle this?