Rev 36362 – Make fofo_order creation idempotent on (fofo_id, invoice_number) Fixes insert-intention ...

amit — Wed, 22 Apr 2026 19:49:38 +0530

amit – 2 file(s) modified
Make fofo_order creation idempotent on (fofo_id, invoice_number)

Fixes insert-intention gap-lock deadlocks on idx_invoice_number observed in
SHOW ENGINE INNODB STATUS at 2026-04-22 13:01:54 — two concurrent requests
inserting the same invoice_number for the same customer (client retry /
upstream webhook retry pattern).

- OrderServiceImpl.createAndGetFofoOrder: select-then-insert idempotency.
Fast path returns existing row if already created; slow-race path catches
DataIntegrityViolationException and re-selects the winner's row.
- add_uk_fofo_order_fofo_invoice.sql: adds UNIQUE KEY (fofo_id, invoice_number)
so the loser of a concurrent-insert race gets a clean DIVE instead of a
silent duplicate row (pre-check showed zero existing dup groups, safe).

findExistingFofoOrder wraps the repo call to swallow the repo's declared
ProfitMandiBusinessException — the impl actually returns null on not-found
(constructs an exception but never throws), so the swallow matches reality.

~ /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/order/OrderServiceImpl.java
+ /trunk/profitmandi-dao/src/main/resources/sql/add_uk_fofo_order_fofo_invoice.sql

SmartDukaan – /trunk/profitmandi-dao/src/main/resources/sql/add_uk_fofo_order_fofo_invoice.sql

Rev 36362 – Make fofo_order creation idempotent on (fofo_id, invoice_number) Fixes insert-intention ...