[ACCEPTED]-mysql insert race condition-race-condition

what you want is LOCK TABLES

or if that seems excessive 4 how about INSERT IGNORE with a check that the row was 3 actually inserted.

If you use the IGNORE 2 keyword, errors that occur while executing 1 the INSERT statement are treated as warnings instead.

It seems to me you should have a unique 21 index on your id column, so a repeated insert 20 would trigger an error instead of being 19 blindingly accepted again.

That can be done 18 by defining the id as a primary key or using 17 a unique index by itself.

I think the first 16 question you need to ask is why do you have 15 many threads doing the exact SAME work? Why 14 would they have to insert the exact same 13 row?

After that being answered, I think 12 that just ignoring the errors will be the 11 most performant solution, but measure both 10 approaches (GET_LOCK v/s ignore errors) and 9 see for yourself.

There is no other way 8 that I know of. Why do you want to avoid 7 errors? You still have to code for the case 6 when another type of error occurs.

As staticsan 5 says transactions do help but, as they usually 4 are implied, if two inserts are ran by different 3 threads, they will both be inside an implied 2 transactions and see consistent views of 1 the database.

Locking the entire table is indeed overkill. To 20 get the effect that you want, you need something 19 that the litterature calls "predicate locks". No 18 one has ever seen those except printed on 17 the paper that academic studies are published 16 on. The next best thing are locks on the 15 "access paths" to the data (in some DBMS's 14 : "page locks").

Some non-SQL systems allow 13 you to do both (1) and (2) in one single 12 statement, more or less meaning the potential 11 race conditions arising from your OS suspending 10 your execution thread right between (1) and 9 (2), are entirely eliminated.

Nevertheless, in 8 the absence of predicate locks such systems 7 will still need to resort to some kind of 6 locking scheme, and the finer the "granularity" (/"scope") of 5 the locks it takes, the better for concurrency.

(And 4 to conclude : some DBMS's - especially the 3 ones you don't have to pay for - do indeed 2 offer no finer lock granularity than "the 1 entire table".)

On a technical level, a transaction will 10 help here because other threads won't see 9 the new row until you commit the transaction.

But 8 in practice that doesn't solve the problem - it 7 only moves it. Your application now needs to 6 check whether the commit fails and decide 5 what to do. I would normally have it rollback 4 what you did, and restart the transaction 3 because now the row will be visible. This 2 is how transaction-based programmer is supposed 1 to work.

I ran into the same problem and searched 4 the Net for a moment :)

Finally I came up 3 with solution similar to method to creating filesystem objects in shared (temporary) directories to securely open temporary files:

$exists = $success = false;
do{
 $exists = check();// select a row in the table 
 if (!$exists)
  $success = create_record();
  if ($success){
   $exists = true;
  }else if ($success != ERROR_DUP_ROW){
    log_error("failed to create row not 'coz DUP_ROW!");
    break;
  }else{
    //probably other process has already created the record,
    //so try check again if exists
  }
}while(!$exists)

Don't 2 be afraid of busy-loop - normally it will execute 1 once or twice.

You prevent duplicate rows very simply by 29 putting unique indexes on your tables. That 28 has nothing to do with LOCKS or TRANSACTIONS.

Do 27 you care if an insert fails because it's 26 a duplicate? Do you need to be notified 25 if it fails? Or is all that matters that 24 the row was inserted, and it doesn't matter 23 by whom or how many duplicates inserts failed?

If 22 you don't care, then all you need is INSERT IGNORE. There 21 is no need to think about transactions or 20 table locks at all.

InnoDB has row level 19 locking automatically, but that applies 18 only to updates and deletes. You are right 17 that it does not apply to inserts. You can't 16 lock what doesn't yet exist!

You can explicitly 15 LOCK the entire table. But if your purpose is 14 to prevent duplicates, then you are doing 13 it wrong. Again, use a unique index.

If there 12 is a set of changes to be made and you want 11 an all-or-nothing result (or even a set 10 of all-or-nothing results within a larger 9 all-or-nothing result), then use transactions 8 and savepoints. Then use ROLLBACK or ROLLBACK TO SAVEPOINT *savepoint_name* to undo changes, including 7 deletes, updates and inserts.

LOCK tables is not 6 a replacement for transactions, but it is 5 your only option with MyISAM tables, which 4 do not support transactions. You can also 3 use it with InnoDB tables if row-level level 2 locking isn't enough. See this page for more information 1 on using transactions with lock table statements.

I have a similar issue. I have a table that 9 under most circumstances should have a unique 8 ticket_id value, but there are some cases 7 where I will have duplicates; not the best 6 design, but it is what it is.

1. User A checks to see if the ticket is reserved, it isn't
2. User B checks to see if the ticket is reserved, it isn't
3. User B inserts a 'reserved' record into the table for that ticket
4. User A inserts a 'reserved' record into the table for that ticket
5. User B check for duplicate? Yes, is my record newer? Yes, leave it
6. User A check for duplicate? Yes, is my record newer? No, delete it

User B has 5 reserved the ticket, User A reports back 4 that the ticket has been taken by someone 3 else.

The key in my instance is that you 2 need a tie-breaker, in my case it's the 1 auto-increment id on the row.

In case insert ignore doesnt fit for you 6 as suggested in the accepted answer , so 5 according to the requirements in your question 4 :

1] select a row from table
2] if 3 it doesn't exist, insert it

Another possible 2 approach is to add a condition to the insert 1 sql statement, e.g :

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Reference: https://stackoverflow.com/a/3164741/179744