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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

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

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post 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();
 
ryanwithanr
Forum Newbie
Posts: 1
Joined: Thu Jun 18, 2009 6:43 am

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

Post 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>'; }
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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post 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
 
Post Reply