kobotoolbox / kpi / 37745681293

83%

master: 76%

push

github

fix(openRosa): lock catch-all counters in date order DEV-2513 (#7674)

### 📣 Summary

Permanently deleting several projects of the same account at the same
time no longer fails with a database deadlock.

### 📖 Description

When several projects of the same account were permanently deleted at
the same time, for example after emptying the trash, some of those
deletions could fail and had to be retried later. They now go through
without getting in each other's way.

### 💭 Notes

- Cause: deleting an `XForm` folds its monthly and daily counters into
the owner's catch-all rows (`xform = NULL`) in
`update_catch_all_counter_on_delete`. The loops had no `ORDER BY`, so
two deletions of the same owner could lock the same months in opposite
orders. Production logs show the deadlocks in exactly that function, on
`logger_monthlyxformsubmissioncounter`. Nothing else writes catch-all
rows, so the other side is always another deletion of the same owner.
- Fix: `order_by('year', 'month')` and `order_by('date')`. Every
deletion now takes these rows in the same order, so the later one waits
instead of deadlocking. The daily loop has the same shape and runs right
after in the same transaction, so it gets the same fix.
- Tests: `TestCatchAllCounterLockOrder` races two real transactions
(`TransactionTestCase`, two threads, a barrier) and forces a sequential
scan, so the unordered query returns rows in insertion order. Without
the fix it fails with the same `deadlock detected ...
logger_monthlyxformsubmissioncounter` as production.
- Second attempt at DEV-2513, after #7553 (suspending the owner's
submissions) was reverted in #7666.
- Out of scope, seen once each in the logs: a deadlock on the daily
counter during `/submission`, and one on `kpi_asset` when two bulk "move
to trash" requests overlap. Also left alone: `update_storage_counters`
locks `UserProfile` before `XForm`, the opposite of the other counter
helpers (no deadlock observed).

### 👀 Preview steps

1. ℹ️ backen... (continued)

10303 of 13642 branches covered (75.52%)

32958 of 39718 relevant lines covered (82.98%)

5.5 hits per line