Page 1 of 1

MySQL atomic SELECT+INSERT -- transactions, locks or what?

Posted: Tue Mar 10, 2009 9:31 am
by Mordred
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?

Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?

Posted: Fri Mar 13, 2009 9:20 pm
by Benjamin
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();
 

Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?

Posted: Thu Jun 18, 2009 6:44 am
by ryanwithanr
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>'; }

Re: MySQL atomic SELECT+INSERT -- transactions, locks or what?

Posted: Thu Jun 18, 2009 1:04 pm
by Weirdan
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.
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:

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