mysql insert after delete fails because of "duplicate entry"

I have a code with two mysql queries.

DELETE FROM my_table WHERE user_id=some_number

INSERT INTO my_table (user_id, ... ) VALUES(some_number, ...)

The field user_id is unique.

In rare cases the 开发者_C百科insert fails claiming a duplicate entry occurred. My first instinct leads me to to believe the DELETE didn't finish and now the insert is trying to insert and I'm getting a duplicate entry. Is this possible? How can I avoid this? Might there be a different explanation you can think of?

Update: The reason I'm deleting is because I want to delete all the data that I am not updating / inserting for the first time. Also, I think it is important to state that most of the data remains the same.

SET AUTOCOMMIT=0;    
START TRANSACTION;    
DELETE FROM my_table WHERE user_id=some_number;     
INSERT INTO my_table (user_id, ... ) VALUES(some_number, ...); 
commit;

Use an UPDATE statement instead:

UPDATE my_table
SET my_column = my_value
WHERE user_id = some_number

You could always try a COMMIT after the DELETE to make sure its completed.

Why do you DELETE and then INSERT the same user_id and not just UPDATE the row?

This happens because the query are treated as two single transaction, so the order of execution is not guaranteed. The effect you are describing is because the insert is processed before delete. You should change the query logic or perform both queries in one single transaction.