Title: fix(files): lock the workspace row before inserting a workspace file by waleedlatif1 · Pull Request #8972 · simstudioai/sim · GitHub
Open Graph Title: fix(files): lock the workspace row before inserting a workspace file by waleedlatif1 · Pull Request #8972 · simstudioai/sim
X Title: fix(files): lock the workspace row before inserting a workspace file by waleedlatif1 · Pull Request #8972 · simstudioai/sim
Description: Summary Fixes deadlock detected (40P01) between a file content write and a concurrent new-file insert in the same workspace. It recurs in bursts when a workspace gets concurrent uploads and overwrites, e.g. an agent writing many files. Cycle: updateWorkspaceFileContent locks the workspace row (lockWorkspaceStorageForMutationInTx, FOR NO KEY UPDATE), then UPDATEs the file. The AFTER trigger workspace_files_search_index_pending then upserts the one-row-per-workspace workspace_file_search_dispatch_queue row. uploadWorkspaceFile, registerUploadedWorkspaceFile and fork executeForkFileBlobCopies INSERT the file first, so the trigger takes the queue row. They lock the workspace row only later, in incrementStorageUsageForBillingContextInTx. In prod logs the blocked content write was "inserting index tuple in relation workspace_file_search_dispatch_queue". The transaction blocking it was waiting on select … from workspace … for no key update. Fix: call lockWorkspaceStorageForMutationInTx right before the file insert in those three paths and in createOwnedWorkspaceFile. createOwnedWorkspaceFile uses non-workspace contexts, so the trigger never reaches the queue row today. It gets the same order for consistency. The increment's later re-lock is already held, so it never waits. Documents the canonical order in the TSDoc of lockWorkspaceStorageForMutationInTx: folder advisory lock → workspace row → file rows → file writes (trigger queue row) → payer row. The trigger is unchanged. Making its queue upsert ON CONFLICT DO NOTHING would lose wakeups. Why it's safe No new lock pair with the folder lock. Upload and register already took the folder advisory lock and then the workspace row (via the increment). Only the workspace row moves ahead of the file/queue writes. Every acquireFolderMutationLock / withFolderTreeLock call is the first lock in its transaction. Mapped-import and lockForkRevision take the folder lock before the workspace row. So no transaction holds the workspace row and then waits on the folder lock. The other workspace-row lockers already follow workspace → files: content write, purgeCreatedWorkspaceFile, cleanup-soft-deletes, payer transfer, and fork KB rollback. The other workspace_files inserts don't charge storage in the same transaction, or don't reach the queue row: chat uploads use mothership context. upload finalizers use execution context and have no tx. chat fork copies don't charge storage in that transaction. KB ownership rows use knowledge-base context, which the trigger skips. The dispatcher never locks workspace rows. It claims queue rows with SKIP LOCKED. Lock waits are unchanged. Upload/register wait under the same 5s lock_timeout the folder lock already sets. The lock is taken after folder-path resolution, so the workspace row is held only across the insert and the increment that already followed it. Extra cost: one cheap re-select of an already-held row per insert. Race and replay paths that end up not charging now also briefly hold the workspace row. No behavior, API or schema changes. Type of Change Bug fix Testing New apps/sim/lib/uploads/contexts/workspace/__integration__/storage-lock-order.integration.ts runs against real Postgres, once each for uploadWorkspaceFile and registerUploadedWorkspaceFile: One transaction holds the workspace row the way updateWorkspaceFileContent does. The finalize path starts, and the test polls until it is blocked by that transaction. The holder then bumps content_updated_at on an existing file in the same workspace. Its trigger upserts the queue row the seed upload created. Asserts that both transactions commit. Pre-fix, the test fails (reasoned, not run locally): With the old order, the finalize path has already inserted its row (trigger → queue row lock) before blocking on the workspace row in the increment. The holder's UPDATE then waits on the queue row, which closes the cycle. Postgres aborts one side with 40P01 after deadlock_timeout (1s, well under the 5s lock_timeout), so a settled result is rejected. Post-fix, the finalize path blocks before its insert, the holder's trigger finds the queue row free, and both commit. Ran biome on the changed files. Per repo policy I did not run type-check, check:audits or integration suites locally; CI runs them. Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing (new tests pass the test-audit authoring gate) No new warnings introduced I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary Fixes deadlock detected (40P01) between a file content write and a concurrent new-file insert in the same workspace. It recurs in bursts when a workspace gets concurrent uploads and overwr...
X Description: Summary Fixes deadlock detected (40P01) between a file content write and a concurrent new-file insert in the same workspace. It recurs in bursts when a workspace gets concurrent uploads and overwr...
Opengraph URL: https://github.com/simstudioai/sim/pull/8972
X: @github
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf |
| route-pattern | /:user_id/:repository/pull/:id/files(.:format) |
| route-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:906c2582-1240-bedd-0959-317c6042edd5 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | CC50:71DFA:909552:CCB74A:6AFB5124 |
| html-safe-nonce | 31348ea889cbaeb36c8a5750491a018a60d8d5ae64c6c23d3dc728690f86851a |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJDQzUwOjcxREZBOjkwOTU1MjpDQ0I3NEE6NkFGQjUxMjQiLCJ2aXNpdG9yX2lkIjoiNzg0MjE0NTI4NDMzNTQxNDEyIiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | e2c9a49d6545559019aeab3856dd5045e73012c26238a862e573ce7707de56b4 |
| hovercard-subject-tag | pull_request:4847607637 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8972/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary Fixes deadlock detected (40P01) between a file content write and a concurrent new-file insert in the same workspace. It recurs in bursts when a workspace gets concurrent uploads and overwr... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | cbf66c024bb6d10e6955baa7413c54cf13a201bb |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width