Duplicate key value violates unique constraint "spy_sales_order-order_reference"

KFrw

Hi All,

we moved the order prozessin to use the queue. But if for some reason we get a flood of messages we get this message:

Duplicate key value violates unique constraint "spy_sales_order-order_reference"

First we thought it might be sprykers sequence number module. But after looking at the code it is basically impossible because they load and lock the row with the lastest order key by propel and so there should be a transaction until the save() call. And there is a unique key restriction on the number so the code and table will never ever generate the same insert.

But what could load the spy_sales_order and try to insert it again?

Our own implementation passes the quote in the end into sprykers SalesOrderSaver and I can't see any path that would load an existing sales order.

Any idea or suggestion?

Cheers Karsten

fsmeier

Heyhey @KFrw ,

we moved the order prozessin to use the queue

How does this look like?

You have the quote-transfer and when someone submits the cart (checks out) instead of already running the CheckoutWorkflow you put the quotetransfer directly to a queue or do you persist and lock it somehow first? In which format?

Then, when you read the quotetransfer from the queue, you use the checkoutworkflow class and create the actual sales-order entity?

All the best,

Florian

KFrw

Propel\Runtime\Exception\PropelException -

Unable to execute INSERT statement

[INSERT INTO spy_sales_order

(id_sales_order, fk_locale, fk_sales_order_address_billing, currency_iso_code, customer_number, customer_reference, email, first_name, ips_quote_uuid, is_test, last_name, oms_processor_identifier, order_channel, order_reference, price_mode, salutation, store, created_at, updated_at) VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18)] in "/data/src/Orm/Zed/Sales/Persistence/Base/SpySalesOrder.php::2591"

This happened again yesterday. I tried to hammer the spryker sequence script and it just didn't show any problems. The PgSql runs SELECT  FROM spy_sequence_number FOR UPDATE which should be a read and write lock.

But we can confirm that it is not a late read of the same spy_sales_order and then trying to INSERT it again into the database but there where two orders with the same refrence number which should be impossible.

Our hoster tells us he sees the same error in other projects. Currently I am clueless how this could happen. There is no SQL replication or anything running that could cause a split brain situation.

I am currently out of ideas here.

KFrw

https://docs.spryker.com/docs/pbc/all/order-management-system/latest/base-shop/unique-random-order-reference-generator#entropy-guidelines We are thinking about moving away from DB number generation because we don't care about a real sequence. But even if this would fix our problem I would feel a lot better if i knew how this could happen.