{"record":{"id":"aa3b4b8e4b16026b","repo":"vxcontrol/pentagi","slug":"failed-to-acquire-advisory-lock-q-w","errorCode":null,"errorMessage":"failed to acquire advisory lock %q: %w","messagePattern":"failed to acquire advisory lock %q: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/pkg/database/tenant.go","lineNumber":210,"sourceCode":"\t})\n}\n\n// WithAdvisoryLock runs fn while holding a PostgreSQL session-level advisory\n// lock derived from key. The lock is taken on a dedicated connection because\n// advisory locks are session-scoped and *sql.DB is a pool.\nfunc WithAdvisoryLock(ctx context.Context, db *sql.DB, key string, fn func(*sql.Conn) error) error {\n\t// crc32 into the signed 32-bit space keeps the key stable and collision-free\n\t// enough for the two distinct locks this application takes.\n\tlockID := int64(int32(crc32.ChecksumIEEE([]byte(key))))\n\n\tconn, err := db.Conn(ctx)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"failed to acquire database connection for advisory lock: %w\", err)\n\t}\n\tdefer conn.Close()\n\n\tif _, err := conn.ExecContext(ctx, \"SELECT pg_advisory_lock($1)\", lockID); err != nil {\n\t\treturn fmt.Errorf(\"failed to acquire advisory lock %q: %w\", key, err)\n\t}\n\tdefer func() {\n\t\t// Best effort: closing the connection releases the lock regardless.\n\t\t_, _ = conn.ExecContext(context.WithoutCancel(ctx), \"SELECT pg_advisory_unlock($1)\", lockID)\n\t}()\n\n\treturn fn(conn)\n}\n\n// withSearchPath returns dsn with the tenant's search_path applied as a\n// PostgreSQL startup parameter (or, with viaOptions, wrapped as\n// options=--search_path=<value> for poolers that need it — see\n// DATABASE_SEARCH_PATH_VIA_OPTIONS in backend/docs/config.md). Supports both\n// URL-style DSNs and libpq keyword strings.\nfunc withSearchPath(dsn, searchPath string, viaOptions bool) (string, error) {\n\tkey, value := \"search_path\", searchPath\n\tif viaOptions {\n\t\tkey, value = \"options\", \"--search_path=\"+searchPath","sourceCodeStart":192,"sourceCodeEnd":228,"githubUrl":"https://github.com/vxcontrol/pentagi/blob/ea665308baaff015b226f308438a68d929d0f29b/backend/pkg/database/tenant.go#L192-L228","documentation":"After obtaining a dedicated connection, WithAdvisoryLock runs SELECT pg_advisory_lock($1) with a lock ID derived from crc32(key). This error means PostgreSQL rejected or could not complete the lock call itself — typically because the session died mid-call, the context was canceled while blocked waiting on a lock another session holds, or the statement was terminated. Note pg_advisory_lock blocks indefinitely, so context cancellation while queued behind another holder is the most common wrapped cause.","triggerScenarios":"EnsureTenantSchema or RunMigrations waits on pg_advisory_lock while another instance/boot holds the same lock and ctx expires or the connection drops; server shutdown (admin shutdown / crash) kills the session; statement timeout fires on the lock wait.","commonSituations":"Two replicas deployed simultaneously with a short startup timeout — the second times out waiting; a crashed instance's session lingering and holding the lock until the server reaps it; statement_timeout set globally on the DB killing long lock waits during big migrations.","solutions":["Retry the start once the current lock holder finishes — check holders via SELECT pid, granted FROM pg_locks WHERE locktype='advisory'; and pg_stat_activity.","Increase the startup context timeout so concurrent boots serialize instead of failing.","If a stale session holds the lock, terminate it: SELECT pg_terminate_backend(<pid>);","Avoid tiny statement_timeout settings on the migration/bootstrap role."],"exampleFix":"// before\nctx := context.Background() // then caller's 5s timeout expires waiting on pg_advisory_lock\n// after\nctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute) // allow lock serialization","handlingStrategy":"retry","validationCode":"// check current advisory-lock holders before deploying a second instance\nSELECT pid, granted, ((payload::bit(32))::bigint) FROM pg_locks WHERE locktype='advisory';","typeGuard":null,"tryCatchPattern":"err := RunMigrations(ctx, db, cfg, up)\nif err != nil && strings.Contains(err.Error(), \"failed to acquire advisory lock\") {\n    // wait for the other holder to finish, then retry once\n    time.Sleep(10 * time.Second)\n    return RunMigrations(ctx, db, cfg, up)\n}","preventionTips":["Use a startup timeout long enough for concurrent instances to serialize","After a crash, verify no stale session holds the lock (pg_locks / pg_stat_activity) and pg_terminate_backend if needed","Avoid aggressive statement_timeout on the migration role","Keep instance count and restarts in mind when scaling"],"tags":["postgres","advisory-lock","concurrency","timeout","migrations"],"backgroundTag":"advisory-lock-contention","analyzedSha":"ea665308baaff015b226f308438a68d929d0f29b","analyzedAt":"2026-09-01T14:16:31.421Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}