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?
MySQL atomic SELECT+INSERT -- transactions, locks or what?
Moderator: General Moderators
Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?
I believe this is about all you need.
Code: Select all
$failed = false;
$mysqli->autocommit(false);
$mysqli->query("SELECT id FROM table WHERE foo = 'bar'") ? NULL : $failed = true;
# if not exists
$mysqli->query("INSERT INTO table VALUES ('bar')") ? NULL : $failed = true;
$failed ? $mysqli->rollback() : $mysqli->commit();
-
ryanwithanr
- Forum Newbie
- Posts: 1
- Joined: Thu Jun 18, 2009 6:43 am
Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?
Would this work in a similar context
Code: Select all
$query = 'SELECT Venue FROM Venues WHERE VenueName ="' . $V . '"';
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
if (mysql_num_rows($result) == 0) { // Available.
// Add the Venue.
$query = "INSERT INTO venues (Userid, VenueName, Address)
VALUES (" . $who . ",'" . $clean['v1'] ."','" . $clean['a1'] . "' )";
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
if (mysql_affected_rows() == 1) { // If it ran OK.
// display new venue....
popup('New Venue Saved!!','ViewNewVenue.php?id=',$venueid);
} else { echo '<div id ="errortext">Your venue could not be added -- Database error!!</div>'; }
Last edited by Benjamin on Thu Jun 18, 2009 4:14 pm, edited 1 time in total.
Reason: Changed code type from text to php. Removed Spam in Signature.
Reason: Changed code type from text to php. Removed Spam in Signature.
Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?
Recently I had to do something similar. In my case I had to go with locks, but for your scenario I'd use unique key on something + insert ignore followed by select:Mordred wrote: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.
Code: Select all
-- assuming there's unique key on the something
-- ALTER TABLE someTable ADD UNIQUE KEY (something);
INSERT IGNORE INTO someTable SET something="blah"; -- it will silently fail if there's already "blah" in the database
SELECT id FROM someTable WHERE something="blah";
-- do stuff with id