Repository navigation
fix(coderd/database/migrations): lock chat tables before 000609 alters them - #30297
Merged
Merged
Conversation
…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`_
Member
Author
|
@codex review |
Codex Review SummaryThis comment shows the latest Codex review activity on this pull request.
ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings. |
|
Codex Review: Didn't find any major issues. Breezy! Reviewed commit: ℹ️ About Codex in GitHubYour team has set up Codex to review pull requests in this repo. Reviews are triggered when you
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
marked this pull request as ready for review
October 2, 2026 18:05
ThomasK33
enabled auto-merge (squash)
October 2, 2026 18:41
ibetitsmike
approved these changes
Oct 2, 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Migration 000609 deadlocked with live chat traffic during a rolling deploy, so the new
coder serverpod 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 onchats. About 130 lines later,ALTER TABLE chats ADD COLUMN automation_idneeds ACCESS EXCLUSIVE. A chatd transaction that locks a chat row and then updates the chat forms a cycle with that lock upgrade.chats_expandedreader that arrives while the migration waits forchatsholds the view lock, and the migration needs that lock forDROP VIEW. Taking the view first instead deadlocks with chatd code that locks a chat and then readschats_expanded(dbauthzUpdateChatStatuscallsGetChatByID).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_timeoutto 100ms, below the defaultdeadlock_timeoutof 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 dropschats_expanded(it is recreated below), lockschats,chat_messagesandchat_queued_messagesin ACCESS EXCLUSIVE mode, and, in 000609, locks the foreign-key parents in the modeCREATE TABLEneeds anyway.Editing these merged migrations is safe: the resulting schema is identical (
dump.sqlregenerates with no diff). Databases that already applied them do not rerun them, and a failed run rolled back to 608.TestMigrationChatTablesConcurrentTrafficreproduces both cycles withpg_blocking_pidssequencing, 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:chatsnow also covers the index builds onchat_messagesandchat_queued_messages: about 100ms with 1Mchat_messagesrows, compared with about 11ms before.deadlock_timeoutstaying above about 600ms.Generated with
xum• Model:anthropic:claude-opus-5-5• Thinking:high