Skip to content

fix(coderd/database/migrations): lock chat tables before 000609 alters them - #30297

Merged
ThomasK33 merged 1 commit into
mainfrom
tk/fix-000609-migration-deadlock
Oct 2, 2026
Merged

ThomasK33 merged 1 commit into
mainfrom
tk/fix-000609-migration-deadlock

Conversation

@ThomasK33

Copy link
Copy Markdown
Member

Migration 000609 deadlocked with live chat traffic during a rolling deploy, so the new coder server pod failed to start (migrate up: ... deadlock detected ... CREATE TYPE chat_automation_kind). Old replicas were still serving chats.

Problem

  • CREATE TABLE chat_automations (... REFERENCES chats ...) takes SHARE ROW EXCLUSIVE on chats. About 130 lines later, ALTER TABLE chats ADD COLUMN automation_id needs ACCESS EXCLUSIVE. A chatd transaction that locks a chat row and then updates the chat forms a cycle with that lock upgrade.
  • A second cycle exists in 000609 and in 000611 on its own: a chats_expanded reader that arrives while the migration waits for chats holds the view lock, and the migration needs that lock for DROP VIEW. Taking the view first instead deadlocks with chatd code that locks a chat and then reads chats_expanded (dbauthz UpdateChatStatus calls GetChatByID).

No fixed lock order avoids both cycles.

Fix

000609 and 000611 now take every lock they need at the start, in a retry loop. Each attempt sets lock_timeout to 100ms, below the default deadlock_timeout of 1s, so the migration gives up before Postgres can choose an application transaction as the deadlock victim. A failed attempt releases its locks and retries for up to 2 minutes. The attempt drops chats_expanded (it is recreated below), locks chats, chat_messages and chat_queued_messages in ACCESS EXCLUSIVE mode, and, in 000609, locks the foreign-key parents in the mode CREATE TABLE needs anyway.

Editing these merged migrations is safe: the resulting schema is identical (dump.sql regenerates with no diff). Databases that already applied them do not rerun them, and a failed run rolled back to 608.

TestMigrationChatTablesConcurrentTraffic reproduces both cycles with pg_blocking_pids sequencing, no sleeps. The original migrations deadlocked in 3 of 3 runs.

Verification and limits

An independent run through the real migrator (UpWithFS, all pending migrations in one transaction) under four chat traffic patterns:

  • 608 to latest (the failing deploy) and 610 to latest: deadlocks before, none after.
  • Upgrades that start earlier still deadlock, before and after this change, in older migrations that this PR does not touch: 000600, 000601 with 000607, and 000608. From 585 (v2.37.3) the migrator itself is the victim. The follow-up in CODAGT-1298 retries the whole migration transaction on SQLSTATE 40P01 and takes the chat locks once at the start of the transaction.
  • The full lock on chats now also covers the index builds on chat_messages and chat_queued_messages: about 100ms with 1M chat_messages rows, compared with about 11ms before.
  • The 100ms wait relies on deadlock_timeout staying above about 600ms.

Generated with xum • Model: anthropic:claude-opus-5-5 • Thinking: high

…s them

Migration 000609 deadlocked with chatd during a rolling deploy.
CREATE TABLE chat_automations took SHARE ROW EXCLUSIVE on chats for its
foreign keys, and the later ALTER TABLE chats needed ACCESS EXCLUSIVE. A
chatd transaction that locked a chat row and then updated the chat formed
a cycle with that upgrade.

A second cycle exists in 000609 and 000611: a chats_expanded reader that
arrives while the migration waits for chats holds the view lock, and the
migration needs that lock for DROP VIEW after it gets chats. Taking the
view first instead deadlocks with chatd transactions that lock a chat and
then read chats_expanded through dbauthz.

No fixed lock order avoids both, so both migrations now take every lock
they need up front in a retry loop: each attempt waits at most 100ms per
lock, below the default deadlock_timeout, and releases everything on a
timeout. DROP VIEW is part of the attempt because LOCK TABLE on the view
would also lock users in ACCESS EXCLUSIVE mode. The resulting schema is
unchanged (dump.sql regenerates identically).

The new test reproduces each cycle with pg_blocking_pids sequencing.

---
_Generated with [`xum`](https://github.com/coder/xum) • Model: `anthropic:claude-opus-5-5` • Thinking: `high`_
@ThomasK33

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Oct 2, 2026 •

Copy link
Copy Markdown

Codex Review Summary

This comment shows the latest Codex review activity on this pull request.

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-10-02T17:54:14.674877Z 187c50e Manual request
ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Breezy!

Reviewed commit: 187c50e31d

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

@ThomasK33
ThomasK33 marked this pull request as ready for review October 2, 2026 18:05
@ThomasK33
ThomasK33 enabled auto-merge (squash) October 2, 2026 18:41
@ThomasK33
ThomasK33 merged commit 122757d into main Oct 2, 2026
57 of 58 checks passed
@ThomasK33
ThomasK33 deleted the tk/fix-000609-migration-deadlock branch October 2, 2026 18:52
@github-actions github-actions Bot locked and limited conversation to collaborators Oct 2, 2026
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants