Subversion Repositories SmartDukaan

-- Migration: add unique constraint on fofo.fofo_order (fofo_id, invoice_number)

-- Paired with idempotent createAndGetFofoOrder() code: concurrent duplicate

-- inserts (client double-clicks, upstream retries) used to deadlock on the

-- idx_invoice_number gap lock — with the unique key, the loser gets a clean

-- DataIntegrityViolationException which the code catches and re-selects.

-- Pre-check on 2026-04-22: zero existing duplicate (fofo_id, invoice_number)

-- groups in the table, so the constraint adds cleanly.

-- Online DDL on InnoDB 5.7+; brief metadata lock only at commit.

ALTER TABLE fofo.fofo_order

  ADD UNIQUE KEY uk_fofo_order_fofo_invoice (fofo_id, invoice_number);

-- Rollback:

-- ALTER TABLE fofo.fofo_order DROP INDEX uk_fofo_order_fofo_invoice;