Skip to content

Commit 081be4c

Browse files
author
Elrond
committed
fix(db): delete workflow fires before locking runs
Attaching a run to a scheduled fire locks the fire row and then key-share locks the run. Taking the run lock before the fire delete inverted that order and could deadlock (40P01) with a cron attach. Delete fires first; the workflow lock already excludes new fires. Pin it with a concurrent regression test. Signed-off-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
1 parent 787c7c4 commit 081be4c

2 files changed

Lines changed: 93 additions & 9 deletions

File tree

‎crates/buzz-db/src/store/workflow/deletion.rs‎

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -155,10 +155,21 @@ async fn delete_workflow_in_transaction(
155155
}
156156

157157
/// Delete a locked workflow and the rows that hang off it, children first, so
158-
/// no foreign-key action is needed: approvals (of the workflow or any of its
159-
/// runs), scheduled fires, then runs. Returns the workflow's `channel_id`.
158+
/// no foreign-key action is needed: scheduled fires, approvals (of the workflow
159+
/// or any of its runs), then runs. Returns the workflow's `channel_id`.
160160
async fn delete_workflow_with_children(tx: &mut AdmittedTx, id: Uuid) -> Result<Option<Uuid>> {
161161
let community = *tx.community().as_uuid();
162+
// Fires go before the run lock. A new fire must key-share lock the workflow,
163+
// which is already locked. Attaching a run to a fire locks the fire row and
164+
// then the run, so taking the run lock first would invert that order and can
165+
// deadlock; deleting the fire first waits for any in-flight attach instead.
166+
sqlx::query(
167+
"DELETE FROM scheduled_workflow_fires WHERE community_id = $1 AND workflow_id = $2",
168+
)
169+
.bind(community)
170+
.bind(id)
171+
.execute(tx.conn())
172+
.await?;
162173
// The workflow lock stops new runs, but an approval naming another workflow
163174
// can still reference one of these runs; it key-share locks only that run.
164175
// Lock the runs (in a fixed order) so such an insert either commits before
@@ -180,13 +191,6 @@ async fn delete_workflow_with_children(tx: &mut AdmittedTx, id: Uuid) -> Result<
180191
.bind(id)
181192
.execute(tx.conn())
182193
.await?;
183-
sqlx::query(
184-
"DELETE FROM scheduled_workflow_fires WHERE community_id = $1 AND workflow_id = $2",
185-
)
186-
.bind(community)
187-
.bind(id)
188-
.execute(tx.conn())
189-
.await?;
190194
sqlx::query("DELETE FROM workflow_runs WHERE community_id = $1 AND workflow_id = $2")
191195
.bind(community)
192196
.bind(id)

‎crates/buzz-db/src/store/workflow/deletion_tests.rs‎

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -557,3 +557,83 @@ async fn workflow_deletion_waits_for_and_removes_concurrent_cross_workflow_appro
557557
}
558558
assert!(get_workflow(&db.pool, community, other).await.is_ok());
559559
}
560+
561+
/// Attaching a run to a scheduled fire locks the fire row, then key-share
562+
/// locks the run for its foreign key. The delete must take those two in the
563+
/// same order (fire before run), so an attach in flight when the delete
564+
/// starts completes and is then removed, rather than deadlocking with it.
565+
#[tokio::test]
566+
#[ignore = "requires Postgres"]
567+
async fn workflow_deletion_waits_for_concurrent_fire_attach_without_deadlock() {
568+
let (db, community) = setup().await;
569+
crate::test_support::retire_foreign_key_delete_actions(&db.pool).await;
570+
let keys = Keys::generate();
571+
let owner = keys.public_key().to_bytes();
572+
let id = Uuid::new_v4();
573+
let d_tag = id.to_string();
574+
let now = Timestamp::now().as_secs();
575+
seed(&db, community, &keys, id, &d_tag, now).await;
576+
let run_id = create_workflow_run(&db.pool, community, id, None, None)
577+
.await
578+
.expect("run");
579+
let scheduled_for: chrono::DateTime<chrono::Utc> = sqlx::query_scalar(
580+
"INSERT INTO scheduled_workflow_fires (community_id, workflow_id, scheduled_for) \
581+
VALUES ($1, $2, date_trunc('second', NOW())) RETURNING scheduled_for",
582+
)
583+
.bind(community.as_uuid())
584+
.bind(id)
585+
.fetch_one(&db.pool)
586+
.await
587+
.expect("unlinked scheduled claim");
588+
589+
// Hold the fire row as an attach does before its foreign-key check runs.
590+
let mut attacher = db.pool.begin().await.expect("begin fire attach");
591+
sqlx::query(
592+
"SELECT 1 FROM scheduled_workflow_fires \
593+
WHERE community_id = $1 AND workflow_id = $2 AND scheduled_for = $3 FOR UPDATE",
594+
)
595+
.bind(community.as_uuid())
596+
.bind(id)
597+
.bind(scheduled_for)
598+
.execute(&mut *attacher)
599+
.await
600+
.expect("lock fire row");
601+
602+
let name = format!("workflow-delete-{}", Uuid::new_v4().simple());
603+
let deleter = Db::from_pool(crate::test_support::named_pool(&name).await);
604+
let deletion = tokio::spawn(async move {
605+
deleter
606+
.delete_workflow_by_coordinate(community, &owner, &d_tag, now as i64)
607+
.await
608+
});
609+
crate::test_support::wait_for_lock_wait(&db.pool, &name).await;
610+
let attached = sqlx::query(
611+
"UPDATE scheduled_workflow_fires SET workflow_run_id = $4 \
612+
WHERE community_id = $1 AND workflow_id = $2 AND scheduled_for = $3",
613+
)
614+
.bind(community.as_uuid())
615+
.bind(id)
616+
.bind(scheduled_for)
617+
.bind(run_id)
618+
.execute(&mut *attacher)
619+
.await
620+
.expect("attach run to fire without deadlock");
621+
assert_eq!(attached.rows_affected(), 1);
622+
attacher.commit().await.expect("commit fire attach");
623+
624+
let outcome = deletion
625+
.await
626+
.expect("join deletion")
627+
.expect("delete after concurrent fire attach");
628+
assert!(outcome.changed);
629+
for (table, column, key) in [
630+
("scheduled_workflow_fires", "workflow_id", id),
631+
("workflow_runs", "workflow_id", id),
632+
] {
633+
assert_eq!(
634+
count_where(&db, table, column, community, key).await,
635+
0,
636+
"{table}"
637+
);
638+
}
639+
}

0 commit comments

Comments
 (0)