{"record":{"id":"f30ed5358edbbdc8","repo":"bytebase/bytebase","slug":"failed-to-commit-empty-sample-instance-cleanup","errorCode":null,"errorMessage":"failed to commit empty sample instance cleanup","messagePattern":"failed to commit empty sample instance cleanup","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"warning","filePath":"backend/store/sample_instance.go","lineNumber":283,"sourceCode":"\t\treturn nil, errors.Wrap(err, \"failed to begin sample instance cleanup\")\n\t}\n\tdefer tx.Rollback()\n\n\tvar workspace string\n\terr = tx.QueryRowContext(ctx, `\n\t\tSELECT workspace FROM sample_instance_setup\n\t\tWHERE deleted_at IS NULL AND (\n\t\t\t(activated_at IS NULL AND updated_at <= $2)\n\t\t\tOR (activated_at IS NOT NULL AND expires_at IS NOT NULL AND expires_at <= $1)\n\t\t)\n\t\t\tAND workspace > $3\n\t\tORDER BY workspace\n\t\tFOR UPDATE SKIP LOCKED\n\t\tLIMIT 1\n\t`, now, staleBefore, afterWorkspace).Scan(&workspace)\n\tif errors.Is(err, sql.ErrNoRows) {\n\t\tif err := tx.Commit(); err != nil {\n\t\t\treturn nil, errors.Wrap(err, \"failed to commit empty sample instance cleanup\")\n\t\t}\n\t\treturn &SampleInstanceCleanupResult{}, nil\n\t}\n\tif err != nil {\n\t\treturn nil, errors.Wrap(err, \"failed to lock sample instance setup for cleanup\")\n\t}\n\tsetup, err := getSampleInstanceSetup(ctx, tx, workspace)\n\tif err != nil {\n\t\treturn nil, err\n\t}\n\tresult := &SampleInstanceCleanupResult{WorkspaceID: workspace, Found: true}\n\tif err := callback(ctx, &SampleInstanceSetupTx{tx: tx, workspace: workspace, replica: setup.ReplicaID}, setup); err != nil {\n\t\tresult.CallbackErr = err\n\t}\n\tif err := tx.Commit(); err != nil {\n\t\treturn nil, errors.Wrap(err, \"failed to commit sample instance cleanup\")\n\t}\n\treturn result, nil","sourceCodeStart":265,"sourceCodeEnd":301,"githubUrl":"https://github.com/bytebase/bytebase/blob/1870550677fe08f0d2a78c07acd27541464eb945/backend/store/sample_instance.go#L265-L301","documentation":"When the cleanup row-select finds no eligible setup (sql.ErrNoRows), the function commits the (empty) transaction to release it and returns an empty SampleInstanceCleanupResult. This wrapper is returned when that no-op commit fails — the database connection was lost or the transaction was already aborted between the SELECT and the COMMIT.","triggerScenarios":"Calling WithLockedSampleInstanceSetupForCleanup where no row matches the stale/expired predicate AND tx.Commit() of the empty transaction fails: connection dropped after the SELECT, database restart, statement timeout aborting the transaction, or context cancelled at the commit boundary.","commonSituations":"Long-lived cleanup connection going stale between ticks and failing on the first commit; Postgres restarted while the cleanup loop held the idle transaction; 'current transaction is aborted' after an earlier warning/timeout in the same transaction.","solutions":["Confirm database connectivity; a connection-level error here means the pool served a dead connection — enable connection lifetime/health checks (ConnMaxLifetime, ping before use).","Treat this as transient: the next cleanup tick re-runs the select and will take the same empty path once connectivity is restored.","If 'transaction is aborted' appears, find the earlier error inside the same transaction (e.g. the SELECT timeout) and address its root cause.","Avoid holding the cleanup transaction open long; the empty path should commit immediately after the no-rows SELECT."],"exampleFix":"// before: stale pooled connection fails the empty commit\nres, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)\n\n// after: keep pool connections healthy so empty commits succeed\ndb.SetConnMaxLifetime(5 * time.Minute)\ndb.SetConnMaxIdleTime(1 * time.Minute)\nres, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)","handlingStrategy":"retry","validationCode":"// recycle stale connections so empty-transaction commits do not hit dead sockets\ndb.SetConnMaxIdleTime(1 * time.Minute)\ndb.SetConnMaxLifetime(5 * time.Minute)\nif err := db.PingContext(ctx); err != nil {\n\treturn // defer cleanup tick\n}","typeGuard":"func isAbortedTxn(err error) bool {\n\tvar pgErr *pgconn.PgError\n\treturn errors.As(err, &pgErr) && pgErr.Code == pgerrcode.InFailedSQLTransaction\n}","tryCatchPattern":"res, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)\nif err != nil && strings.Contains(err.Error(), \"failed to commit empty sample instance cleanup\") {\n\t// harmless: nothing to clean anyway; retry on the next tick\n\tlog.Printf(\"empty cleanup commit failed, will retry: %v\", err)\n\treturn nil\n}","preventionTips":["Set pool idle/lifetime limits below any intermediary (NAT, LB, pgbouncer) idle timeout.","Do not hold cleanup transactions open across long callbacks; commit promptly on the empty path.","Treat empty-cleanup commit failures as benign — no data was at stake — and retry next tick.","Enable TCP keepalives on the Postgres connection string to prune dead connections early."],"tags":["database","transaction","commit","cleanup"],"backgroundTag":"transaction-commit-failed","analyzedSha":"1870550677fe08f0d2a78c07acd27541464eb945","analyzedAt":"2026-09-06T21:16:13.665Z","contentChangedAt":"2026-09-06T21:16:13.665Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}