Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The basic steps are (pseudocode)

function doStuff(int someID){
    found = `SELECT someIDFROM targettable` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

The problem is ofcourse that if process1 reads uniqueID, figures it should insert the value, and process2 does the same before the write is finished, I get 2 lines in my targettable. This is possible (someID is not unique for that table), but in this case unwanted because it was done on wrong assumptions because of the concurrency.

Now my assumption/feeling is that a transaction alone is not enough for this. The second process will have consistent reads I think, but that does not help prevent the second INSERT, does it? so this will not do (I think, please advice me on this. This could be where I'm going wrong). (I'm quite sure at the moment this is not enough to ensure correct behaviour, because of testing)

function doStuff(int someID){
    'START TRANSACTION HERE'
    found = `SELECT someIDFROM targettable where someid = someID ` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
    COMMIT TRANSACTION
} 

From what I read about InnoDB locking reads it could be that I should use those, but I'm not sure as I have never worked with them. It could also be the transaction is enough, and I'm just begin foolish.

My current idea is to use SELECT someID FROM targettable FOR UPDATE, which I figure will give me a lock. But I'm not sure what lock, since it depends on isolation level. This would look like so:

function doStuff(int someID){
    found = `SELECT someIDFROM targettable where someid = someID FOR UPDATE` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

I'm not sure how to cope with the value I get from another table, but that might be another thing.

Summarizing: Can I get away with just using a transaction, do I need to use a locking-read (if so, how?) or is there another option I missed?

Update: If I do something like

START TRANSACTION;
SELECT * FROM targettable WHERE someid = 3 FOR UPDATE

And query that select in another thread, I get no result (locked?) until this transaction is finished. But if I put an INDEX on that someid, I DO! I'm probably missing something, but I wasn't expecting that.
How can I be sure I don't insert this row twice?

share|improve this question
    
What I basically want is "other threads shouldnt insert this row until I'm done with my work". Should I lock the complete table? I only want to lock inserting that one row... –  Nanne Feb 15 '13 at 14:58

2 Answers 2

I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT. That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

  • In the best case, you run 10000 SELECTs are find that someID exists
  • In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}
share|improve this answer
    
The problem is, I'm not doing this a lot of times, but it is being done concurrently. So maybe this function will run a couple of hundred times per day, but possibly 2 times per someID. I don't have a prefab list, as the requests happen seperatly. And that's where the problem is. someID is not unique, so the ingore doesn't help. The roundtrips are not a problem by the way, it has virtually no impact on the system. I think I might get away with trying to do it all in one query, but this will get tricky because of the non-uniqueness. I did find something I think, but need to check it first. –  Nanne Feb 16 '13 at 9:14

I think this link might do what you're trying to accomplish. Basically it's insert where not exists statement: http://stackoverflow.com/a/3025332/16501

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.