Magento – Integrity constraint violation: 1062 Duplicate entry for key ‘UNQ_SALES_FLAT_INVOICE_INCREMENT_ID’

invoicemagento-1.9MySQLorders

I'm helping a merchant track down the root cause for some failed payment transactions (during a heavy order day), that failed with the following error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '51986' for key 'UNQ_SALES_FLAT_INVOICE_INCREMENT_ID'

The UNQ_SALES_FLAT_INVOICE_INCREMENT_ID index is a unique key on the increment_id column in the sales_flat_invoice table. When I look in this table for the increment_id mentioned in the error (51986), I find there's already a invoice with this increment_id in there, and it's for an order placed by a different customer.

My 2 questions related to this

  • Where in Magento CE 1.9.0.1 is an invoice ID normally created?

  • Are there known issues in a stock Magento CE 1.9.0.1 with colliding invoice IDs for near simultaneous orders?

I realize the increment ID of 51986 means the store has some sort of extension for changing the increment IDs installed, but I want to make sure there isn't known science w/r/t to this before going too far down that path.

Best Answer

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.

Related Topic