Magento – Fix SQL Error: Integrity Constraint Violation: 1062 Duplicate Entry

The error has been found on our import/update script (product->save() was called twice).

Important: no database "cleaning" was required.

I'm happy to have not choose to follow some advice on the web that propose to do that kind of operations (@fabian, you're golden rule was successfully followed).

Order, invoice, creditmemo, shipping was EAV until 1.6(?)

@Prateek invoice WAS an EAV model and the increment_id still is.

Increment_id creation and problem

Increment ID is created here

\Mage_Eav_Model_Entity_Attribute_Backend_Increment which calls
\Mage_Eav_Model_Entity_Abstract::setNewIncrementId which calls
\Mage_Eav_Model_Entity_Type::fetchNewIncrementId

I would assume because in the last method the transaction is started (and the table/row is not locked) a second order creation can pass by and take the same newly created increment_id.

Solution

I would assume, that if you lock the row/table before reading, you can avoid that any other process reads the table until you write a new increment_id. This might help: How do I lock a row after using load()?

But I fear, that locking the row make bad performance loss.