GeezerVison

Context

ClaimPrebuiltWorkspace picks a prebuild with FOR UPDATE OF p SKIP LOCKED, where p is the workspace_prebuilds view. That view is built from CTEs. Postgres does not push row-level locks into a CTE, so the clause locks nothing.

When several users claim from the same preset at once, two claims can select the same prebuild. The second claim waits on the first claim's row lock in the outer UPDATE. Once the first commits, the second reassigns the workspace to its own user. The first user's next build on that workspace fails with 409 A workspace build is already active.

We hit this in load testing for a training session with hundreds of users claiming at once. More than half of the claims returned 409, even with client-side retries.

Intent

Lock the underlying workspaces row (FOR UPDATE OF pw SKIP LOCKED) instead of the view, so concurrent claims skip a prebuild that is already being claimed.

Also filter on pw.owner_id being the prebuilds system user. Postgres rechecks conditions on a locked row in READ COMMITTED mode. A prebuild that a concurrent transaction has just claimed then drops out instead of being claimed again.

This changes only the query text. There is no schema or migration change, and dump.sql is unchanged.

Testing

TestClaimPrebuiltWorkspaceConcurrent holds one claim's transaction open and runs a second claim against the same preset. It polls pg_stat_activity to tell "returned" apart from "blocked on a lock", so it does not depend on wall-clock timing.

Before the fix (test only):

--- FAIL: TestClaimPrebuiltWorkspaceConcurrent (0.41s)
    querier_test.go:7975: 
                Error Trace:    /tmp/coder-src/coder/coderd/database/querier_test.go:7975
                Error:          claim B blocked on claim A's lock
                Test:           TestClaimPrebuiltWorkspaceConcurrent
                Messages:       claim B returned {ID:c2d820d4-6977-4137-a9cc-35c257e47c89 Name:claimed-by-cheerful_gottlieb11}, <nil> after claim A committed
FAIL
FAIL    github.com/coder/coder/v2/coderd/database       0.468s
FAIL

After the fix:

=== RUN   TestClaimPrebuiltWorkspaceConcurrent
--- PASS: TestClaimPrebuiltWorkspaceConcurrent (0.40s)
PASS
ok      github.com/coder/coder/v2/coderd/database       0.447s

Also run against Postgres, on both main and v2.38.3:

• TestClaimPrebuiltWorkspaceConcurrent with -count=10 and with -race.
• ./coderd/database/...
• ./enterprise/coderd/prebuilds/...
• Prebuild, claim, and create tests in ./enterprise/coderd and ./enterprise/cli.
• golangci-lint v2.0.2 reports 0 issues.
• coderd/database/generate.sh with the pinned sqlc fork changes only the query const in queries.sql.go.

Notes

The same patch applies cleanly to v2.38.3 and v2.38.4. Both still have the bug.