Delete and Insert throws Duplicate entry for key PRIMARY

Question

I have a composite PK, where I need to update one of its PK value. But due to some internal issues, I can't fire update queries to a PK column.

So I'm firing DELETE and INSERT query.

This DELETE and INSERT is done inside a TRANSACTION (READ COMMITTED).

But sometimes, when two requests do update to the same row. On Duplicate entry for key PRIMARY errors are thrown. This occurs randomly, I tried a lot in reproducing this issue locally but I couldn't find the root cause.

Note: Due to some internal restrictions, I couldn't try the following things.

1. Update query to PK
2. Replace query
3. Insert on duplicate or Insert Ignore queries
4. Adding a running ID currently for this table will be a huge migration.

Kindly help me with this issue.

Update:

Sample Table Structure:

       Table: temp
Create Table: CREATE TABLE `temp` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`,`id2`,`id3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Sample Data:

+-----+-----+-----+-------+
| id1 | id2 | id3 | value |
+-----+-----+-----+-------+
|   1 | 100 | 111 |   123 |
|   2 | 200 | 222 |   456 |
+-----+-----+-----+-------+

Sample Queries:

Transaction T1 tries to update ID1=3 value where id2=100

Delete from temp where id2=100
Insert into temp values(3,100,111,123);

Transaction T2 tries to update ID1=3 value where id2=100

Delete from temp where id2=100
Insert into temp values(3,100,111,123);

number of deletion and insertion will always be the same

Answer 1

First of all, getting a duplicate key error (or for example deadlocks) once in a while is nothing fundamentally problematic. Database servers are inherently concurrent, and if you do not want to completely lock the database, it's expected that two processes may try to do something that conflicts each other (e.g. trying to insert the same row). It got caught, and your job is now to react to this properly.

For your situation, you should get your error when you do not have rows to delete (e.g. the id does not yet exists), which is especially likely if you use your logic of delete-insert generally even for completely new entries.

The read committed-isolation level does not keep locks for non-existing rows (or gap locks):

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

So without an existing row to delete, the following flow might happen occasionally for two transactions A and B that want to insert the same id:

A: delete id, no rows -> no lock
B: delete id, no rows -> no lock (and no wait)
A: insert id -> locks row(s)
B: insert id -> wait
A: commit
B: lock released -> insert id -> duplicate key error

Your details may vary, you may use some orm that hides those details, or you may e.g. have an additional check to see if that id already exists (but then you should include this check/select with for update inside your transaction to make this check consistent), and then use a different procedure that just inserts - but in that case you will just run into a similar problem: you will just get the duplicate key error in that other procedure (where two "simple" inserts conflict each other).

Ultimately, you will just have to handle the case that two sessions try do the same thing at the same time. As you excluded all solutions that handle this situation inside the database (e.g. replace or on duplicate key update), you will have to catch and handle it manually in your application (e.g. rollback and repeat the transaction).

You could test this by the way by changing your isolation level to repeatable read (but since this could be a significant change, you should only do this in a development environment). This should give you a deadlock instead of a duplicate key error for this situation. While this doesn't exactly solve your problem, it could convince you that this actually is the problem.

Answer 2

@vinieth

As id1, id2 and id3 are the primary keys, there will be no chance to duplicate the value. right?

If your concern is just to update the id1 then I would suggest you use UPDATE statement, instead of DELETE and INSERT statement. Because by doing delete and insert you are consuming the resource and it is a bad practice too.

You can simply write the statement as

UPDATE `temp`
SET
   id1=3
WHERE
   id2=100;

Instead of

Delete from temp where id2=100;
Insert into temp values(3,100,111,123);

You can see the difference between both statements.

Also If I talk about the error you are facing is just normal when it comes to bulk operation with DB.

DB engines are more intelligent. When you are fire both statements at the same time. DB will prepare the list of the statement before the execution. So, at that time of preparing the statement, It stays inside the Stack memory. And after the preparation of the query, they calculate the execution time and order them into the priority Just to make the execution faster. In that case, Your insert operation got the first priority. So, DB will try to execute the insert operation and found the value and throws a "Duplicate entry for PRIMARY KEY" error.

Solution: If you don't want to use UPDATE statement instead of DELETE and INSERT. Please remove the primary key from id1 and check the result. But I still recommend you go with UPDATE statement.

EDIT:

There is also an another scenario that is responsible for this error. When you trying to fire both queries at the same time. They both get to run in parallel. And as DELETE and INSERT both run at the same time, INSERT operation found the value which is yet to delete. And engine returns this error.