| 1 | package store |
| 2 | |
| 3 | import ( |
| 4 | "strings" |
| 5 | |
| 6 | "gorm.io/gorm" |
| 7 | |
| 8 | "amadan.net/oficina/sheets/internal/sheets/calc" |
| 9 | ) |
| 10 | |
| 11 | // markDirtyBatchSize bounds how many (row_id, col_id) pairs go into one |
| 12 | // `UPDATE cells SET input_version = ? WHERE tab_id = ? AND (row_id, |
| 13 | // col_id) IN (...)` statement inside markDirty. |
| 14 | // |
| 15 | // Before this, markDirty ran one UPDATE per touched cell — under h.mu, |
| 16 | // the same mutex Value and every other Set take — and touched is bounded |
| 17 | // by MaxCellsPerTab (200,000), not calc.MaxDirtyPerPass (5,000; that cap |
| 18 | // only bounds the RECOMPUTE below it, see setBlank's own comment). One |
| 19 | // statement per cell was measured at ~103ms for 5,001 cells, which |
| 20 | // extrapolates to multiple seconds of exclusive-lock hold at the real |
| 21 | // 200,000-cell ceiling — see batching-report.md. |
| 22 | // |
| 23 | // 1,000 pairs costs 1,000*2 = 2,000 bound parameters plus the 2 fixed |
| 24 | // ones (v, tab_id): comfortably under SQLite's default |
| 25 | // SQLITE_MAX_VARIABLE_NUMBER (32,766 on the bundled modernc.org/sqlite |
| 26 | // build), while cutting a 200,000-cell blank's marking from 200,000 |
| 27 | // statement executions to 200. |
| 28 | const markDirtyBatchSize = 1000 |
| 29 | |
| 30 | // markDirty bumps every cell in touched's own InputVersion to v, in |
| 31 | // batches of at most markDirtyBatchSize, leaving CacheVersion untouched — |
| 32 | // the same "input advances without yet touching cache" shape setContent |
| 33 | // uses on the one cell it directly edits, applied here to every cell |
| 34 | // whose precedent is about to disappear rather than to the cell whose |
| 35 | // content actually changed. |
| 36 | // |
| 37 | // Must run inside the SAME transaction as setBlank's row delete — that |
| 38 | // atomicity is what makes an interrupted blank's dependents recoverable |
| 39 | // at all (see setBlank's doc comment and |
| 40 | // TestBlankCrashLeavesDependentsRecoverable). Batching the statements |
| 41 | // does not change that: every batch still executes against tx, the |
| 42 | // caller's transaction, never one of its own. |
| 43 | func (h *Handle) markDirty(tx *gorm.DB, touched map[calc.CellKey]struct{}, v uint64) error { |
| 44 | ids := make([]cellIDs, 0, len(touched)) |
| 45 | for key := range touched { |
| 46 | cid, ok := h.keyToIDs[key] |
| 47 | if !ok { |
| 48 | continue // defensive: every touched key was registered via SetFormula |
| 49 | } |
| 50 | ids = append(ids, cid) |
| 51 | } |
| 52 | for start := 0; start < len(ids); start += markDirtyBatchSize { |
| 53 | end := start + markDirtyBatchSize |
| 54 | if end > len(ids) { |
| 55 | end = len(ids) |
| 56 | } |
| 57 | if err := markDirtyBatch(tx, h.tabID, ids[start:end], v); err != nil { |
| 58 | return err |
| 59 | } |
| 60 | } |
| 61 | return nil |
| 62 | } |
| 63 | |
| 64 | // markDirtyBatch runs the single batched UPDATE for one chunk of ids. |
| 65 | // Raw SQL rather than GORM's query builder because GORM has no row-value |
| 66 | // `IN ((?,?),(?,?),...)` construct — the same reason |
| 67 | // handle_test.go's seedOversizedTab already reaches for tx.Exec with |
| 68 | // hand-built placeholders. |
| 69 | func markDirtyBatch(tx *gorm.DB, tabID uint64, batch []cellIDs, v uint64) error { |
| 70 | var sql strings.Builder |
| 71 | sql.WriteString("UPDATE cells SET input_version = ? WHERE tab_id = ? AND (row_id, col_id) IN (") |
| 72 | args := make([]any, 0, 2+2*len(batch)) |
| 73 | args = append(args, v, tabID) |
| 74 | for i, cid := range batch { |
| 75 | if i > 0 { |
| 76 | sql.WriteByte(',') |
| 77 | } |
| 78 | sql.WriteString("(?,?)") |
| 79 | args = append(args, cid.RowID, cid.ColID) |
| 80 | } |
| 81 | sql.WriteByte(')') |
| 82 | return tx.Exec(sql.String(), args...).Error |
| 83 | } |
| 84 | |
| 85 | // openRecoveryBatchSize bounds how many stale origins' recomputation |
| 86 | // results are combined into one commit during Open's crash-recovery loop |
| 87 | // (recoverStale, called from handle_open.go). |
| 88 | // |
| 89 | // Graph.Recalc is pure in-memory work — cheap no matter how many origins |
| 90 | // run before a commit — so the only cost this batches is COMMITS: each |
| 91 | // is a gorm.DB.Transaction, and on this project's WAL-mode SQLite each |
| 92 | // commit costs at least one fsync. Recovering 5,001 independently-stale |
| 93 | // cells one transaction per cell measured ~25s, almost entirely that |
| 94 | // fsync cost, extrapolating to tens of minutes at the 200,000-cell |
| 95 | // MaxCellsPerTab ceiling — see batching-report.md. Grouping origins into |
| 96 | // batches of openRecoveryBatchSize turns len(stale) commits into roughly |
| 97 | // len(stale)/openRecoveryBatchSize. |
| 98 | // |
| 99 | // 500 is not tied to a SQLite limit the way markDirtyBatchSize is — it |
| 100 | // keeps one transaction's worth of UPDATEs (up to openRecoveryBatchSize |
| 101 | // origins, each able to contribute up to calc.MaxDirtyPerPass cells via |
| 102 | // persistTouched) from growing unboundedly large for a single commit, |
| 103 | // while still cutting commit count by roughly 500x at MaxCellsPerTab |
| 104 | // scale. |
| 105 | const openRecoveryBatchSize = 500 |
| 106 | |
| 107 | // recoverStale runs Open's crash-recovery pass over every stale origin, |
| 108 | // persisting in batches of at most openRecoveryBatchSize origins per |
| 109 | // transaction instead of committing once per origin. |
| 110 | // |
| 111 | // Recomputation (recalcTouched, handle_set.go) is in-memory only and |
| 112 | // happens for every origin in a batch before any of that batch is |
| 113 | // persisted, so a crash mid-batch simply leaves the not-yet-persisted |
| 114 | // origins in this batch — and every batch after it — exactly as stale as |
| 115 | // they were before this call, for the next Open to retry. That is the |
| 116 | // same recoverable state a crash landing between two origins already |
| 117 | // left under the old one-transaction-per-origin code; batching changes |
| 118 | // how many origins one commit covers, not what an interrupted commit |
| 119 | // leaves behind. |
| 120 | // |
| 121 | // Each origin still goes through recalcTouched's own |
| 122 | // calc.MaxDirtyPerPass gate individually — an origin whose own |
| 123 | // TransitiveDependents exceeds the cap is skipped exactly as |
| 124 | // the ordinary Set path has always skipped it, left stale for a future Open |
| 125 | // (or an ordinary Set that touches it directly) to retry. Batching only |
| 126 | // changes when a successfully-recomputed origin's result is committed, |
| 127 | // never whether it is recomputed at all. |
| 128 | // |
| 129 | // Each flush's persistTouched call also writes one recalc marker |
| 130 | // (events.go) for the whole batch — up to openRecoveryBatchSize origins |
| 131 | // — rather than one marker per origin the way setContent's single-origin |
| 132 | // setContent does. See persistTouched's doc comment for why that |
| 133 | // granularity change is honest rather than misleading: its triggerID is |
| 134 | // unconditionally 0 on every call site, so a batch's marker was never |
| 135 | // claiming to name the one origin that caused it. |
| 136 | func (h *Handle) recoverStale(stale []calc.CellKey, v uint64) error { |
| 137 | combined := make(map[calc.CellKey]struct{}) |
| 138 | for i, origin := range stale { |
| 139 | if touched, ok := h.recalcTouched(origin, true); ok { |
| 140 | for k := range touched { |
| 141 | combined[k] = struct{}{} |
| 142 | } |
| 143 | } |
| 144 | last := i == len(stale)-1 |
| 145 | if len(combined) == 0 { |
| 146 | continue |
| 147 | } |
| 148 | if (i+1)%openRecoveryBatchSize == 0 || last { |
| 149 | if err := h.persistTouched(combined, v); err != nil { |
| 150 | return err |
| 151 | } |
| 152 | combined = make(map[calc.CellKey]struct{}) |
| 153 | } |
| 154 | } |
| 155 | return nil |
| 156 | } |
| 157 | |
amadan