Коротко и по делу — возможные причины, план исправления/оптимизации и набор тестов.
1) Возможные причины потери строк в order_items
- Отсутствие атомарности: вставка/удаление order_items и создание orders не в одной транзакции → частичные записи либо потеря при retry/rollback.
- Гонки при параллельных обновлениях одного заказа: паттерн DELETE+INSERT или полная перезапись набора позиций без блокировки — «last writer wins», строки теряются.
- Неправильная обработка конфликтов/UPSERT: `INSERT ... ON CONFLICT DO NOTHING` или некорректный `UPDATE` может пропустить/перезаписать строки.
- Уровень изоляции и сериализация: при уровне ниже serializable возможны lost update / write skew; при serializable — откаты (serialization failure) если приложение не ретраится.
- Дедлоки/rollback со стороны БД: транзакция откатывается, приложение не делает retry → пропуск позиций.
- Отсутствие или неправильные внешние ключи/ограничений: нет гарантии целостности и нет сигналов об аномалиях; возможны случайные удаления.
- Приложенная дедупликация/логика на уровне приложения: агрессивное объединение запросов/очистка «дубликатов» может некорректно удалить нужные строки при параллельной работе.
- Репликация/lag/чтение с реплики: приложение читает старое состояние и решает обновить набор позиций, перезаписывая актуальные данные.
- Распределённая архитектура/шардинг без транзакций по шардам: часть вставок проходит на одном шарде, часть — на другом, потеря согласованности.
2) План исправления (пошагово)
- Сначала — мониторинг и репликация инцидента:
- Включить детализированный лог изменения order_items (triggers / audit table / change stream) чтобы фиксировать кто/когда/какая транзакция изменила строки.
- Запустить скрипт целостности: найти orders без позиций и неожиданные удаления.
- Обеспечить транзакционную атомарность:
- Оборачивать создание order + все order_items в одну транзакцию.
- Если бизнес-логика асинхронна — использовать надежную очередь (job + retry) и idempotent операции.
- Защитить от параллельных модификаций:
- Либо pessimistic locking: `SELECT ... FOR UPDATE` на строке orders перед изменением items, либо
- optimistic locking: версия (`version`/`updated_at`) + проверка/ретрай при конфликте.
- Избегать схемы DELETE+INSERT без блокировки; предпочтительнее upsert-ы.
- Обрабатывать ошибки БД корректно:
- Ретрай при deadlock/serialization failure (SQLSTATE $40001$ / deadlock код) с экспоненциальным backoff, лимит ретраев — например до $3$ попыток.
- Добавить ограничения и валидации в схеме:
- FK: `ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT;`
- FK для product: `... FOREIGN KEY (product_id) REFERENCES products(id);`
- Первичный ключ / уникальность: `PRIMARY KEY (order_id, product_id)` (если одна запись на пару), или `id` PK + unique(order_id, product_id).
- NOT NULL на quantity и CHECK `quantity > 0`.
- Триггер/constraint на суммарную валидацию, если нужно.
- Улучшить логику вставки:
- Использовать `INSERT ... ON CONFLICT (...) DO UPDATE` для корректной агрегации количества, вместо удаления и повторной вставки.
- Оптимизация масштабируемости:
- Индексы: индекс по `order_id` в order_items; если часто фильтруют по product_id — добавить его.
- Батчинг: вставлять позиции пакетно в одной транзакции (например batch size $1000$ или меньше).
- Шардирование: шардинг по order_id вместе с order_items (co-locate) чтобы транзакции оставались локальными; либо partitioning по дате/диапазону для очень больших таблиц.
- Горизонтальная масштабируемость: CQRS — запись в очередь, запись в основную БД, чтение из проекций.
- Контроль изменений и миграции:
- Перед наложением FK/PK — проверить и исправить неконсистентные данные; использовать `VALIDATE CONSTRAINT` (Postgres) или phased migration.
- Наблюдаемость:
- Метрики тревог: рост «orders без items», частота ретраев транзакций, rate ошибок write conflicts.
- Аудитные логи с transaction_id, user_id, stack trace.
3) Конкретные SQL-примеры (рекомендации)
- Composite PK + FK:
- ALTER TABLE order_items ADD CONSTRAINT order_items_pk PRIMARY KEY (order_id, product_id);
- ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT;
- Upsert примитив:
- INSERT INTO order_items(order_id, product_id, quantity) VALUES(...) ON CONFLICT (order_id, product_id) DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;
4) Тесты для валидации (что и как проверять)
- Конкурентные тесты (интеграционные):
- Запустить N параллельных воркеров (например $50$ — варьировать) которые одновременно добавляют/обновляют позиции одного и того же заказа; проверять итоговую совокупность позиций и суммарные количества.
- Тесты на корректность retry:
- Имитировать rollback/deadlock и убедиться, что операция ретраится и не теряет строки; проверить максимум ретраев $3$.
- Нагрузочные тесты (stress):
- Симуляция пиков: создать поток заказов с большой частотой (например $1000$ заказов/сек в тестовой среде) и проверить долю аномалий близка к $0$.
- Fault injection:
- Прерывание сервиса во время транзакции; проверка, что нет частично применённых наборов (atomicity).
- Репликация/lag тест:
- Чтение с реплики после записи на мастер; тесты на read‑your‑writes при использовании реплик.
- Schema migration tests:
- Перед наложением FK/PK — тесты, которые находят заведомо неправильные записи; затем проверить миграцию без потери данных.
- End-to-end проверки:
- Автоматический reconcile job: периодическая сверка orders ↔ order_items (например считать, у скольких заказов количество позиций $=$ $0$ и корректность сумм).
- Мониторинг метрик:
- Убедиться, что метрики retry rate, conflict rate, orders-without-items уменьшаются после фиксов.
5) Короткие рекомендации по приоритетам (первые шаги)
- Немедленно включить аудит изменений и запустить скрипт целостности.
- Обернуть операции создания заказа + items в транзакцию (если ещё не).
- Добавить FK и composite PK (после очистки данных).
- Реализовать корректный retry на стороне приложения и заменить DELETE+INSERT на upsert/locking.
- Нагрузочное тестирование и мониторинг перед выкатом в прод.
Если нужно — могу прислать шаблоны SQL-скриптов для анализа консистентности, пример кода ретрая, и шаблоны нагрузочных тестов.